PDA

View Full Version : Don't show the opening/closing workbooks



db342
07-25-2007, 05:25 PM
Very new to VBA, but wrote a macro that allows the user to type in a file/filepath on an "Entry" ws which will then open 6 groups of 24 different CSV files and compile them into one newly created workbook with 6 worksheets - 1 ws for each group. (In other words, there is one Excel WB with one WS called "Entry". Data is entered on that and the macro is run from there. The macro creates a new workbook, then pulls all 6x24 worksheets of data into the new workbook and saves it.)

Everything is working except that when it is running the excel ws "Entry" (the ws that is opened in order to run the macro) blinks and the taskbar shows the little blips/blinks with all the files being opened and closed.

I did shut off the screen autoupdate so all the data isn't scrolling about, but any attempts to change the visibility of the windows or application to get rid of the remaining blinking is either shutting off the "Entry" window as well or is creating an object not found error.

There is a sub that creates the new (destination) wb and a sub that opens, copies, and closes both the destination and the source wb/ws's

Any ideas as to how to make this appear as though nothing is happening while all of these files are being opened, copied, closed...??
Thanks for any help!

Shawn

db342
07-25-2007, 05:28 PM
I should have probably posted this in the morning, since I've got to leave work soon. I will be checking back then though!

malik641
07-25-2007, 07:13 PM
Hey Shawn,

Instead of using the current instance of Excel's application, you can create a new instance and set it's visible property to False. Then open the CSV files from there and perform the rest of the code in the new Excel.Application instance. I don't think you will see any flickering at all using this method since you will originally force the new application instance to not be visible, and therefore any workbook you open inside that instance will not be visible by the user either :)

Something along the lines of:
Public Sub Testing123()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = New Excel.Application
xlApp.Visible = False

Set xlWB = xlApp.Workbooks.Open("Your filename.xls")

' More code...

xlWB.Close False
xlApp.Quit
End Sub
To start :thumb

malik641
07-25-2007, 07:13 PM
Just noticed that you are new here. Welcome to VBAX :hi:

db342
07-27-2007, 01:03 PM
Thank you for the help, I will try your suggestion. That should take care of it!