1
  • Excel 2010

Once I opened the file with the raw measurement by myself, I have a macro which performs some actions on the raw data, i.e. sorts it, deletes some stuff, copies some stuff into a new sheet.

At the end it closes the raw data file (while not saving the changes) and also closes, saves and renames the newly created Excel file the macro created after all the sorting, deleting and copying.

So considering I only have this macro file (.xlsm) open...

How can I run that macro on all the Excel files (.xlsx) which are in the same folder of said macro file? I'd prefer not to open them one by one and re-run the macro again.

I am currently closing the open "raw" file via:

ActiveWorkbook.Saved = True
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

Have to admit I don't know if they fully make sense but they work.

Community
  • 1
  • 1
henry
  • 178
  • 3
  • 13
  • [ExecuteMacro4Excel](http://vba4all.wordpress.com/category/vba-macros/various-ways-to-pull-data-from-another-workbook-closedopened/) –  Aug 12 '14 at 07:00
  • 1
    `I'd prefer not to open them one by one and re-run the macro again` That is not possible considering you `performs some actions on "raw" measurement data, sorts it, deletes some stuff, copies some stuff into a new sheet`. You can iterate each file in a folder using *DIR* function and there are a lot of examples here. – L42 Aug 12 '14 at 07:28
  • @L42 Hey, I'm open for anything. Didn't anticipate it not to work. Is [this one ok](http://stackoverflow.com/questions/3375175/how-do-i-open-all-the-excel-files-one-by-one-and-run-a-macro)? Or is another one, e.g. [this](http://stackoverflow.com/questions/14766238/run-same-excel-macro-on-multiple-excel-files), better? – henry Aug 12 '14 at 07:30
  • @L42 but the OP also said `At the end it closes the raw data file (while not saving the changes)` –  Aug 12 '14 at 08:05
  • 1
    possible duplicate of [how do i open ALL the excel files one by one and run a macro](http://stackoverflow.com/questions/3375175/how-do-i-open-all-the-excel-files-one-by-one-and-run-a-macro) – GSerg Aug 12 '14 at 08:14
  • 1
    @mehow yep. But it doesn't mean the only way is not opening it. If OP is doing sorting, deleting and copying, I see no way of better executing it than opening the workbook first. – L42 Aug 12 '14 at 08:20
  • 1
    @L42 you are probably right, at this level it sounds like opening is the right thing to do but in general if there are a few workbooks to work with performance will come in to the play at some point and then the OP is going to realize that ADO is probably best way to go. –  Aug 12 '14 at 08:46
  • @mehow I'm not saying I know better or I blindly believe L42's words but his reply essentially uses the same code as GSerg's reply. ADO, on first glance, seems to be geared to a whole different (read: more experienced) level of users. Wouldn't know where to begin with this. So imho the solution is totally ok. – henry Aug 12 '14 at 08:50

1 Answers1

3

It is simpler than the links you posted.
See below:

Sub stantial()
   Dim myfiles, wb As Workbook, ws As Worksheet
   myfiles = Dir(Thisworkbook.Path & "\*.xlsx")
   Do While Len(myfiles) <> 0
       'Debug.Print myfiles
       '~~> open as readonly since you don't want to alter the original
       Set wb = Workbooks.Open(Thisworkbook.Path & "\" & myfiles, , True)
       '~~> Do your stuff here (edit, copy, sort etc...)
       wb.Close False
       Set wb = Nothing '~~> clean up
       myfiles = Dir
   Loop   
End Sub

Above code retrieves all filenames with .xlsx extention then opens it using Workbooks Open method.
Once you've opened the file, you can work on the open workbook object and do your stuff.
Before you proceed with the next, close the workbook, clean up variables. HTH.

L42
  • 19,427
  • 11
  • 44
  • 68
  • Can I not keep the closing method from my original macro, do I have to use `wb.Close` (or whatever exactly any new macro for this would do)? – henry Aug 12 '14 at 08:14
  • @henry what was your previous method? I mean, why don't you want to close the wb you opened? – L42 Aug 12 '14 at 08:15
  • Edited the op, hope that explains it. – henry Aug 12 '14 at 08:20
  • @henry `wb.Close` is not a new macro, I used it because I set a variable `wb` as the opened workbook so we can work directly in that object. It will function the same as your code but is more explicit. – L42 Aug 12 '14 at 08:29
  • Just did a test with your code and `wb.Close False` commented out. It worked like a charm. So I can say this is solved. – henry Aug 12 '14 at 08:35
  • Hm maybe there was something lost in translation. The "raw" referred to the data being unprocessed. There is no need for performing actions in a (newly created?) worksheet named "raw" or anything. This is a bit confusing to be honest. [The old one was this.](http://pastebin.com/DEesJGwR) – henry Aug 12 '14 at 08:41