 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008Calling an external sub dynamically.
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  SignatureRegards, Nigel nigelnospam@9sw.co.uk > Hello All, > [quoted text clipped - 22 lines] > Thanks > Joe > Application.Run (myFile & "!ShowForm") > [quoted text clipped - 34 lines] > > - 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. SignatureTim Zych SF, CA > Hello All, > [quoted text clipped - 22 lines] > Thanks > Joe > Your pseudo code is on target, but I'd tweak it a bit: > [quoted text clipped - 38 lines] > > - 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" Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) 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: > [quoted text clipped - 44 lines] > > - 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/microsoft.public.excel.programming/browse_thread/ thread/da75e274fddca635/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... > Workbooks.Open (FullFolder) > Workbooks(FileName).Activate [quoted text clipped - 68 lines] > 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 > Double quote? > [quoted text clipped - 10 lines] > Thanks > Joe Thanks everyone.. I find the code of Bob most easy for me... Yes Bob, it definitely helped :) Thanks again 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" > Hello All, > [quoted text clipped - 22 lines] > Thanks > Joe SignatureDave Peterson I'd use a variation of Bob Phillips' code: (Sorry about that, Bob!) > I'd use a variation of Bob Philip's code: > [quoted text clipped - 34 lines] > > Dave Peterson SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.