Problem solve Get help with specific problems with your technologies, process and projects.

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.Close (True)
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.


Dig Deeper on .NET Architecture Best Practices

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

I haven't quite got what was the problem. Were you trying to write the same information to multiple spreadsheets or what? Couldn't you just copy cells?
Are you just looking to automate the filling of multiple cells with the same data?  Would a reference not be suitable ?