Solved: Activating sub from another workbook

[chinkha]

Hi everybody,

currently i am trying to call a sub from another workbook
what it does is that it will open another workbook then activate the first workbook, then i will run a sub in the workbook it open previously.

the workbook and sub name is a variable.

in the first workbook the VBA code is as follow

Application.Run "'" & Range("Path_MacroMod_2").Value

but every time i run this code, it will prompt Method 'Range' object _global failed.

range("Path_MacroMod_2") is refer to a cell in one of the sheet in the first book and the value is macro database.xls'!rollover.

any solution to this or there is a better way to run this code.

thanks in advances

[Bob Phillips]

Does the cell Range("Path_MacroMod_2") include the workbook name? Is it enclosed by single quotes?

[chinkha]

Does the cell Range("Path_MacroMod_2") include the workbook name? Is it enclosed by single quotes?

Hi,

Thanks for the reply.

Range("Path_MacroMod_2") refers to to to excel cell, in the cell, there is a formula which merge two input togather.
the result of the formula is marco database.xls'!rollover

and the VBA code i am using is
Application.Run "'" & Range("Path_MacroMod_2").Value

i have been using this code for few months already, and it have successfully run the rollover module, but this month it seem to have a issue with it

i am not sure how to insert or where to insert the single quote you are mentioning. please advise on where i should correct on the code or cell formula.

thanks again.

[Bob Phillips]

You need a leading quote as well.

[chinkha]

You need a leading quote as well.

Hi, how should i change the code?

thanks

[Bob Phillips]

I did tell you, are you not skilled enough to implement that?

Show me the formula that setups that cell.

[chinkha]

I did tell you, are you not skilled enough to implement that?

Show me the formula that setups that cell.

sorry i dont understand whats a leading quote. my programming skills are not really good enough as i am not train in this area.
Apologies.
anyway the formula that setup that cell is

=macroname&"'!"&E23

thanks

[Bob Phillips]

This

="'"&macroname&"'!"&E23

[Aflatoon]

It appears the leading quote is supplied to the Run method later on, but I suspect you need to specify which workbook and sheet the specified range is on:

Application.Run "'" & workbooks("workbook name here").Sheets("sheet name").Range("Path_MacroMod_2").Value

[chinkha]

This

="'"&macroname&"'!"&E23
hi,

just did the method you have indicated. but if my VBA code have the " ' ", would that help?

anyway i implemented the formula and run the code, it still gives me the error.

thanks

[chinkha]

It appears the leading quote is supplied to the Run method later on, but I suspect you need to specify which workbook and sheet the specified range is on:

Application.Run "'" & workbooks("workbook name here").Sheets("sheet name").Range("Path_MacroMod_2").Value

hi,

the code i am running is from another workbook, but the code is found in a module. should i transfer it to a sheet or still remain in module. if it remain in module, whats the code i should input to make this work.

thanks

[GTO]

hi,

the code i am running is from another workbook, but the code is found in a module. should i transfer it to a sheet or still remain in module. if it remain in module, whats the code i should input to make this work.

thanks

Hi there Chinka,

Please attach the database.xls workbook so we can see it. You can remove any sensitive information from the sheets, or in fact, remove all info from the sheets. Please leave the 'rollover()' sub or function exactly where it is currently.

Mark

[chinkha]

Hi there Chinka,

Please attach the database.xls workbook so we can see it. You can remove any sensitive information from the sheets, or in fact, remove all info from the sheets. Please leave the 'rollover()' sub or function exactly where it is currently.

Mark
Hi,

thanks for the reply

found the fault already, apparently the error is found in the rollover sub.

thanks again.