Run Excel Macro On XLSX File

1
votes

Our business system saves a report in an XLSX file format, which is standard Excel. I have a macro that I have written that processes this report to give me usable data. I had to write it in an XLSM file, which is a macro enabled excel spreadsheet. Is there a way I can run the macro on the original file without having to copy and paste the code in? I have seen some VBS scripts that run macros that already embedded into the spreadsheet, but this is slightly different.

4
vbaexcel
You could access the report sheet from another sheet or WorkBook (where the macro lives) or you could write sth in VB.Net using the Excel Interop, which reads your report and runs the macro on it. I will write you some example – Rafa Gomez

4 Answers

1
votes

Instead of using an XLSM file, use an XLAM file (Excel Add-In format) and then add it to Excel as an Add-In. Then the code can be run on any spreadsheet loaded into memory at the time.

2
votes

Just set a reference to the XLSX file:

Sub Test()

    Dim OtherWorkbook As Workbook

    'Otherworkbook should be closed at start of code.
    'We open it here.
    Set OtherWorkbook = Workbooks.Open("full path to other workbook")

    With OtherWorkbook
        .Worksheets("Sheet1").Range("A1") = "I've just updated the other workbook."
        .Save
        .Close
    End With

End Sub
1
votes

You could just do like this

Dim reportWb = Workbooks.Open('workBookPath')

Dim reportSheet = reportWb.Sheets(SheetNr or SheetName)

Then you could do your macro

1
votes

I'm late to this but you can also store macros and VBA scripts in a personal macro workbook (PERSONAL.XLSB) which will open up as a hidden workbook every time you open excel. This way you can save a macro/script on a separate personal file but run it in a XLSX file. Visit HERE for more info.