Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

run macro on multiple spreadsheets at once

Status
Not open for further replies.
Shift838

Shift838

IS-IT--Management
Jan 27, 2003
987
US
is it possible to run a macro within Excel on all spreadsheets that I open at one time. I have repots created in an excel format that needs to have this macro ran to format them but it is a very tedious tasks to do this one by one. Is there a way to open them all up within excel and when I import the macro to tell it to run on all of them spreadsheets?
 
Sort by date Sort by votes
No, you can't run it on multiple workbooks simultaneously. But you can loop through all files in a folder. Or only all files that match a certain naming convention.

That way you don't even have to worry about opening the workbooks yourself.

Here's something to get you started:
Code: 
    strPath = "C:\Your\Path\"
    strFileName = Dir(strPath & "FileName*.xls")

    Do While strFileName <> ""
        Workbooks.Open Filename:= strPath & strFileName
        '...
        'YourCodeHere
        '...
    Loop

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
Upvote 0 Downvote
Are you saying that I can put this code into the current macro and when ran it will open the workbook out of the given directory and run the macro then go to the next file?
 
Upvote 0 Downvote
Yep, that's the idea.

You might want to include something to close each file after it's been processed - especially if you have a lot of files in the folder.
Code: 
Workbooks(strFileName).Close savechanges:=True

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
Upvote 0 Downvote
I have placed your code into the macro as follows:

Code: 
strPath = "C:\Your\Path\"
    strFileName = Dir(strPath & "FileName*.csv")

    Do While strFileName <> ""
        Workbooks.Open Filename:= strPath & strFileName
        '...
        'lots of code here to change formatting such has words, etc in a CSV file
        '...
Workbooks(strFileName).Close savechanges:=True
    Loop

However, when I click the Run macro, it does nothing. It does not open any files in the directory or make any modifications and save it.
 
Upvote 0 Downvote
One other thing. I did change the FileName* to what my files begin with.

I have debugged a little bit. I see if I do a msgbox strfilename I get a blank message, no variables in init.
 
Upvote 0 Downvote
Make sure you set both strPath and strFileName to suit your own circumstances.

And I would strongly advise stepping through it using [F8] for the first couple of workbooks so you can see what it's doing.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
Upvote 0 Downvote
I did both and it does nothing as I as I said.

STRPATH = "C:\TEMP\ID\"
STRFILENAME = Dir(STRPATH & "AH-ID*.csv")

When I do a msgbox strpath it shows the C:\temp\id\ just fine, but a msgbox of strfilename shows nothing.
 
Upvote 0 Downvote
I got it to work partially. It only does the first file in the directory and keeps looping to the same file. Does not go to the other files. Here is the code:

Code: 
Sub ID_Owner_Format()
'
' ID_Owner_Format Macro
'
strPath = "C:\temp\id\"
    strFileName = Dir(strPath & "AH_ID*.csv")

    Do While strFileName <> ""
        Workbooks.Open Filename:=strPath & strFileName
'
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("1:1").Select
    Selection.Font.Bold = True
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "UPDATE/DELETE/NO CHANGE"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Account Name"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Application Name"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Version of Application"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Description of Account/ID"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Reason ID Exist?"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Server Names Account Has Access To"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Type of ID " & Chr(10) & "SID = Service" & Chr(10) & "UID = User"
    With ActiveCell.Characters(Start:=1, Length:=36).Font
        .Name = "Calibri"
        .FontStyle = "Bold"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Deny Dial-In for ID? (Y/N)"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Can Password Change? (Y/N)"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Does Password Expire?(Y/N)"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Can Account Expire(Y/N)"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = ""
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Can Account Expire?(Y/N)"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = _
        "Can Password Be Changed With No Issue to Application?(Y/N)"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Does ID Require Email?(Y/N)"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Does ID Require Internet Access?(Y/N)"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Account Used In Test Environment?(Y/N)"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Account Used in Development Environment?(Y/N)"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Account Used In Staging Environment?(Y/N)"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Account Used In Production Environment?(Y/N)"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "Account Used In America Region?(Y/N)"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "Account Used In Asia Region?(Y/N)"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "Account Used In Europe Region?(Y/N)"
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "Account Used In Other Region?(Y/N)"
    Range("X1").Select
    ActiveCell.FormulaR1C1 = _
        "Account Owner Name (Must Match Exactly as Global Address Book)"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "Account Inactive(Y/N)"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "Review Month"
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "Who Is The Responsible Group?"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "SOW #"
    Columns("AA:AA").Select
    Columns("AB:AB").EntireColumn.AutoFit
    Columns("AB:AB").ColumnWidth = 11.57
    Columns("AA:AA").EntireColumn.AutoFit
    Columns("Z:Z").EntireColumn.AutoFit
    Columns("Y:Y").EntireColumn.AutoFit
    Columns("X:X").EntireColumn.AutoFit
    Columns("W:W").ColumnWidth = 8.29
    Columns("W:W").EntireColumn.AutoFit
    Columns("V:V").EntireColumn.AutoFit
    Columns("U:U").EntireColumn.AutoFit
    Columns("T:T").EntireColumn.AutoFit
    Columns("S:S").EntireColumn.AutoFit
    Columns("R:R").EntireColumn.AutoFit
    Columns("Q:Q").EntireColumn.AutoFit
    Columns("P:P").EntireColumn.AutoFit
    Columns("O:O").EntireColumn.AutoFit
    Columns("N:N").EntireColumn.AutoFit
    Columns("M:M").EntireColumn.AutoFit
    Columns("L:L").EntireColumn.AutoFit
    Columns("K:K").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    ActiveWindow.SmallScroll ToRight:=-9
    Columns("I:I").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("H:H").ColumnWidth = 14.43
    Columns("G:G").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("E:E").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("A:A").EntireColumn.AutoFit
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "UPDATE"
    Range("A2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "EXAMPLE"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Oracle"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "10"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Account used for Oracle service"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Used to run all oracle services"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "SERVERNAME1; SERVERNAME2"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "SID"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "Yes"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("S2").Select
    ActiveCell.FormulaR1C1 = "Yes"
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "Yes"
    Range("U2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("V2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("W2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "Hancock, John"
    Range("Y2").Select
    ActiveCell.FormulaR1C1 = "No"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "Current Month"
    Range("AA2").Select
    ActiveCell.FormulaR1C1 = "Application Hosting"
    Range("AB2").Select
    ActiveCell.FormulaR1C1 = "69254"
    Columns("E:G").Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.SmallScroll ToRight:=4
    Columns("I:W").Select
    Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("Y:Y").Select
    Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    
    Columns("A:A").EntireColumn.AutoFit
    Range("A1").Select
    
    'ActiveWorkbook.Save
    Workbooks(strFileName).Close savechanges:=True
    Loop
    
End Sub
 
Upvote 0 Downvote
Try going on to the next found parameter...you have to reset the variable with Dir.
Code: 
strPath = "C:\Your\Path\"
    strFileName = Dir(strPath & "FileName*.csv")

    Do While strFileName <> ""
        Workbooks.Open Filename:= strPath & strFileName
        '...
        'lots of code here 
        '...
        Workbooks(strFileName).Close savechanges:=True
        [b]strFileName = Dir()[/b]
    Loop
Are you sure the files are in the path folder?

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry
 
Upvote 0 Downvote
For the record you didn't say... you only mentioned the file name, not folder. But let's not bicker.

What you're describing means that there are no files within that folder that match the search critera.

Let's try this: turn on your macro recorder (in 2003 & before: Tools > Macro > Record macro; in 2007: Developer Ribbon > Code > Record Macro).

From within Excel, Open a file from the target folder. If a wizard appears to walk you through column choices, complete it as you normally would. Turn off the macro recorder.

Copy and paste the generated code into a new post. (If you surround it with the [ignore]
Code:
[/ignore] tags it will be easier to read once posted.)

That will ensure that we have the folder and file name exactly. Then we can generalize the file name with a wildcard (*) and help clean up the code.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
Upvote 0 Downvote
the strfilename = dir() worked.

Great.. Thank you!

You have saved me about 8 hours every month!
 
Upvote 0 Downvote
D'oh! I can't believe I left that out. Thanks, Gerry.

My previous post was composed before seeing the two previous posts.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.
 
Upvote 0 Downvote
He said smugly: "Press F1 over Dir. Help shows this requirement."

Just razzin' ya John. Forgetting a wee piece of instruction - vital though it may be - is easy enough to do.



As if I have not done the same thing.....

[rofl]

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Hyperlinks in Word cut at #
Replies
2
Views
229
Andrzejek
Andrzejek
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
687
strongm
strongm
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
remeng
  • Locked
  • Question
multiple err handling
Replies
6
Views
585
remeng
remeng
remeng
  • Locked
  • Question
If obsolete - deactivate all but one macro
Replies
6
Views
405
mintjulep
M

Part and Inventory Search

Sponsor

Back
Top