Running Excel VBA macros without opening the excel file

12-04-2008, 02:08 PM

DVK

Newcomer

Join Date: Dec 2008

Posts: 3

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,

12-05-2008, 10:38 AM

DVK

Newcomer

Join Date: Dec 2008

Posts: 3

Could anybody give me a hand?

12-05-2008, 10:57 AM

Burningslash12 Burningslash12 is offline

Junior Contributor

Join Date: Sep 2008

Location: Singapore

Posts: 303

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!

12-05-2008, 11:07 AM

Burningslash12 Burningslash12 is offline

Junior Contributor

Join Date: Sep 2008

Location: Singapore

Posts: 303

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!

12-05-2008, 11:35 AM

DVK

Newcomer

Join Date: Dec 2008

Posts: 3

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

12-05-2008, 11:51 AM

Roger_Wgnr Roger_Wgnr is offline

CodeASaurus Hex

Forum Leader
* Expert *

Join Date: Jul 2006

Location: San Antonio TX

Posts: 2,427

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

12-08-2008, 06:51 AM

Mill

Just another Excel nerd

Retired Moderator
* Guru *

Join Date: Feb 2000

Location: Michigan, USA

Posts: 2,624

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