Erik Greene
Reader Erik Greene shares the results of many hours of head scratching over how to deal with Excel from VB.
I recently finished a project in which I had to dump information out to an EXCEL spreadsheet. I ran into 2 problems that I thought I would share the solutions for.
* First, I had to figure out a way to change to a different worksheet.
* Second I had a difficult time saving and closing the EXCEL session after I had updated the file.
These are the solutions I came up with. Take a look at these before spending the hours it took me to figure it out.
'*Solution 1
'to switch between worksheets, use the shortcut - CTRL-Pagedown
SendKeys ("^{PGDN}")
'just be sure that you CTRL-Pageup to the first page you started from
before you save
SendKeys ("^{PGUP}")
'*Solution 2
'This one was actually a misunderstanding of how the EXCEL Object
library defined things.
'First, close all active sheets and the workbook
Set xlSheet = Nothing
Set xlBook = Nothing
'Now, just use the save command and close your EXCEL session.
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close (True)
xlApp.Quit
Set xlApp = Nothing
--------------------------------------------------Erik Greene is currently working for the Stride Rite Corporate Customer Service Center in Richmond, Indiana as a Systems Analyst. He uses VB almost daily.
This was first published in March 2001