Tip

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.