Solved

Looping through spreadsheets in a folder and formatting multiple tabs in each spreadsheet

Posted on 2014-04-15
5
207 Views
Last Modified: 2014-04-15
Experts,

I have a folder which contains multiple spreadsheets each with two tabs.  The spreadsheets were created in SAS and exported to Excel.  If I have a macro which formats the spreadsheets, can someone tell me how I can go through the directory in VB and format each spreadsheet?

The name of each spreadsheet begins with "Table_"
 and the two respective tabs to be formatted end with " _new"  and " _all " respectively
0
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40001314
HI,

pls try

Sub macro3()
Set fso = CreateObject("Scripting.FileSystemObject")
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
With fDialog
    .Title = "Select the folder"
    If .Show = True Then
        FolderName = .SelectedItems(1)
    Else
        MsgBox "No Folder selected!", vbOKOnly, "File Export"
        Exit Sub
    End If
End With
Set ObjFolder = fso.GetFolder(FolderName)
On Error GoTo 0
If IsEmpty(ObjFolder) Then
    MsgBox "Folder not valid"
    Exit Sub
End If

Set ObjFiles = ObjFolder.Files
For Each ObjFile In ObjFiles
    If ObjFile.Name Like "Table_*.xls*" Then
        Workbooks.Open (FolderName & "\" & ObjFile.Name)
        For Each sh In ActiveWorkbook.Sheets
            If sh.Name Like "*_new" Or sh.Name Like "*_all" Then
                ' YourCode
            End If
        Next
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    End If
Next
End Sub

Open in new window

Regards
0
 

Author Comment

by:morinia
ID: 40001359
rgonzo1921,

Where do I execute this macro from?
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 40001366
At best from another excel workbook not one you are trying to change
0
 

Author Comment

by:morinia
ID: 40001392
Pardon my ignorance, but I know what to input for Folder Name.  Can you tell me what is

      & ObjFile.Name
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 40001402
the code will present you with a folder selector dialog

you only have to replace the 26 with your code
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question