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.
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.
[VBA]Private Sub CommandButton1_Click()
Workbooks.Open ("C:\Documents\WorkbookA.xls")
End Sub[/VBA]
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
Thanks Simon, much appreciated .
Just revisiting the above thread. I was wondering what is the syntax to use with Simon's code above to prevent updating links?
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
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]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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]
Cheers
Andy
[VBA]
Private Sub CommandButton1_Click()
Workbooks.Open ("C:\Documents\WorkbookA.xls"),UpdateLinks:=False
End Sub
[/VBA]
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
The same as the code that you used in your other thread!Originally Posted by xluser2007
Last edited by Bob Phillips; 01-05-2008 at 01:17 PM.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Threads merged
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Hmmmmm, merged threads......confuses the simpler folk......like me!
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
helpfull for me ,as newbie
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
I'll accept the thanks but even more confused now....guess my single braincell has little room for lateral accomplishments!
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)