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


--

Regards,
Nigel
(E-Mail Removed)



"Joe" <(E-Mail Removed)> wrote in message
news:2c15ded7-0d7e-4fc9-82d2-(E-Mail Removed)...
> 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

On Jan 30, 1:37*pm, "Nigel" <nigel-...@nosupanetspam.com> wrote:
> *Application.Run (myFile & "!ShowForm")
>
> where myFile is the full name for the external book
>
> --
>
> Regards,
> Nigel
> nigelnos...@9sw.co.uk
>
> "Joe" <Joe.Varghese.J...@gmail.com> wrote in message
>
> news:2c15ded7-0d7e-4fc9-82d2-(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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.


--
Tim Zych
SF, CA

"Joe" <(E-Mail Removed)> wrote in message
news:2c15ded7-0d7e-4fc9-82d2-(E-Mail Removed)...
> 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

On Jan 30, 1:55*pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET> wrote:
> 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
>
> "Joe" <Joe.Varghese.J...@gmail.com> wrote in message
>
> news:2c15ded7-0d7e-4fc9-82d2-(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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)



"Joe" <(E-Mail Removed)> wrote in message
news:b4bc5b18-abc4-4edb-8237-(E-Mail Removed)...
On Jan 30, 1:55 pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET> wrote:
> 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
>
> "Joe" <Joe.Varghese.J...@gmail.com> wrote in message
>
> news:2c15ded7-0d7e-4fc9-82d2-(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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/micro...da5722fdb3af0a

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...


On Jan 30, 10:30 am, "Bob Phillips" <bob....@somewhere.com> wrote:
> 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)
>
> "Joe" <Joe.Varghese.J...@gmail.com> wrote in message
>
> news:b4bc5b18-abc4-4edb-8237-(E-Mail Removed)...
> On Jan 30, 1:55 pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET> wrote:
>
>
>
> > 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
>
> > "Joe" <Joe.Varghese.J...@gmail.com> wrote in message
>
> >news:2c15ded7-0d7e-4fc9-82d2-(E-Mail Removed)...
>
> > > 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- Hide quoted text -
>
> > - Show quoted text -
>
> 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'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"


Joe wrote:
>
> 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

--

Dave Peterson

I'd use a variation of Bob Phillips' code:

(Sorry about that, Bob!)

Dave Peterson wrote:
>
> 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"
>
> Joe wrote:
> >
> > 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
>
> --
>
> Dave Peterson

--

Dave Peterson

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