Running Excel VBA macros without opening the excel file
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?
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.
Dim myBook As Workbook
Application.ScreenUpdating = False
Set myBook = Workbooks.Open("C:\TestBook.xls")
myBook.Sheets(1).Range("A1").Value = "Test2"
Application.ScreenUpdating = True
Hope this helps
"We may rise and fall, but in the end, we'll meet our fate together..."
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
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