[RESOLVED] Multiple spreadsheet updates

[Aelanna]

Ok, this is just bizarre and has been driving me crazy all day.

My program makes changes to 4 different spreadsheets when a button is clicked. It opens sheet 1, changes it, saves it, closes Excel. Then launches excel, opens sheet 2, changes it, saves it, closes excel. Etc.

I wrote the code for updating each sheet as its own block of code, and tested each block of code seperately as I wrote it. (I didn't want to have three sheets open each time I wanted to test the code for the third sheet, etc) I would cut the code for the other parts out and put it into an uncalled temp subroutine at the end of the program while I wasn't using it, and had only the code for the sheet I was working on in the Command1_Click() event.

Each block of code for each of the four sheets works flawlessly as long as it is the only code block in the Click() event. But as soon as I start putting 2, 3, or all 4 blocks together in the Click() event, they start doing weird things, like telling me the sheet already exists and asking if I want to overwrite (the sheets all do exist, and I was not asked to overwrite in each block because I used Application.DisplayAlerts to turn of fthe prompt when saving), or telling me Method 'Cells' of object '_Global' failed on the line:
iRow = Cells.SpecialCells(xlCellTypeLastCell).Row
when no error occurs if the code block is alone.

I'm not sure why this is happening, and why all the code blocks work fine alone but don't play nice together. The code for them all is virtually identical as far as how they call up Excel, open the sheets, activate a sheet, put stuff in the cells, save the sheet, close excel, and do cleanup.

I'm thinking it might be because it's opening up a second instance of Excel while the first instance isn't quite closed yet? I've tried adding a pause in the program between the closing of excel and the opening of it again, but that doesn't seem to help, either using the Sleep API function or a simple for..next loop.

[DaGeeza]

I had some weird problems like this a while back, ended up to fix the problem Open excel - then the document, alter - close the document and move on to the next, keeping excel open in all cases.

Dim oExcel as Excel.Application

if oExcel = Nothing
      oExcel = CreateObject("Excel.Application")
end if

You can then use oExcel.visible = false if you want to hide it

[Aelanna]

Ok, so would I put that in each code block after the first? And that would replace

Set oExcel = New Excel.Application ?

[RobDog888]

If your going off of the code from the other thread the the app object is only created once upon form load. You should only need to open/close workbooks, etc.

[Aelanna]

Yes, I found that out when I tried sticking his code in there. I got rid of all the app objects after the first one, and got rid of the oExcel.close and oExcel=Nothing lines in the cleanups. So far, initial tests show it's working, with no more error messages!

I can't thank all you guys on this Forum enough for your help on this project. Without VBForums and you all who post here, this project would never have been, or would have taken a LOT more time than I probably have in order to finish. When I'm done, can I put up screen shots of my project so you can see what I've done with your help?