Click here to Skip to main content
14,608,023 members
Rate this:
Please Sign up or sign in to vote.
See more:
"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

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 17-Apr-20 0:21am
Rate this:
Please Sign up or sign in to vote.

Solution 1

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.
   
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
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

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
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 5

oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open(Dts.Variables("filePath").Value.ToString())
//Your macro here: I inserted my macro here and return a error: Excepted a statement
oExcel.Run("fileSave")
   
Comments
CHill60 17-Apr-20 7:59am
   
This is not a solution

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100