CODEWITHSUNDEEP
vbaexcel
Zach Hill
Zach Hill

Reputation: 11

Calling macro from another workbook error

I am trying to call a macro from another workbook, and I keep getting the following error.

Cannot run the macro 

'IMECM_To...

The macro may not be available in this workbook or all macros may be disabled.

I have made sure the macro name is spelled correctly, and have made sure that the macro is spelled correctly.
I have looked online and have not been able to figure out how to fix this.
Thanks

Here is the code 

Sub move()

Dim wb As Workbook
Dim MacroFolder As String
Dim MacroWb As String

MacroFolder = "C:\Users\zhill\Desktop\macro learn\"
MacroWb = "IMECM_To_LDW_CSV_Format-20151023-for-2015Q3-for-udf-version-13.0.015.xlsm"

Set wb = Workbooks.Open(MacroFolder & MacroWb)

wb.Sheets("ALFA to Corp CSV").Cells(13, 2) = ThisWorkbook.Sheets("sheet1").Range("IntexFolderList").Cells(1, 1)
wb.Sheets("ALFA to Corp CSV").Cells(14, 2) = ThisWorkbook.Sheets("sheet1").Range("OutputFolderList").Cells(1, 1)
wb.Sheets("ALFA to Corp CSV").Cells(9, 9) = ThisWorkbook.Sheets("sheet1").Range("RunNbr").Cells(1, 1)

wb.Sheets("ALFA to Corp CSV").Application.Run (MacroWb & "!ALFAtoCorpCsvFormat")

End Sub

Upvotes: 1

Views: 2291

Answers (2)

Ryeo
Ryeo

Reputation: 61

code looks fine, probably you need to do some debugging to rule out wrong filename press CTRL G , which will open your Immediate Window

type in the debug.print commands as below , if the line "Exists?" is follow by an empty space it means there's a problem with your file name or directory.

hit F8 to run the code line by line instead of F5

Sub move()

Dim wb As Workbook
Dim MacroFolder As String
Dim MacroWb As String

MacroFolder = "C:\Users\zhill\Desktop\macro learn\"
MacroWb = "IMECM_To_LDW_CSV_Format-20151023-for-2015Q3-for-udf-version-13.0.015.xlsm"

Debug.Print MacroFolder & MacroWb
Debug.Print "Exists?", Dir(MacroFolder & MacroWb)

Set wb = Workbooks.Open(MacroFolder & MacroWb)

wb.Sheets("ALFA to Corp CSV").Cells(13, 2) = ThisWorkbook.Sheets("sheet1").Range("IntexFolderList").Cells(1, 1)
wb.Sheets("ALFA to Corp CSV").Cells(14, 2) = ThisWorkbook.Sheets("sheet1").Range("OutputFolderList").Cells(1, 1)
wb.Sheets("ALFA to Corp CSV").Cells(9, 9) = ThisWorkbook.Sheets("sheet1").Range("RunNbr").Cells(1, 1)

wb.Sheets("ALFA to Corp CSV").Application.Run (MacroWb & "!ALFAtoCorpCsvFormat")

End Sub

if you still cant seem to arrow into the problem run this code to see what are the files that excel has picked up in your directory. 

Sub PrintDirectoryFiles()

Dim myfile As String
Dim MacroFolder As String

MacroFolder = "C:\Users\zhill\Desktop\macro learn\"
myfile = Dir(MacroFolder & "*.xlsm")  'or *.*

Do While myfile <> ""
    Debug.Print myfile
    myfile = Dir
Loop



End Sub

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21639

If the workbook name includes spaces or some other particular characters it is necessary to enclose the name with single quotes, like this :

Application.Run "'Book 1.xls'!MyMacroName"

Source: Ron deBruin : How do I use Application.Run in Excel?

To ensure that macros are not disabled in Excel, you could go:
File>Options>Trust Center>Trust Center Settings>Macro Settings>Enable All Macros

Upvotes: 1

Related Questions