Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
"Cannot run the macro ...The macro may not be available in the workbook or all marcros may be disabled"<pre>

oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open(Dts.Variables("filePath").Value.ToString())
//Your macro here:
oExcel.Run("Yourmacro")


I have used the above logic in SSIS package, every thing went fine but macros was not running and the result was the above error
My macro name is "Module1"


the Macro is as follows

VB
Sub fileSave()
'
Dim newFileName As String, originalFileName As String, fileSaveName As String, fileNamePathSaved As String, fileNameSaved As String
Dim response As VbMsgBoxResult, currentRoute As String
'
ThisWorkbook.RefreshAll
ActiveWorkbook.Save ' save the current workbook before messing with it
Application.DisplayAlerts = False ' turns off alerts and messages
' Save file name and path into a variable
originalFileName = ActiveWorkbook.FullName ' gets the fullname with path
' originalFilePath = ActiveWorkbook.Path ' grabs the current path

Dim usingReplace As String
usingReplace = Replace(originalFileName, ".xlsm", ".xlsx")
ActiveWorkbook.SaveAs Filename:=usingReplace, FileFormat:=xlOpenXMLWorkbook
fileNameSaved = ActiveWorkbook.Name ' grabs the name of the saved file

Workbooks.Open Filename:=originalFileName 'reopens the original workbook file
Application.DisplayAlerts = True ' turns the alerts and messages back on


'provide an opportinity to clear the incident report flag
' If incidentFiled = True Then response = MsgBox("Do you want to clear the Incident Report?", vbInformation + vbOKCancel, "Incident Report Form")
If response = vbOK Then incidentFiled = False
'close the newly made file

' Workbooks(fileNameSaved).Close True ' sub terminates at this point
'
End Sub


thanks in advance

What I have tried:

oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open(Dts.Variables("filePath").Value.ToString())
//Your macro here:
oExcel.Run("Module1")
Posted
Updated 20-Jan-21 9:40am

Read the error message, it couldn;t be much clearer if it tried:
Quote:
Cannot run the macro ...The macro may not be available in the workbook or all marcros may be disabled

So there are two possibilities:
1) "The macro may not be available in the workbook" - check the macro code exists in the same workbook as the code you are trying to execute. If it isn't ... it can't be run!
2) "all marcros may be disabled" - this is pretty likely: file macros are often disabled across the whole of Office for security reasons (IIRC that's even the default these days). If macros are disabled, no macro can be run.
 
Share this answer
 
Comments
Smart003 15-May-19 3:57am    
Apologies for the late reply, Macros available in workbook and I tried to run the macros in the Excel file(.xlsm)(which is given as input) it works as expected
oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open(Dts.Variables("filePath").Value.ToString())
//Your macro here:
oExcel.Run("fileSave")


Now after the change, the logic is working as expected
 
Share this answer
 
when saving your file, choose SAVE AS, and click on the drop down arrow from SAVE AS TYPE option, look for EXCEL MICRO-ENABLED WORKBOOK
 
Share this answer
 
Comments
CHill60 20-Mar-20 7:34am    
You cannot save a workbook that contains macros unless you change it from an .xlsx (the default) to one of .xlsm, .xlsb or .xls. Even if you do do that Macros can still be disabled or you can try to run a Macro that is not available in the open workbook. So this "solution" is just inaccurate.
Make sure a macro is assigned. In my case I created a button, Recorded a macro to use with the button, however I never actually assigned the macro to the button. Once I right clicked on the button, Selected assign Macro, selected the recorded macro, the button worked. Of course I had also saved the workbook as a MacroEnabled Workbook.
 
Share this answer
 
Comments
Richard MacCutchan 20-Jan-21 15:45pm    
See Solution 2; this was resolved two years ago.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900