Loop Macro

[milescm]

I have up to 31 files open. I am running macro on active file then closeing and saving. I want this macro to loop on next active file until the last one is done. Then I want to close excel.


Thanks,
Chad

 

[Mark O'Brien]

So what's your question?

Do you know how to set up a "For...Next" loop?
Are you wanting to open these files in the loop and or are they already open?
If you want them opened, are they all in the same directory?
Is the macro you want to be run situated in each workbook?
If so, are they triggered by the "Open" event?

Just some questions to get you started.

 

[milescm]

Here is my code. I will open all files manually from the same directory and saving back to the same directory.

Sub EECHARTS()
'
' EECHARTS Macro
' AUTO CHART EE OVERALL DIAMETER DATA
'
' Keyboard Shortcut: Ctrl+p
'

' Rename Sheet
ActiveSheet.Name = "Data"
'
' Set Cell D1 Equal to the File Name
Range("$D$1") = ActiveWorkbook.Name
Range("D2").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R[-1]C,"".CSV"","""")"
Range("D2").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlValues
Range("D2").Select
Selection.ClearContents
'
' Select Data Automatically
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "TableRange"
'
' Chart Data Selected Above
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Macro Chart"
ActiveChart.SetSourceData Source:=Sheets("Data").Range("TableRange"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart.TextBoxes.Add(338, 229, 49, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$C$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft -75#
Selection.ShapeRange.IncrementTop -191.52
With ActiveChart.TextBoxes.Add(338, 229, 34, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$D$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft 136#
Selection.ShapeRange.IncrementTop -191.52
'
' Print Chart
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'
' Save File
If ActiveWorkbook.FileFormat = xlCSV Then
ActiveWorkbook.SaveAs FileFormat:=xlNormal
End If
'
' Close Workbook
ActiveWorkbook.Close

'
' Run Macro for all Open Files
Application.OnTime Now + TimeValue("00:00:01"), "EECHARTS"

'
End Sub

 

[Mark O'Brien]

OK, after you open up all of your workbooks, you just need to add three lines of code, I've modified your code and hopefully it'll work, since I've got no way to test.

Sub EECHARTS()
'
' EECHARTS Macro
' AUTO CHART EE OVERALL DIAMETER DATA
'
' Keyboard Shortcut: Ctrl+p
'
Dim oBook as WorkBook

'Start Loop

For Each oBook in Workbooks
' Rename Sheet
ActiveSheet.Name = "Data"
'
' Set Cell D1 Equal to the File Name
Range("$D$1") = oBook.Name
Range("D2").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R[-1]C,"".CSV"","""")"
Range("D2").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlValues
Range("D2").Select
Selection.ClearContents
'
' Select Data Automatically
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "TableRange"
'
' Chart Data Selected Above
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Macro Chart"
ActiveChart.SetSourceData Source:=Sheets("Data").Range("TableRange"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart.TextBoxes.Add(338, 229, 49, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$C$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft -75#
Selection.ShapeRange.IncrementTop -191.52
With ActiveChart.TextBoxes.Add(338, 229, 34, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$D$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft 136#
Selection.ShapeRange.IncrementTop -191.52
'
' Print Chart
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'
' Save File
If oBook.FileFormat = xlCSV Then
oBook.SaveAs FileFormat:=xlNormal
End If
'
' Close Workbook
oBook.Close

'
' Run Macro for all Open Files
Application.OnTime Now + TimeValue("00:00:01"), "EECHARTS"
next 'oBook
'
End Sub

 

[Nimrod]

Here's a little Bat file that will AutoOpen all the xls files in a local directory.
In other words save this code to a batch file. If you put it in the same directory as your 31 xls files it will automatically open them all for you ... instead of you having to do it manually.

@echo off

:: open all xls files found in local dir
FOR /F "eol=#  tokens=1,2*" %%1 IN ('dir /b *.xls') DO call %%1