opening a workbook with a command button

[MartinL]

Hi

I have been looking at summing closed workbooks here and decided that due to the problems people are facing I would be better off opening the workbooks

I have a command button that makes the cells switch between 2 sets of data.

The second set of data is held in a second workbook and needs to be recalculated every time. Works fine if the workbook is open but only returns the saved values if closed.

If have added some VBA to the Command Button that opens the 2nd spreadsheet, however, is there a way to get it to open in the background rather than on top of the sheet I am working on.

My humble approach has been to write a macro and then edit it to go back to the original sheet I have been working like so:

Private Sub CommandButton1_Click()
'
' OpenABC Macro
' Macro recorded 21/04/2009 by martin.lucas
'
' Keyboard Shortcut: Ctrl+Shift+M
'
ChDir "L:\Customers\ABC\ABC Stats"
Workbooks.Open Filename:="L:\Customers\ABC\ABC Stats\MasterABC.xls", _
UpdateLinks:=0
Windows("TopLine Management.xls").Activate

End Sub

Can this be done without the user knowing it has happened.
and can a check be done so that excel does not try to open it again if it is already open.

 

[Norie]

Martin

This could actually be nothing to do with the code really.

Do you have a lot of links you want to update?

The reason I ask is because the UpdateLinks argument tells VBA whether or not to do so.

 

[MartinL]

Norie

I dont think so

I have a date, and 4 other cells linked from my active sheet to my remote sheet (wb1)
and wb1 is calculating 6 different figures for the active sheet to pick up

however wb1 also has links to a further spreadsheet which does not have to be updated during this process.

 

[Norie]

Well don't update the links.

Just remove the UpdateLinks... part, or change 1 to 0.

UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.
<TABLE cellSpacing=4 cellPadding=4 cols=2><TBODY><TR vAlign=top><TH width="20%">Value</TH><TH width="80%">Meaning</TH></TR><TR vAlign=top><TD class=TT width="20%">0</TD><TD class=TT width="80%">Doesn't update any references</TD></TR><TR vAlign=top><TD class=TT width="20%">1</TD><TD class=TT width="80%">Updates external references but not remote references</TD></TR><TR vAlign=top><TD class=TT width="20%">2</TD><TD class=TT width="80%">Updates remote references but not external references</TD></TR><TR vAlign=top><TD class=TT width="20%">3</TD><TD class=TT width="80%">Updates both remote and external references</TD></TR></TBODY></TABLE>

 

[MartinL]

Norie

Changing UpdateLinks makes it run better

But I see that the remote spreadsheet is now recalcing everytime it is opened
and every time it gets to wb1.close it asks to be saved
this on top of the time taken for it to open and close make it a very slow process.

maybe linking this to the dropdown is not the best idea, I need to think about placing this code in a position where it runs once when the sheet is opened but active.workbook is not the right place to do this.

 

[Norie]

Turn off Calculations, disable events.

Application.Calculations =xlManualCalculation ' not sure if that's the right constant but it something like that
 
Application.EnableEvents = False
 
' open workbooks
' run code
' close workbooks
 
Application.Calculations =xlAutomaticCalculation ' not sure if that's the right constant but it something like that
 
Application.EnableEvents = True

Note re-enabling events is v. important.

 

[MartinL]

I just want to die.....
I've been round and round in circles for about a week now

Ive updated my Dropdown box code to this:

Private Sub ComboBoxPremiumProduct_Change()
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
ChDir "L:\Customers\A&B\A&B Stats"
Set wb1 = Workbooks.Open(Filename:="L:\Customers\A&B\A&B Stats\DatabaseA&B.xls", UpdateLinks:=0)

' open workbooks
' run code
' close workbooks

wb1.Close
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

But it takes about 2 mins before the screen comes back to life
If the file stays open it takes about a second.
if the user uses the up/down arrows on the dropdown box they are totally screwed as excel remembers every time the button is pushed and iterates the code.

For some reason the links to my other spreadsheets work fine without being included in this way.

 

[Norie]

Why are you using the combobox change event?

 

[MartinL]

Hi Norie

Your question may very well be the answer

The combobox is the reason for the code to be run.
It allows the user to select an item, a second combobox allows a user to select a date.
Both are used so only valid selections can be made and no spelling mistakes are made.

When the user has made their selection around 360 generic values are displayed.

4 week this year and last year
12 week this year and last year
52 week this year and last year

over 10 retailers
and 6 formats

amount sold (£)
amount sold (kg)
average spend per buyer
average price per volume
purchase frequency
penetration %

So by adding the code to the item combobox wont really work as the item may be selected then the date changed and the vba will not then be run.

This code is just used for a single retailer whose data is held on a different spreadsheet although there will be another 4 retailers joining shortly but they will be all together on yet another spreadsheet.

Its another day and Ive just booted up and read your reply so after this coffee I'm gonna start thinking again. Sleep did not help me this time.