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.

 

[Ivan F Moala]

You can always hide the workbook....

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

ActiveWindow.Visible = False


End Sub

 

[MartinL]

Hi

Thanks for the quick reply, Sorry I didnt answer earlier I had a client meeting.
Your suggestion was quite interesting as after I press the command button you can see the spreadsheet open but then it appears to close but the data updates fantastically each time I change the criteria.

However if the button is pressed again it tries to open the spreadsheet again and a message box appears to warn you. Can this be checked for in the VBA: if ABC.xls is open then do nothing.....that sort of thing?

 

[MartinL]

Hi

I am trying to calculate values from these three closed spreadsheets.
I have added this to my spreadsheet but it does not appear to work.
What I mean is, I still have to physically open them before my spreadsheet will populate until then I get #N/A

In Edit\Edit links\Startup Prompt I have set don't display the alert and update Links
I am assuming this means update all links but don't bother telling me

And in Options\Calculation I have set automatic calculation ON

Private Sub Worksheet_Activate()
' OpenLinkedDB Macro
' Macro recorded 21/04/2009 by martin.lucas
'
' Open Special Retailer1
'
ChDir "L:\Customers\A&B\A&B Stats"
Workbooks.Open Filename:="DatabaseA&B.xls", _
UpdateLinks:=1
ActiveWindow.Visible = False

' OpenPremiumdb
ChDir ":\Performance Data\Market Data"
Workbooks.Open Filename:="Premium_Report.xls", _
UpdateLinks:=1
ActiveWindow.Visible = False

' OpenPrepareddb
ChDir ":\Performance Data\TNS Market Data"
Workbooks.Open Filename:="Prepared_Report.xls", _
UpdateLinks:=1
ActiveWindow.Visible = False

End Sub

Is there a problem with what I am trying to do ie open 3 or more spreadsheets in this fashion

 

[MartinL]

I see from a previous thread that yopu can only open 1 workbook with this method. Is there a way around this as I need to open 3 now and possibly 4
I think one workbook is opening as some of my values appear to be updating.

Oh my head!

 

[Epicfail]

I use this to update links in my workbooks instead of having them open and then hide in the background. But I only have one sheet to link to. Might b able to double ud the update link for multiple sheets. IF not let me know so I dont scratch my head later lol......


Private Sub Worksheet_Change(ByVal Target As Range)
'Adjust Range(" : ") according to needs
If Not Intersect(Target, Range(" ")) Is Nothing Then
ActiveWorkbook.UpdateLink Name:= _
" your file path.xlsx", Type:= _xlExcelLinks
End If
End Sub

 

[Norie]

Why not just turn off ScreenUpdating, open the file(s), do your thing, close the file(s) and turn ScreenUpdating back on?

Application.ScreenUpdating = False
 
ChDir "L:\Customers\A&B\A&B Stats"
 
Set wb1 = Workbooks.Open (Filename:="DatabaseA&B.xls", UpdateLinks:=1)
 
' OpenPremiumdb
ChDir ":\Performance Data\Market Data"
Set wb2 = Workbooks.Open (Filename:="Premium_Report.xls", UpdateLinks:=1)
 
' OpenPrepareddb
ChDir ":\Performance Data\TNS Market Data"
Set wb3 = Workbooks.Open (Filename:="Prepared_Report.xls", UpdateLinks:=1)
 
' you should now have the 3 workbooks open and you can reference them using wb1-3, but I would recommend you use more descriptive names
 
 
' run code
 
' close workbooks
 
wb1.Close
wb2.Close
wb3.Close
 
Application.Screenupdating = True

 

[MartinL]

Hi

Type:= _xlExcelLinks

I think this is a 2007 thing
I'm using 2003 (

Thanks for replying though
Since your option I've also looked at

ActiveWorkbook.UpdateRemoteReferences
and ActiveWorkbook.UpdateLinks

but have no idea how these may work
My Mr Walkenbach does not seem to cover them in my old 2002 manuals time.
Methinks its time to get a new set of books

 

[Norie]

Martin

xlExcelLinks is a VBA constant equal to 2, it's available at least as far back as Excel 2000.

 

[MartinL]

Hi Norie

I have added your code to a dropdown box
The theory being that when a change is made the code runs

For test reasons I used just one workbook so my code looks like this

Application.ScreenUpdating = False

ChDir "L:\Customers\A&B\A&B Stats"

Set wb1 = Workbooks.Open (Filename:="DatabaseA&B.xls", UpdateLinks:=1)


' you should now have the 3 workbooks open and you can reference them using wb1-3, but I would recommend you use more descriptive names


' run code

' close workbooks

wb1.Close

Application.Screenupdating = True
However it has brought my spreadsheet to a standstill.
after 20 mins its still not responding