Multiple Open Files

[saltkev]

Good Afternoon

I wonder can someone help. I have multiple Excel Files in one Directory.

1. I need to Open Each one in turn.
2. Activate a given Sheet. The Sheet Name will vary from Workbook to workbook (E.g Station 1, Station 2 , Station 3)
3. Print the Selected Sheet to my default printer.
4. Close the Work book.

Many Thanks

Kev

 

[tonyyy]

Kev,

You might consider the following...

Sub LoopFilesSheets()
Application.ScreenUpdating = False
Dim wb As Workbook, wb1 As Workbook
Dim FolderName As String, fileName As String
Dim i As Long

''''Select folder that contains files
With Application.FileDialog(msoFileDialogFolderPicker)
  .AllowMultiSelect = False
  If .Show = 0 Then Exit Sub
  FolderName = .SelectedItems(1) & "\"
End With

Set wb = ThisWorkbook
fileName = Dir(FolderName & "*.xls?")

''''Loop through files
Do While fileName <> ""
    If fileName <> wb.Name Then
        Set wb1 = Workbooks.Open(FolderName & fileName)
        ''''Loop through sheets
        For i = 1 To wb1.Worksheets.Count
            If InStr(wb1.Sheets(i).Name, "Station") > 0 Then wb1.Sheets(i).PrintOut
        Next i
        wb1.Close savechanges:=False
    End If
    fileName = Dir
Loop
Application.ScreenUpdating = True
MsgBox "The dishes are done, dude!"
End Sub

Cheers,

tonyyy

 

[saltkev]

Top Job Tony, Worked a Treat, Many Thanks

 

[saltkev]

Hi Tony

I am trying now for the next evolution of your code! What I would like to do is.

Rather than open all the Excel files & print all the Station Sheets in a given Folder. I would like to be able select which Excel files I open and print the station sheet.

thanking you in anticipation

Kev

 

[tonyyy]

Top Job Tony, Worked a Treat, Many Thanks

You're very welcome.

Rather than open all the Excel files & print all the Station Sheets in a given Folder. I would like to be able select which Excel files I open and print the station sheet.

Sub LoopFilesSheets2()
Application.ScreenUpdating = False
Dim fd As FileDialog
Dim wb As Workbook
Dim i As Long, j As Long

''''Open FileDialog and select file(s)
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .InitialView = msoFileDialogViewList
    .AllowMultiSelect = True
    If .Show = 0 Then Exit Sub
End With

''''Loop through selected file(s)
For i = 1 To fd.SelectedItems.Count
    Set wb = Workbooks.Open(fd.SelectedItems(i))
    ''''Loop through sheets
    For j = 1 To wb.Worksheets.Count
        If InStr(wb.Sheets(j).Name, "Station") > 0 Then wb.Sheets(j).PrintOut
    Next j
    wb.Close savechanges:=False
Next i
Application.ScreenUpdating = True
MsgBox "The dishes are done, dude!"
End Sub

 

[saltkev]

Cracking, Many Thanks

Kev

 

[tonyyy]

You're very welcome...