Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a file and execute code
I'm trying to open a file and execute code in that file. Here's what I've got:
Private Sub Workbook_Open() WName = ActiveWorkbook.Name LogFileName = "C:\NCR_db.xls" ' Set the name and location of the log file Workbooks.Open Filename:=LogFileName ' open the log file LFName = ActiveWorkbook.Name ' return the name of the workbook Workbooks(WName).Activate Call Test_code_exec ' pass control to the main code End Sub I keep getting an undefined Sub or Function error. The file doesn't open so it look slike the error occurs before the code exexcutes - VB is trying to identify all of its calls before execution, and since this is in a different file it doesn't recognize it. The Declare statement allows you to identify external subroutines in DLLs, but can you specify that the code is external in another VB Project / Excel file? This is in XL2003. Thanks, Blair |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a file and execute code
So your "test_code_exec" procedure is in that NCR_db.xls workbook?
If yes, I'd use: Option Explicit Private Sub Workbook_Open() Dim Wkbk as workbook dim LogFileName as string 'Set the name and location of the log file LogFileName = "C:\NCR_db.xls" set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=LogFileName) on error goto 0 'check to see if it opened ok if wkbk is nothing then msgbox logfilename & " didn't open!" else 'me is the object that owns the code me.activate 'run the code in the workbook that this code opened application.run "'" & wkbk.name & "'!Test_code_exec" end if End Sub (Untested, uncompiled. Watch for typos.) BlairH wrote: I'm trying to open a file and execute code in that file. Here's what I've got: Private Sub Workbook_Open() WName = ActiveWorkbook.Name LogFileName = "C:\NCR_db.xls" ' Set the name and location of the log file Workbooks.Open Filename:=LogFileName ' open the log file LFName = ActiveWorkbook.Name ' return the name of the workbook Workbooks(WName).Activate Call Test_code_exec ' pass control to the main code End Sub I keep getting an undefined Sub or Function error. The file doesn't open so it look slike the error occurs before the code exexcutes - VB is trying to identify all of its calls before execution, and since this is in a different file it doesn't recognize it. The Declare statement allows you to identify external subroutines in DLLs, but can you specify that the code is external in another VB Project / Excel file? This is in XL2003. Thanks, Blair -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a file and execute code
For a bit of background info, I want to create a template file that users
will fill out as a form. This will then execute code to copy the info to a separate log file stored on a network server. I want to have the code in the log file as opposed to the template file so that it can be more readily updated. For various reasons I am unable to do this in MSAccess, which would be orders of magnitude easier. Thanks! "BlairH" wrote: I'm trying to open a file and execute code in that file. Here's what I've got: Private Sub Workbook_Open() WName = ActiveWorkbook.Name LogFileName = "C:\NCR_db.xls" ' Set the name and location of the log file Workbooks.Open Filename:=LogFileName ' open the log file LFName = ActiveWorkbook.Name ' return the name of the workbook Workbooks(WName).Activate Call Test_code_exec ' pass control to the main code End Sub I keep getting an undefined Sub or Function error. The file doesn't open so it look slike the error occurs before the code exexcutes - VB is trying to identify all of its calls before execution, and since this is in a different file it doesn't recognize it. The Declare statement allows you to identify external subroutines in DLLs, but can you specify that the code is external in another VB Project / Excel file? This is in XL2003. Thanks, Blair |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a file and execute code
Thanks, Dave.
Everything works except the application.run statment gives a mcaro not found error. Blair "Dave Peterson" wrote: So your "test_code_exec" procedure is in that NCR_db.xls workbook? If yes, I'd use: Option Explicit Private Sub Workbook_Open() Dim Wkbk as workbook dim LogFileName as string 'Set the name and location of the log file LogFileName = "C:\NCR_db.xls" set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=LogFileName) on error goto 0 'check to see if it opened ok if wkbk is nothing then msgbox logfilename & " didn't open!" else 'me is the object that owns the code me.activate 'run the code in the workbook that this code opened application.run "'" & wkbk.name & "'!Test_code_exec" end if End Sub (Untested, uncompiled. Watch for typos.) BlairH wrote: I'm trying to open a file and execute code in that file. Here's what I've got: Private Sub Workbook_Open() WName = ActiveWorkbook.Name LogFileName = "C:\NCR_db.xls" ' Set the name and location of the log file Workbooks.Open Filename:=LogFileName ' open the log file LFName = ActiveWorkbook.Name ' return the name of the workbook Workbooks(WName).Activate Call Test_code_exec ' pass control to the main code End Sub I keep getting an undefined Sub or Function error. The file doesn't open so it look slike the error occurs before the code exexcutes - VB is trying to identify all of its calls before execution, and since this is in a different file it doesn't recognize it. The Declare statement allows you to identify external subroutines in DLLs, but can you specify that the code is external in another VB Project / Excel file? This is in XL2003. Thanks, Blair -- Dave Peterson . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a file and execute code
What's the name of the macro you want to run?
Is it in the C:\NCR_db.xls workbook? Is it in a General module? (I'd check for common typos first.) BlairH wrote: Thanks, Dave. Everything works except the application.run statment gives a mcaro not found error. Blair "Dave Peterson" wrote: So your "test_code_exec" procedure is in that NCR_db.xls workbook? If yes, I'd use: Option Explicit Private Sub Workbook_Open() Dim Wkbk as workbook dim LogFileName as string 'Set the name and location of the log file LogFileName = "C:\NCR_db.xls" set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=LogFileName) on error goto 0 'check to see if it opened ok if wkbk is nothing then msgbox logfilename & " didn't open!" else 'me is the object that owns the code me.activate 'run the code in the workbook that this code opened application.run "'" & wkbk.name & "'!Test_code_exec" end if End Sub (Untested, uncompiled. Watch for typos.) BlairH wrote: I'm trying to open a file and execute code in that file. Here's what I've got: Private Sub Workbook_Open() WName = ActiveWorkbook.Name LogFileName = "C:\NCR_db.xls" ' Set the name and location of the log file Workbooks.Open Filename:=LogFileName ' open the log file LFName = ActiveWorkbook.Name ' return the name of the workbook Workbooks(WName).Activate Call Test_code_exec ' pass control to the main code End Sub I keep getting an undefined Sub or Function error. The file doesn't open so it look slike the error occurs before the code exexcutes - VB is trying to identify all of its calls before execution, and since this is in a different file it doesn't recognize it. The Declare statement allows you to identify external subroutines in DLLs, but can you specify that the code is external in another VB Project / Excel file? This is in XL2003. Thanks, Blair -- Dave Peterson . -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a file and execute code
Test_code_exec is in Module1 of NCR_db.xls and is defined as a Public Sub.
"Dave Peterson" wrote: So your "test_code_exec" procedure is in that NCR_db.xls workbook? If yes, I'd use: Option Explicit Private Sub Workbook_Open() Dim Wkbk as workbook dim LogFileName as string 'Set the name and location of the log file LogFileName = "C:\NCR_db.xls" set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=LogFileName) on error goto 0 'check to see if it opened ok if wkbk is nothing then msgbox logfilename & " didn't open!" else 'me is the object that owns the code me.activate 'run the code in the workbook that this code opened application.run "'" & wkbk.name & "'!Test_code_exec" end if End Sub (Untested, uncompiled. Watch for typos.) BlairH wrote: I'm trying to open a file and execute code in that file. Here's what I've got: Private Sub Workbook_Open() WName = ActiveWorkbook.Name LogFileName = "C:\NCR_db.xls" ' Set the name and location of the log file Workbooks.Open Filename:=LogFileName ' open the log file LFName = ActiveWorkbook.Name ' return the name of the workbook Workbooks(WName).Activate Call Test_code_exec ' pass control to the main code End Sub I keep getting an undefined Sub or Function error. The file doesn't open so it look slike the error occurs before the code exexcutes - VB is trying to identify all of its calls before execution, and since this is in a different file it doesn't recognize it. The Declare statement allows you to identify external subroutines in DLLs, but can you specify that the code is external in another VB Project / Excel file? This is in XL2003. Thanks, Blair -- Dave Peterson . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a file and execute code
I'm still guessing that you have a typo somewhere.
BlairH wrote: Test_code_exec is in Module1 of NCR_db.xls and is defined as a Public Sub. "Dave Peterson" wrote: So your "test_code_exec" procedure is in that NCR_db.xls workbook? If yes, I'd use: Option Explicit Private Sub Workbook_Open() Dim Wkbk as workbook dim LogFileName as string 'Set the name and location of the log file LogFileName = "C:\NCR_db.xls" set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=LogFileName) on error goto 0 'check to see if it opened ok if wkbk is nothing then msgbox logfilename & " didn't open!" else 'me is the object that owns the code me.activate 'run the code in the workbook that this code opened application.run "'" & wkbk.name & "'!Test_code_exec" end if End Sub (Untested, uncompiled. Watch for typos.) BlairH wrote: I'm trying to open a file and execute code in that file. Here's what I've got: Private Sub Workbook_Open() WName = ActiveWorkbook.Name LogFileName = "C:\NCR_db.xls" ' Set the name and location of the log file Workbooks.Open Filename:=LogFileName ' open the log file LFName = ActiveWorkbook.Name ' return the name of the workbook Workbooks(WName).Activate Call Test_code_exec ' pass control to the main code End Sub I keep getting an undefined Sub or Function error. The file doesn't open so it look slike the error occurs before the code exexcutes - VB is trying to identify all of its calls before execution, and since this is in a different file it doesn't recognize it. The Declare statement allows you to identify external subroutines in DLLs, but can you specify that the code is external in another VB Project / Excel file? This is in XL2003. Thanks, Blair -- Dave Peterson . -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a file and execute code
Thanks for your help on this Dave.
I seem to have it working. Apparently it wants you to specify the name of the module. It works if the Run command looks like this: application.run "'" & wkbk.name & "'!Module1.Test_code_exec" Blair "Dave Peterson" wrote: I'm still guessing that you have a typo somewhere. BlairH wrote: Test_code_exec is in Module1 of NCR_db.xls and is defined as a Public Sub. "Dave Peterson" wrote: So your "test_code_exec" procedure is in that NCR_db.xls workbook? If yes, I'd use: Option Explicit Private Sub Workbook_Open() Dim Wkbk as workbook dim LogFileName as string 'Set the name and location of the log file LogFileName = "C:\NCR_db.xls" set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=LogFileName) on error goto 0 'check to see if it opened ok if wkbk is nothing then msgbox logfilename & " didn't open!" else 'me is the object that owns the code me.activate 'run the code in the workbook that this code opened application.run "'" & wkbk.name & "'!Test_code_exec" end if End Sub (Untested, uncompiled. Watch for typos.) BlairH wrote: I'm trying to open a file and execute code in that file. Here's what I've got: Private Sub Workbook_Open() WName = ActiveWorkbook.Name LogFileName = "C:\NCR_db.xls" ' Set the name and location of the log file Workbooks.Open Filename:=LogFileName ' open the log file LFName = ActiveWorkbook.Name ' return the name of the workbook Workbooks(WName).Activate Call Test_code_exec ' pass control to the main code End Sub I keep getting an undefined Sub or Function error. The file doesn't open so it look slike the error occurs before the code exexcutes - VB is trying to identify all of its calls before execution, and since this is in a different file it doesn't recognize it. The Declare statement allows you to identify external subroutines in DLLs, but can you specify that the code is external in another VB Project / Excel file? This is in XL2003. Thanks, Blair -- Dave Peterson . -- Dave Peterson . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open a file and execute code
My bet is that you have two procedures (in different modules) that have the same
name. If I'm right, I'd go back and fix it. BlairH wrote: Thanks for your help on this Dave. I seem to have it working. Apparently it wants you to specify the name of the module. It works if the Run command looks like this: application.run "'" & wkbk.name & "'!Module1.Test_code_exec" Blair "Dave Peterson" wrote: I'm still guessing that you have a typo somewhere. BlairH wrote: Test_code_exec is in Module1 of NCR_db.xls and is defined as a Public Sub. "Dave Peterson" wrote: So your "test_code_exec" procedure is in that NCR_db.xls workbook? If yes, I'd use: Option Explicit Private Sub Workbook_Open() Dim Wkbk as workbook dim LogFileName as string 'Set the name and location of the log file LogFileName = "C:\NCR_db.xls" set wkbk = nothing on error resume next set wkbk = Workbooks.Open(Filename:=LogFileName) on error goto 0 'check to see if it opened ok if wkbk is nothing then msgbox logfilename & " didn't open!" else 'me is the object that owns the code me.activate 'run the code in the workbook that this code opened application.run "'" & wkbk.name & "'!Test_code_exec" end if End Sub (Untested, uncompiled. Watch for typos.) BlairH wrote: I'm trying to open a file and execute code in that file. Here's what I've got: Private Sub Workbook_Open() WName = ActiveWorkbook.Name LogFileName = "C:\NCR_db.xls" ' Set the name and location of the log file Workbooks.Open Filename:=LogFileName ' open the log file LFName = ActiveWorkbook.Name ' return the name of the workbook Workbooks(WName).Activate Call Test_code_exec ' pass control to the main code End Sub I keep getting an undefined Sub or Function error. The file doesn't open so it look slike the error occurs before the code exexcutes - VB is trying to identify all of its calls before execution, and since this is in a different file it doesn't recognize it. The Declare statement allows you to identify external subroutines in DLLs, but can you specify that the code is external in another VB Project / Excel file? This is in XL2003. Thanks, Blair -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open txt file using vba code | Excel Programming | |||
Execute VB code against certain open workbooks | Excel Programming | |||
Execute code from book1 of code from book2 | Excel Programming | |||
How to open a zip file from the code? | Excel Programming | |||
VBA code to open file? | Excel Programming |