-2

I have an excel spreadsheet with a very complex macro. The spreadsheet takes a file (imported through a button on the sheet), and runs statistics on it. is there any way to automate the macro to run on multiple files in a folder?

Thanks in advance

braX
  • 11,506
  • 5
  • 20
  • 33
Sapir Dreyer
  • 1
  • 1
    Yes - you can use Dir() to loop over files in a folder. There are plenty of posts here which cover that. If you need help, post your code and describe exactly what issue(s) you're having with it. – Tim Williams Oct 13 '20 at 07:12
  • Hello Welcome to stack overflow !.Please post your source code ,then others can help you to solve your issue.Refer this before posting a question again .https://stackoverflow.com/help/how-to-ask – Hasindu Dahanayake Oct 13 '20 at 10:27

1 Answers1

1

The macro identifies the worksheets from which it draws data and these sheets are in a workbook, which is also identified. Hence, by changing the name of the source(s) in the code, perhaps even dynamically, you can make the same macro perform its magic on other workbooks.

With that said, since VBA absolutely needs a workbooks to identify a worksheet and must have a worksheet in order to identify a cell, VBA will provide a default for either if the code doesn't mention another. Therefore the innocent Cells(1, 1) or Range("A1:B10") you may see in your code in fact stand for ActiveWorkbook.ActiveSheet.Cells(1, 1) or ActiveWorkbook.ActiveSheet.Range("A1:B10"). Therefore, if you want to change the default workbook for another the process is to first introduce a variable to specify the workbook and worksheet and then change the object assigned to that variable.

In a less generic way, let me presume that you don't have syntax like Range("A1:B10") in your code at all but Worksheets("Sheet1").Range("A1:B10"), identifying the worksheet but not the workbook. Let me further presume that the sheet names are the same in all the workbooks on which you want to run the code. In that case you would make the change as shown below.

Dim Wb As Workbook
Set Wb = ActiveWorkbook    'or perhaps ThisWorkbook (=the one having the code)
' and then change all applicable instances to 
Wb.Worksheets("Sheet1").Range("A1:B10")

Now you can change the code to specify another workbook simply with:-

Set Wb = Workbooks("My workbook.xlsm")
Variatus
  • 14,293
  • 2
  • 14
  • 30
  • In addition to that [Loop through files in a folder using VBA?](https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) could be useful and the [Workbooks.Open method](https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open). – Pᴇʜ Oct 13 '20 at 08:02