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

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

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

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
.

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

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
.

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

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
.

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