How can I run a .txt macro on many .xslx files without having to change the .xlsx file?

Question

I'll give an example. So, lets say I have this very simple macro:

Sub updateOneCell(cellNumber As String, cellLetter As String, updatedValue As String)

    Range(cellLetter & cellNumber).value = updatedValue

End Sub

How do I run this macro which is saved as a .txt on multiple .xlsx without having to convert the .xlsx to .xlsm? Also without having to open up the .xlsx files.

https://stackoverflow.com/questions/35132469/vbscript-to-open-an-excel-file-and-then-do-a-save-as — Tarik, Jul 19 '20 at 15:32
To get list of XLS files in folder: https://stackoverflow.com/questions/4200028/vbscript-list-all-pdf-files-in-folder-and-subfolders — Tarik, Jul 19 '20 at 15:36

score 0 · Answer 1 · answered Jul 19 '20 at 11:06

0

Maybe storing the code in a separate .xlsm workbook will appropriate solution? You can run the code directly from the .xlsm workbook and change all the .xlsx files at once.

Changing data in a closed workbook is not possible (as far as I know)... maybe if changing the byte content of the file directly which, I guess, isn't a very nice solution. Instead, you can hide the workbooks that you want to edit with ActiveWindow.Visible = False.

answered Jul 19 '20 at 11:06

ENIAC

813
1
8
19

Changing data in a closed workbook may be possible using a third-party library, but I don't know how much it improves (or decreases) the speed. For suppressing rendering, ``Application.ScreenUpdating = False`` may be better because it stops all screen updates. – kamocyc Jul 19 '20 at 11:16

How can I run a .txt macro on many .xslx files without having to change the .xlsx file?

1 Answers1