Insert Integer Variable into file path while assigning macro

[rwr5014]

Hi all,

In order to assign a macro to an object, I need to specify an Integer variable as part of the file path. How do I do this? I've tried different combinations of quote types/positioning with no luck.

When clicked, I need to run macro "DatabaseScan" from workbook C:\Users\Ryan\Storage\Database(Integer variable).xlsm

How would I go about inputting this?

Selection.OnAction = ????

Thank you for your help.

 

[essentialkiwi]

you could convert the integer to a string

so for example:

FilePath = "C:\Users\Ryan\Storage\Database" & cstr(integer variable) & ".xlsm"

 

[rwr5014]

you could convert the integer to a string

so for example:

FilePath = "C:\Users\Ryan\Storage\Database" & cstr(integer variable) & ".xlsm"

Thanks for the tip, essential. However, your code does not include the macro which is key to answering the question. Do you have any idea on how to fill in the "???" portion of my original post? Thanks!

 

[essentialkiwi]

So... just to get it clear in my head

* you have two (or more) Excel workbooks?
* you want to run a macro called 'DatabaseScan' that is located in workbook1, from workbook2 ?

therefore you want to have the 'DatabaseScan' macro available from multiple Excel workbooks

is this correct?

 

[essentialkiwi]

The object that you are attempting to execute the 'DatabaseScan' macro from... what kind of object is it?


If you did create the object in code it should be as simple as this:

Sub CreateButton()
ActiveSheet.Buttons.Add(200, 46.5, 43.5, 12).Select
Selection.OnAction = "Personal.xlsb!Test"
End Sub


notice that I have my macro 'Test' save in my Personal.xlsb file. This means that the macro can then be accessed from any workbook that is opened in Excel.