CODEWITHSUNDEEP
vbaexcel
David
David

Reputation: 79

Run Excel Macro On XLSX File

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.

Upvotes: 2

Views: 20582

Answers (4)

jc23
jc23

Reputation: 11

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.

Upvotes: 1

braX
braX

Reputation: 11755

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.

Upvotes: 1

Rafa Gomez
Rafa Gomez

Reputation: 710

You could just do like this

Dim reportWb = Workbooks.Open('workBookPath')

Dim reportSheet = reportWb.Sheets(SheetNr or SheetName)

Then you could do your macro

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

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

Upvotes: 2

Related Questions