Opening Workbook through command button

[xluser2007]

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]

[VBA]Private Sub CommandButton1_Click()
Workbooks.Open ("C:\Documents\WorkbookA.xls")
End Sub[/VBA]

[xluser2007]

Thanks Simon, much appreciated .

[xluser2007]

Just revisiting the above thread. I was wondering what is the syntax to use with Simon's code above to prevent updating links?

[xluser2007]

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:

[vba]

Sub openwkbks()

Workbooks.Open Filename:=Range("A1").Value, UpdateLinks:=0
Workbooks.Open Filename:=Range("A2").Value, UpdateLinks:=0

End Sub

[/vba]

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:


[vba]

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

[/vba]

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]

That ius because the newly opened workbook becomes active, and the code will then look there for the next filename

Use

[vba]

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
[/vba]

[Andy Pope]

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.

[vba]
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
[/vba]

[Simon Lloyd]

[VBA]
Private Sub CommandButton1_Click()
Workbooks.Open ("C:\Documents\WorkbookA.xls"),UpdateLinks:=False
End Sub
[/VBA]

[Bob Phillips]

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]

Threads merged

[Simon Lloyd]

Hmmmmm, merged threads......confuses the simpler folk......like me!

[ikk]

helpfull for me ,as newbie

[xluser2007]

Thanks Simon, xld, Andy and lucas,

Helpful as always!

Also thanks for combining the threads Simon and posting the relevant code for my other query.

regards

[Simon Lloyd]

I'll accept the thanks but even more confused now....guess my single braincell has little room for lateral accomplishments!