Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On opening a file auto open another
I know this should be the simplest thing but I'm missing something.
When I open the file "accounting.xls" I want it to open the file "cashups.xls" automatically. Now... I've looked up VB help and as a result I have typed in Workbooks.Open "cashups.xls", 0 but it didnt work. I have presumed it's typed into the window that opens when you right click on the Excel icon and select "show code" in the main worksheet tab. The syntax in help is unclear as always, but I don't think it requires the brackets shown in help. I have tried them anyway, and I get an error message saying an "=" sign is expected. I've had a look in this thread and found a more functional method if I can get it to work, and this is: Dim wbk As Workbook On Error Resume Next Set wbk = Workbooks("Cashups.xls") On Error GoTo 0 If wbk Is Nothing Then MsgBox "Opening Cashups.xls now" Set wbk = Workbooks.Open("C:\MyBook.xls") End If I copied and pasted this into the same window, just changing the example to my own filename and remming out my first attempt, but it still wouldn't open the other file. Can someone tell me what I did wrong? Also, is there a way of doing this so that whichever tab the "accounting.xls" file opens to the macro will still run? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On opening a file auto open another
insert yourself a module in accounting.xls and paste the following
Sub auto_open() Workbooks.Open ("cashups.xls") End Sub -- Gary "Kevryl" wrote in message ... I know this should be the simplest thing but I'm missing something. When I open the file "accounting.xls" I want it to open the file "cashups.xls" automatically. Now... I've looked up VB help and as a result I have typed in Workbooks.Open "cashups.xls", 0 but it didnt work. I have presumed it's typed into the window that opens when you right click on the Excel icon and select "show code" in the main worksheet tab. The syntax in help is unclear as always, but I don't think it requires the brackets shown in help. I have tried them anyway, and I get an error message saying an "=" sign is expected. I've had a look in this thread and found a more functional method if I can get it to work, and this is: Dim wbk As Workbook On Error Resume Next Set wbk = Workbooks("Cashups.xls") On Error GoTo 0 If wbk Is Nothing Then MsgBox "Opening Cashups.xls now" Set wbk = Workbooks.Open("C:\MyBook.xls") End If I copied and pasted this into the same window, just changing the example to my own filename and remming out my first attempt, but it still wouldn't open the other file. Can someone tell me what I did wrong? Also, is there a way of doing this so that whichever tab the "accounting.xls" file opens to the macro will still run? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On opening a file auto open another
?B?S2V2cnls?= wrote in
: I know this should be the simplest thing but I'm missing something. When I open the file "accounting.xls" I want it to open the file "cashups.xls" automatically. Now... I've looked up VB help and as a result I have typed in Workbooks.Open "cashups.xls", 0 but it didnt work. I have presumed it's typed into the window that opens when you right click on the Excel icon and select "show code" in the main worksheet tab. When you see that window, it should (if it has no code) show: Private Sub Workbook_Open() End Sub If you paste your code between those two lines, it should work. Private Sub Workbook_Open() Workbooks.Open "cashups.xls" End Sub Add the path, e.g. Workbooks.Open "c:MyFolder\cashups.xls" There are MANY other options, but this should do what you want. Add this line before the End Sub to show your original Workbook. Workbooks("accounting.xls").Activate Cheers, Alan Carpenter The syntax in help is unclear as always, but I don't think it requires the brackets shown in help. I have tried them anyway, and I get an error message saying an "=" sign is expected. I've had a look in this thread and found a more functional method if I can get it to work, and this is: Dim wbk As Workbook On Error Resume Next Set wbk = Workbooks("Cashups.xls") On Error GoTo 0 If wbk Is Nothing Then MsgBox "Opening Cashups.xls now" Set wbk = Workbooks.Open("C:\MyBook.xls") End If I copied and pasted this into the same window, just changing the example to my own filename and remming out my first attempt, but it still wouldn't open the other file. Can someone tell me what I did wrong? Also, is there a way of doing this so that whichever tab the "accounting.xls" file opens to the macro will still run? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On opening a file auto open another
Thank you Alan. I have actually used Gary's solution so that the thing works
whichver tab the spreadsheet is opened in. Howver your information will be very useful for other purposes. Cheers, Kevryl "Alan Carpenter" wrote: When you see that window, it should (if it has no code) show: Private Sub Workbook_Open() End Sub If you paste your code between those two lines, it should work. Private Sub Workbook_Open() Workbooks.Open "cashups.xls" End Sub Add the path, e.g. Workbooks.Open "c:MyFolder\cashups.xls" There are MANY other options, but this should do what you want. Add this line before the End Sub to show your original Workbook. Workbooks("accounting.xls").Activate Cheers, Alan Carpenter The syntax in help is unclear as always, but I don't think it requires the brackets shown in help. I have tried them anyway, and I get an error message saying an "=" sign is expected. I've had a look in this thread and found a more functional method if I can get it to work, and this is: Dim wbk As Workbook On Error Resume Next Set wbk = Workbooks("Cashups.xls") On Error GoTo 0 If wbk Is Nothing Then MsgBox "Opening Cashups.xls now" Set wbk = Workbooks.Open("C:\MyBook.xls") End If I copied and pasted this into the same window, just changing the example to my own filename and remming out my first attempt, but it still wouldn't open the other file. Can someone tell me what I did wrong? Also, is there a way of doing this so that whichever tab the "accounting.xls" file opens to the macro will still run? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On opening a file auto open another
Thanks Gary. Works like a charm.
Cheers, Kevryl "Gary Keramidas" wrote: insert yourself a module in accounting.xls and paste the following Sub auto_open() Workbooks.Open ("cashups.xls") End Sub -- Gary "Kevryl" wrote in message ... I know this should be the simplest thing but I'm missing something. When I open the file "accounting.xls" I want it to open the file "cashups.xls" automatically. Now... I've looked up VB help and as a result I have typed in Workbooks.Open "cashups.xls", 0 but it didnt work. I have presumed it's typed into the window that opens when you right click on the Excel icon and select "show code" in the main worksheet tab. The syntax in help is unclear as always, but I don't think it requires the brackets shown in help. I have tried them anyway, and I get an error message saying an "=" sign is expected. I've had a look in this thread and found a more functional method if I can get it to work, and this is: Dim wbk As Workbook On Error Resume Next Set wbk = Workbooks("Cashups.xls") On Error GoTo 0 If wbk Is Nothing Then MsgBox "Opening Cashups.xls now" Set wbk = Workbooks.Open("C:\MyBook.xls") End If I copied and pasted this into the same window, just changing the example to my own filename and remming out my first attempt, but it still wouldn't open the other file. Can someone tell me what I did wrong? Also, is there a way of doing this so that whichever tab the "accounting.xls" file opens to the macro will still run? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening a file in Excel starts application but dose not open file | Excel Discussion (Misc queries) | |||
auto prompt file insert object when opening a template | Excel Programming | |||
when opening a .xls file it seems to want to open in word, what d | Excel Discussion (Misc queries) | |||
Auto opening of a EXcel file on a CD | Excel Programming |