CODEWITHSUNDEEP
excelvbams-project
Hemil Dhruv
Hemil Dhruv

Reputation: 3

Open an existing Excel file and run a macro in that file through MS Project

I want to open an Excel file named Open_Yard_Scheduling_tool_V8.xlsm, run a macro in the same Excel file named formulcopy and then copy the data from the Excel file and paste into MS Project file by writing a code in VBA in MS Project application.

I am able to open the Excel file and copy its data and paste it into the required project file but I'm not able to run the macro file in Excel through VBA in MS Project.

I am using the code mentioned below.

Sub ex_mac()   
    Dim xlApp As Object
    Dim xlWkb As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlWkb = xlApp.Workbooks.Open("C:\Users\DHRUV.HEMIL.MANOJ\Documents\Open_Yard_Scheduling_tool_V8.xlsm")

    xlApp.Visible = True

    Application.Run ("'C:\Users\DHRUV.HEMIL.MANOJ\Documents\Open_Yard_Scheduling_tool_V8.xlsm'!formulcopy") 
End Sub

It is only opening the required Excel file but it is not able to run the macro in the same Excel file.

Upvotes: 0

Views: 1271

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

The issue here is that Application.Run refers to the MS Project application but you try to run code in the Excel application. Therefore you need to use xlApp.Run instead (which refers to the Excel applictation that you created with Set xlApp = CreateObject("Excel.Application"):

xlApp.Run("'Open_Yard_Scheduling_tool_V8.xlsm'!formulcopy")

Note that the full path is not needed here.

Upvotes: 2

Related Questions