Open a file and execute code

[BlairH]

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]

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]

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]

Thanks, Dave.

Everything works except the application.run statment gives a mcaro not found
error.

Blair

 

[Dave Peterson]

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]

Test_code_exec is in Module1 of NCR_db.xls and is defined as a Public Sub.

 

[Dave Peterson]

I'm still guessing that you have a typo somewhere.

 

[BlairH]

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]

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.