Running Excel VBA macros without opening the excel file

DVK
12-04-2008, 03:08 PM
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,

DVK
12-05-2008, 11:38 AM
Could anybody give me a hand?

Burningslash12
12-05-2008, 11:57 AM
I Assume you have 1 workbook that contains the macro, and the other workbooks just the data to be formatted:


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:


Application.ScreenUpdating = False


Then set it to True, before your program ends.

Hope this helps :D

Burningslash12
12-05-2008, 12:07 PM
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.


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 :D

DVK
12-05-2008, 12:35 PM
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

Roger_Wgnr
12-05-2008, 12:51 PM
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.

Mill
12-08-2008, 07:51 AM
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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum