Select Variable Number of Worksheets to Include in a PDF

[groverparkgeorge]

I am automating Excel from Access. We open an Excel Template, populate one or more worksheets with data from the database and then save the Excel Template as a PDF.

I can make it all work smoothly as long as I hard-code the worksheets to be included in the PDF:

.Worksheets(Array("Bat-1", "Bat-2", "Bat-3", "Bat-4", "Bat-5")).Select
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
ReportFiles & "\" & StrEquipmentTested & ".pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

However, there may be anywhere from 1 to 9 bats in any test run, so I need to select a variable number of worksheets, one for each Bat (we're talking sports equipment, not mammals).

I can create a variable that seems to be the equivalent of the string in the Array() for selecting sheets to include, but it fails with a subscript out of range error.
Is my only alternative a Case Select that counts worksheets and uses a hard-coded set of statements?

Thanks in advance.
George

 

[groverparkgeorge]

I found the answer, and it is sort of a 'doh' for those of you who use Excel a lot more than I do.

For Each xlSheet In .ActiveWorkbook.Sheets
If xlSheet.Name Like "*Bat-*" Or _
.Worksheets(iTests).Name Like "*Bat(*" Or _
.Worksheets(iTests).Name Like "*Bat (*" Then
xlSheet.Select False
End If
Next xlSheet

Because we're working with some "legacy" data, the actual worksheet names are not quite so consistent as we'd like, hence the need to include both - and ()'s as delimiters.

Thanks.