PDA

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!

ikk
01-05-2008, 01:05 PM
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!