I am not sure where I have gone wrong with codes below. Every time I run it, I get a run time error 1004 ... the macro cannot be found; but the macro file is there!

file_name = Userform1.ListBox1.Value & ".xla"
pgm2run = file_name & "!StartPgm"

Workbooks.Open Filename:=file_name
Application.Run pgm2run

I am using Excel 2002 on Windows XP Professional. PLease help. TIA

Hi Dave, Thanks for your suggestion.

Anyway I have managed to overcome the run time error. For the benefits of other users, here is how I did it. I changed lne #2 of the code to pgm2run = "Startpgm" from pgm2run=file_name & "!StartPgm"; dropping the file_name. Here is the new code -

file_name = Userform1.ListBox1.Value & ".xla"
pgm2run = "StartPgm"
Workbooks.Open Filename:=file_name
Application.Run pgm2run


"Dave Newing" wrote:

Hi Francis,

I think run time error 1004 is file does not exist.

Check that the file string you are looking for is correct.
One way of doing this is to print the variable 'file_name' to your immediate window by adding:

Debug.Print file_name

After:

file_name = Userform1.ListBox1.Value & ".xla"

Your immediate window can be opened by pressing Ctrl+G.






"Francis Ang" wrote:

I am not sure where I have gone wrong with codes below. Every time I run it, I get a run time error 1004 ... the macro cannot be found; but the macro file is there!

file_name = Userform1.ListBox1.Value & ".xla"
pgm2run = file_name & "!StartPgm"

Workbooks.Open Filename:=file_name
Application.Run pgm2run

I am using Excel 2002 on Windows XP Professional. PLease help. TIA