Calling an external sub dynamically.

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hello All,

I am using VB to call a particular SUB of different files.
I have the following code working fine. I need help to modify the 4th
line to make it dynamic.

'***********************************

Workbooks.Open (FullFolder)
Workbooks(FileName).Activate

ActiveWorkbook.Worksheets("Blank").Activate
Application.Run "Feature_Compare_VB.xls!ShowForm"

'***********************************

FullFolder is the Full address of the destination file.
FileName is the Name of the Destination File.
ShowForm is the common SUB that I have in all my destination files.

I want to modify the 4th line to something like this... (use the
variable FileName instead of explicitly specifying the file name.
Application.Run FileName & "!ShowForm"


Thanks
Joe
 
Application.Run (myFile & "!ShowForm")

where myFile is the full name for the external book
 
 Application.Run (myFile & "!ShowForm")

where myFile is the full name for the external book

--

Regards,
Nigel
(e-mail address removed)










- Show quoted text -
Click to expand...

Thanks Nigel..
It only opens the destination file. Its not executing the Sub -
Showform.
I have that SUB to initate showing a Form Window. Its not doing that.

Does anyone know why it maynot be working !!

Pls...

Joe
 
Your pseudo code is on target, but I'd tweak it a bit:

Application.Run "'" & Replace(FileName,"'","''") & "'!ShowForm"

Quotes around the Filename ensure the macro is callable if there are or are
not spaces in the workbook, e.g. My Book.xls, and the single-quote doubling
ensures it is callable if there are single quotes in the workbook, e.g.
Pete's Book.xls. Application.Run "Pete's Book.xls!ShowForm" won't work.
Application.Run "'Pete''s Book.xls'!ShowForm" will.
 
Your pseudo code is on target, but I'd tweak it a bit:

Application.Run "'" & Replace(FileName,"'","''") & "'!ShowForm"

Quotes around the Filename ensure the macro is callable if there are or are
not spaces in the workbook, e.g. My Book.xls, and the single-quote doubling
ensures it is callable if there are single quotes in the workbook, e.g.
Pete's Book.xls. Application.Run "Pete's Book.xls!ShowForm" won't work.
Application.Run "'Pete''s Book.xls'!ShowForm" will.

--
Tim Zych
SF, CA










- Show quoted text -
Click to expand...

Thanks Tim.. But i doubt something..
One " is missing in the begining.. How do we insert a Double Quote?

Application.Run "'" & Replace(FileName,"'","''") & "'!ShowForm" ???

Thanks
Joe
 
Workbooks.Open (FullFolder)
Workbooks(FileName).Activate

ActiveWorkbook.Worksheets("Blank").Activate
Application.Run "'" & Activeworkbook.Name & "'!ShowForm"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



Your pseudo code is on target, but I'd tweak it a bit:

Application.Run "'" & Replace(FileName,"'","''") & "'!ShowForm"

Quotes around the Filename ensure the macro is callable if there are or
are
not spaces in the workbook, e.g. My Book.xls, and the single-quote
doubling
ensures it is callable if there are single quotes in the workbook, e.g.
Pete's Book.xls. Application.Run "Pete's Book.xls!ShowForm" won't work.
Application.Run "'Pete''s Book.xls'!ShowForm" will.

--
Tim Zych
SF, CA










- Show quoted text -
Click to expand...

Thanks Tim.. But i doubt something..
One " is missing in the begining.. How do we insert a Double Quote?

Application.Run "'" & Replace(FileName,"'","''") & "'!ShowForm" ???

Thanks
Joe
 
I had a similar question the otehr day, see:
http://groups.google.com/group/micr...274fddca635/b9da5722fdb3af0a#b9da5722fdb3af0a

and I got a similar response to the above, I ended up using this in my
procedure:

Sub CallProcedureIn(WkBook as String, Procedure as STring)
Application.Run "'" & WkBook & "'!" & Procedure
End Sub

so taht's assuming you know / can get the workbook name, and you know
the Procedure name. You could even get the names of all the available
procedures, but then you'll be heading towards recreating the vba /
excel macro button...
 
I'd use a variation of Bob Philip's code:

dim otherwkbk as workbook
set otherwkbk = workbooks.open(filename:=fullfolder)
otherwkbk.activate 'shouldn't be necessary, it should already be active
otherwkbk.Worksheets("Blank").select
Application.Run "'" & otherwkbk.name & "'!ShowForm"
 
Double quote?

It's a single quote + FileName + single quote + rest of stuff. Try it out.
It works.


---------------------------------

Thanks Tim.. But i doubt something..
One " is missing in the begining.. How do we insert a Double Quote?

Application.Run "'" & Replace(FileName,"'","''") & "'!ShowForm" ???

Thanks
Joe
 
Double quote?

It's a single quote + FileName + single quote + rest of stuff. Try it out.
It works.

---------------------------------

Thanks Tim..  But i doubt something..
One " is missing in the begining..  How do we insert a Double Quote?

Application.Run "'" & Replace(FileName,"'","''") & "'!ShowForm"   ???

Thanks
Joe
Click to expand...


Thanks everyone..
I find the code of Bob most easy for me... Yes Bob, it definitely
helped :)

Thanks again
Joe
 
Back
Top