View Full Version : Opening Workbook through command button
xluser2007
12-08-2007, 05:16 PM
Hi All,
If I want to open say: C:\Documents\WorkbookA.xls via a command button what is the code you use (If I have the command button already set up)?
Thanks all.
Simon Lloyd
12-08-2007, 05:22 PM
Private Sub CommandButton1_Click()
Workbooks.Open ("C:\Documents\WorkbookA.xls")
End Sub
xluser2007
12-08-2007, 08:44 PM
Thanks Simon, much appreciated :).
xluser2007
01-05-2008, 01:00 AM
Just revisiting the above thread. I was wondering what is the syntax to use with Simon's code above to prevent updating links?
xluser2007
01-05-2008, 07:47 AM
Hi aLL,
I have been trying to code the folowing macro to open a few workbooks without updating external links.
Although simple enough I keep running into a 1004 error.
As an example,
A1 = C:\Documents and Settings\LongFilenameA.xls
A2 = C:\Documents and Settings\LongFilenameB.xls
Now I use the following code:
Sub openwkbks()
Workbooks.Open Filename:=Range("A1").Value, UpdateLinks:=0
Workbooks.Open Filename:=Range("A2").Value, UpdateLinks:=0
End Sub
It opens A1 fine, but does not open A2 workbook and runs into the 1004 runtime error (see attached picture).
Could Anyone please help me correct the above macro so as to ask it it to properly open A1 and A2 filepaths correctly?
[Note: If I use the following code:
Sub openwkbks()
Workbooks.Open Filename:="C:\Documents and Settings\LongFilenameA.xls", UpdateLinks:=0
Workbooks.Open Filename:="C:\Documents and Settings\LongFilenameB.xls", UpdateLinks:=0
End Sub
This works, but doesn't serve my purpose as I ideally want to open A1:A10 every months and them in the VBA code]
Regards
Bob Phillips
01-05-2008, 08:02 AM
That ius because the newly opened workbook becomes active, and the code will then look there for the next filename
Use
Sub openwkbks()
Dim This As Worksheet
Set This = Activesheet
Workbooks.Open Filename:=This.Range("A1").Value, UpdateLinks:=0
Workbooks.Open Filename:=This.Range("A2").Value, UpdateLinks:=0
End Sub
Andy Pope
01-05-2008, 08:04 AM
The reason the first line works and subsequent ones fail is that your range reference is not fully qualified. It is using the active sheet as a reference.
One the first workbook is opened the A2 is that of the recently opened workbook not the one with the relevant data.
So set a reference to the sheet and use it.
Sub openwkbks()
Dim shtUse as worksheet
set shtUse = Activeworksheet
Workbooks.Open Filename:=shtUse.Range("A1").Value, UpdateLinks:=0
Workbooks.Open Filename:=shtUse.Range("A2").Value, UpdateLinks:=0
End Sub
Simon Lloyd
01-05-2008, 09:40 AM
Private Sub CommandButton1_Click()
Workbooks.Open ("C:\Documents\WorkbookA.xls"),UpdateLinks:=False
End Sub
Bob Phillips
01-05-2008, 09:46 AM
Just revisiting the above thread. I was wondering what is the syntax to use with Simon's code above to prevent updating links?
The same as the code that you used in your other thread!
lucas
01-05-2008, 09:54 AM
Threads merged
Simon Lloyd
01-05-2008, 10:04 AM
Hmmmmm, merged threads......confuses the simpler folk......like me!
helpfull for me ,as newbie
xluser2007
01-05-2008, 03:07 PM
Thanks Simon, xld, Andy and lucas,
Helpful as always! :content:
Also thanks for combining the threads Simon and posting the relevant code for my other query.
regards
Simon Lloyd
01-06-2008, 09:37 AM
I'll accept the thanks but even more confused now....guess my single braincell has little room for lateral accomplishments!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.