Calling a Procedure in Another Workbook

[PipBoy808]

Hello,

I'm trying to call a procedure from another workbook without hard-coding the name of the workbook into my VBA. Rather, as I will be repeating this process, the secondary workbook names are contained within a worksheet and assigned to variables 'MyWbName' and 'TBWB'.

Dim MyWBName as String
Dim TBWB as Workbook

MyWBName="workbookname.xlsm"

Workbooks(MyWBName).activate

Set TBWB = ActiveWorkbook

Call TBWB.modimport.import

There's a module in each secondary workbook called 'modimport' with a procedure called 'import', but does anyone know how I can call it up?

Thanks in advance!

 

[Luke M]

Use exclamation mark to separate workbook from module, period to separate module from macro, like so

Dim MyWBName As String
MyWBName = "workbookname.xlsm"
Application.Run MyWBName & "!modimport.Import"

 

[PipBoy808]

No dice I'm afraid. The error is declaring that 'workbook.module.procedure' is unavailable or disabled. Neither are true. At least the code is looking for the right procedure name though, so that's a start.

 

[Luke M]

This:
workbook.module.procedure
is not what I typed. Need to have a "!" not a period. Fully concatenated, will look for:
workbook!module.procedure

 

[PipBoy808]

Sorry that was a typo. I meant workbook!module.procedure doesn't work.

 

[Luke M]

Are both workbooks already open? Have you double checks all the names? If there's a space in the workbook name, you'll need to include some single quotation marks similar to when writing a formula.

 

[PipBoy808]

Yes to all of those. A couple of us in the office were poring over it wondering why it wasn't working. If we Debug.Print the line it's the exact workbook and macro that we're looking for. Odd.

 

[Luke M]

Ok, taking it back to basics. Open both of the attached workbooks, see if you can get the button in Recipient to work correctly.