Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Loop Macro

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Chad Miles
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Chad Miles
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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




  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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




  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Code:
    @echo off
    
    :: open all xls files found in local dir
    FOR /F "eol=#  tokens=1,2*" %%1 IN ('dir /b *.xls') DO call %%1
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •