vb6 and excel

[john24]

is there a way to get excel to update all worksheets in a workbook from within vb - i can get the active worksheet to update but all of the other linked worksheets remain static. is there a solution to this?

 

[VDavid]

To update other worksheet, you have to make it the active one,

Set objXL = CreateObject("Excel.Application")
objXL.Sheets(Index).Activate

where Index is the sheet number you want to update.

Hope this helps,

David.

 

[john24]

this works for the active sheet only, the other sheets never get updated.

 

[VDavid]

I thought that you wanted to update programatically the worksheets, i did that activating every sheets and changing the data on it.
I'm not sure what you want, if you want to update a sheet while it is open in excel, then sorry i cant help you.

David.

 

[john24]

here's the situation, there's one primary sheet open to the user to do as they will (with restrictions) - there are 5 more sheets linked to the primary sheet. when the user is finished entering data i then do a .calculate. this updates the primary sheet but the other sheets that are linked to the primary are not updated. using excel directly and doing an f9 updates all sheets, which is what i want to do.

 

[paulbent]

Something like this should do it:

For Each objSheet In objApp.ActiveWorkbook.Worksheets
objSheet.Calculate
Next

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[VDavid]

john24
What Paul post should do it, if not you can also try SendKeys "{F9}", or I can post you and example i've just made, it opens an excel file, let the user enter 2 numbers in two cells in the first sheet, then within the vb app you active the second sheet, enter a calcualte field and active the first sheet, it works, the calcualte field is update. But that's what you want?, or with Paul's reply is ok.

David.

 

[john24]

what i don't want to do is to activate each individual sheet - only the first sheet gets input, all the others are linked to the first and the last sheet contains the final calcs. with just the first sheet active the other sheets never get updated. i'll try the sendkeys and see that helps
thanks

 

[paulbent]

john24,

The code I posted doesn't activate any sheets. Did you try it?

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[john24]

i have not, i've been assigned to yet another swamp full of aligators but thanks for the help, i will try asap

 

[johnwm]

Make sure that your spreadsheet has Autocalculation set on. It's under Tools|Options|Calculation - just make sure that Automatic is selected, then any changes to any sheet will be relected thriughout the whole workbook

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[crpjaviman]

I have done what you are looking for in VB code. I have several workbooks with several worksheets that I need to update on a daily basis.

Dim x, y As Integer

For x = 1 To Workbooks.Count
Workbooks(x).Activate
For y = 1 To Worksheets.Count
Worksheets(x).Activate
MsgBox Activesheet.Name
Next y
Next x

I know that this is really basic, but you will be able to change almost anything on the active sheet.

Let me know if this puts you on the right track.

Thanks,
crpjaviman