Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Running Excel VBA macros without opening the excel file


Reply
 
Thread Tools Display Modes
  #1  
Old 12-04-2008, 02:08 PM
DVK DVK is offline
Newcomer
 
Join Date: Dec 2008
Posts: 3
Default Running Excel VBA macros without opening the excel file


Hi all,

I have some VBA macros in an excel file which formats the various sheets in the excel file. Operations performed include copy & paste data from one sheet to another, creating hyperlinks, highlighting some selected cells by coloring & bordering, creating named cell ranges, etc.,. To run this VBA macro, I need to open up the excel file.

The problem is that my boss does not want the excel file to open up while doing this process. Is there a way, that I could do something like a batch job for formatting excel files without opening it?

Your help is much appreciated.
Thanks in advance,
Reply With Quote
  #2  
Old 12-05-2008, 10:38 AM
DVK DVK is offline
Newcomer
 
Join Date: Dec 2008
Posts: 3
Default

Could anybody give me a hand?
Reply With Quote
  #3  
Old 12-05-2008, 10:57 AM
Burningslash12's Avatar
Burningslash12 Burningslash12 is offline
Junior Contributor
 
Join Date: Sep 2008
Location: Singapore
Posts: 303
Default

I Assume you have 1 workbook that contains the macro, and the other workbooks just the data to be formatted:

Code:
The problem is that my boss does not want the excel file to open up while doing this process. 
Is there a way, that I could do something like a batch job for formatting excel files without opening it?
Any reason why your boss doesn't want to open the file? or he just don't want to SEE it being open? Because for some reason, this can be annoying.

If this is the case, you can just add this code before you open the workbook and do the processing:

Code:
   Application.ScreenUpdating  = False
Then set it to True, before your program ends.

Hope this helps
__________________
Burningslash12
"We may rise and fall, but in the end, we'll meet our fate together..."

Don't Click Me!
Reply With Quote
  #4  
Old 12-05-2008, 11:07 AM
Burningslash12's Avatar
Burningslash12 Burningslash12 is offline
Junior Contributor
 
Join Date: Sep 2008
Location: Singapore
Posts: 303
Default

Code:
Is there a way, that I could do something like a batch job for formatting excel files ...
Maybe you can play around with this code, just make the filename of the workbook variable.
You need to add a loop if you need to process more than 1 workbook.

Code:
Sub testFormat()
    Dim myBook As Workbook
    
    Application.ScreenUpdating = False
    Set myBook = Workbooks.Open("C:\TestBook.xls")
    
    myBook.Sheets(1).Range("A1").Value = "Test2"
    myBook.Save
    myBook.Close
    Application.ScreenUpdating = True
End Sub
Hope this helps
__________________
Burningslash12
"We may rise and fall, but in the end, we'll meet our fate together..."

Don't Click Me!
Reply With Quote
  #5  
Old 12-05-2008, 11:35 AM
DVK DVK is offline
Newcomer
 
Join Date: Dec 2008
Posts: 3
Default

Thanks for your response.
Infact, i have used upto "Application.Visible = False".

But my boss does not want to see the excel file being open! :-(

To give a background, we have a grid application which uses the users' machines to create the files. So, we want it to be a background job (not a front end job)

Thanks,
DVK
Reply With Quote
  #6  
Old 12-05-2008, 11:51 AM
Roger_Wgnr's Avatar
Roger_Wgnr Roger_Wgnr is offline
CodeASaurus Hex

Forum Leader
* Expert *
 
Join Date: Jul 2006
Location: San Antonio TX
Posts: 2,427
Default

I don't think this is possible (If I understand you) with VBA.
Since VBA code resides in the workbook you can not run it without opening the workbook. The only way around this would be to create an application that opens an Excel process and the workbook without displaying excel in VB or another language.
__________________
Code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. ~Martin Golding
The user is a peripheral that types when you issue a read request. ~Peter Williams
MSDN Visual Basic .NET General FAQ
Reply With Quote
  #7  
Old 12-08-2008, 06:51 AM
Mill Mill is offline
Just another Excel nerd

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,631
Default

What about creating each file on your own PC and then just disributing them as a background process?

Or if the data is somewhat static, you could create a template file that the users can open each time they run Excel.

Or create one network file and put a shortcut to it on each user's desktop. Then, run the code in the Workbook_Open event, and, as someone else mentioned, set Application.ScreenUpdating = False so they don't have to wait for the screen to update.
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->