Excel Macro help?

[nittanylions]

I'm sure this may be pretty simple for most of you, but I'm still working on honing my VBA skills. Here's my situation.

I have 50+ files, and I need to print one sheet from each file. But that sheet isn't always named the same thing. For instance, file #1 has sheets named "A", "B", and "C". File #2 only has sheets named "B" and "C". File #3 only has a sheet named "C".

I would like to run a macro that loops through all open files, printing one sheet from each file. If "A" is available, print "A". Else, print "B". Else, print "C", down the line, for each individual file.

Any help?

TIA.

[Abused Student]

Without going all the way through it, I would set it up to open the sheet, then read in


for i = 0 to workbook.sheets.count-1 (might need to start at 1, forget)
if orderingfunction(workbook.sheets(i).name)<currentm ax then
currentmax = i
end if
next i

[It's a trap!]

dim objfs as filesystemobject
dim objfolder as filder
din objfile as file
dim counter as long


set objfs = new filesystemobject
set objfolder = objfs.getfolder(Folder Name Here)

For each objfile in objfolder.files
workbooks.open Folder Name & objfile.name
For counter = 1 to sheets.count
sheets(counter).select
...
application.displayalerts = false
activewindow.close
application.displayalerts = true
next counter
next objfile

[spencerhs5]

I think you need to give us more information. I highly doubt that your sheets actual names are A,B, C etc. otherwise you would just alphabetize and print the minimum. If you come up with pseudo logic on how to determine what sheet to print then we could help you better. One method I can think of would be an array where you specify sheet names and the corresponding order to check.

[spencerhs5]

Originally Posted by It's a trap!

dim objfs as filesystemobject
dim objfolder as filder
din objfile as file
dim counter as long


set objfs = new filesystemobject
set objfolder = objfs.getfolder(Folder Name Here)

For each objfile in objfolder.files
workbooks.open Folder Name & objfile.name
For counter = 1 to sheets.count
sheets(counter).select
...
application.displayalerts = false
activewindow.close
application.displayalerts = true
next counter
next objfile

Just an FYI this method needs to enable the FSO Object, Tools>References

[strategygamer]

Originally Posted by It's a trap!

dim objfs as filesystemobject
dim objfolder as filder
din objfile as file
dim counter as long


set objfs = new filesystemobject
set objfolder = objfs.getfolder(Folder Name Here)

For each objfile in objfolder.files
workbooks.open Folder Name & objfile.name
For counter = 1 to sheets.count
sheets(counter).select
...
application.displayalerts = false
activewindow.close
application.displayalerts = true
next counter
next objfile

That looks like it would print all the files, no? The OP only wanted 1 sheet per file.

IAT's code will work good to open the files within a directory, then you can use some if logic to determine which sheet to print.

I found this code online, I found it fairly helpful for some other macros that I run, it should work for you.

Public Function IsSheetExists(sname) As Boolean
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then IsSheetExists = True Else IsSheetExists = False
End Function

Function x(sheet1, sheet2, sheet3) As Boolean
If IsSheetExists(sheet1) Then
  x = sheet1
ElseIf IsSheetExists(sheet2) Then
  x = sheet2
Else
  x = sheet3
End If
End Function

[nittanylions]

My sheets are actually named NET, NET1, NET2. And I don't mind actually opening the files to print them, since there are more files in the directory that don't fit quite the same with the others. My usual method of printing multiple files is to use the code below. However, all the other files I've ever used this for have the same sheet names throughout. This time, I want to only print one sheet per file, in the order NET, NET1, NET2.

--------------------------------
Function PrintTheseSheets()
Sheets(Array("NET", "NET1", "NET2")).Select
ActiveWindow.SelectedSheets.PrintOut
End Function

Sub PrintTheseSheetsLoop()
Dim intloop1 As Integer
For intloop1 = 1 To Excel.Workbooks.Count
Excel.Workbooks(intloop1).Activate
If UCase(Excel.Workbooks(intloop1).Name) <> "PERSONAL.XLSB" Then
Call PrintTheseSheets
End If
Next intloop1
End Sub
------------------------------------------

Essentially, anytime I want to run a macro on multiple open files, I use the same standard looping precedure (the second Sub) and replace which macro to call. Quick and dirty, but usually serves my purposes.

FYI, I've never had a course on programming and have only learned how to do anything in VBA by using the macro recorder and editing existing macros written by others, so please be gentle.

[strategygamer]

Public Function IsSheetExists(sname) As Boolean
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then IsSheetExists = True Else IsSheetExists = False
End Function

Function x(sheet1, sheet2, sheet3) As String
If IsSheetExists(sheet1) Then
  x = sheet1
ElseIf IsSheetExists(sheet2) Then
  x = sheet2
Else
  x = sheet3
End If
End Function

I changed the personal.xls line to account for the possibility of different file extensions between 2003 and 2007.

Sub PrintTheseSheetsLoop()
Dim intloop1 As Integer
For intloop1 = 1 To Excel.Workbooks.Count
  If Left(UCase(Excel.Workbooks(intloop1).Name), 12) <> "PERSONAL.XLS" Then
    Excel.Workbooks(intloop1).Activate
    sheet2print = x("NET","NET1","NET2")
    sheets(sheet2print).printout
  End If
Next intloop1
End Sub

[SpaceActuary]

Using a "For Each" loop may make this a little prettier:

Option Explicit

Sub PrintNETSheets()
    Dim wkb As Workbook
    Dim wks As Worksheet
    
    For Each wkb In Workbooks
        If Left(UCase(wkb.Name), 12) <> "PERSONAL.XLS" Then
            
            On Error Resume Next        ' Ignore errors
            Set wks = wkb.Sheets("NET")
            If wks Is Nothing Then Set wks = wkb.Sheets("NET1")
            If wks Is Nothing Then Set wks = wkb.Sheets("NET2")
            On Error GoTo 0             ' Stop ignoring errors
            
            If wks Is Nothing Then
                'No sheets were found!
            Else
                wks.PrintOut
            End If
        
            Set wks = Nothing
            
            'wkb.close                 ' Optional
        End If
    Next wkb
End Sub

"For Each...Next" loops work similar to "For...Next" but instead of iterating over a range of numbers, they iterate over all items in a collection (such as the Workbooks collection). I believe "For Each..." loops are a hair faster than the typical "For...Next" loops--but nothing you will probably notice. The biggest advantage is it's a little easier to read. Inside the loop, you only have to refer to the current object (in this case wkb).

For more info: http://msdn.microsoft.com/en-us/libr...d0(VS.85).aspx