Write text to multiple Excel worksheets

This tip shows how to write text to multiple Excel worksheets.

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

Dig deeper on .NET Architecture Best Practices

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close