CODEWITHSUNDEEP
excelvba
M Muaz
M Muaz

Reputation: 91

Is it possible to run a macro externally on an excel file?

Currently, I export data daily from software to excel files.

There's a lot of repetitive tasks so I created a macro.

  1. I open the new exported excel file and then save it as "Macro-Enabled worksheet"

  2. I open the Macro-enabled worksheet

  3. I import the macros into the excel file

  4. I run the macros

Is there a way to run the macro without doing all the steps above using VBS or any other way?

I don't know if there's a solution out there, but I would prefer if an external VBA operator would ask for the location of the exported file and then does the rest

Upvotes: 0

Views: 1849

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

You can easily open any other workbook and run any commands on that workbook. So you can have the following macro in an Excel file MyMacroFile.xlsm and manipulate data in C:\Temp\WorkbookToRunMacroOn.xlsx for example.

Option Explicit

Public Sub DoTasksOnOtherWorkbook()
    'open another workbook
    Dim OpenWorkbook As Workbook
    Set OpenWorkbook = Application.Workbooks.Open(Filename:="C:\Temp\WorkbookToRunMacroOn.xlsx")
    
    OpenWorkbook.Worksheets("Sheet1").Range("A1").Value = "Changed A1 in another workbook"
    
    'don't forget to close the workbook and save or not
    OpenWorkbook.Close SaveChanges:=True
End Sub

If you want to ask the user to select a file to open you can use the Application.FileDialog property it returns a file name that you can then use in the Application.Workbooks.Open to open it.

Upvotes: 2

Related Questions