I have a program that runs from Access which opens an Excel workbook,
plugs a value (from Access) into the first cell of a worksheet called
"Tables," and then publishes a PDF containing between 1 and 4
worksheets selected by the user in an Access list box.

The code below worked like a charm at first, but all of a sudden it
has started including in the PDF the "Tables" worksheet in addition to
the worksheets selected in the list box.

I can't seem to find an easy way to deselect the "Tables" worksheet
before selecting the desired worksheets. Can you??

Here's my code:

'Plug in the selected SchoolID in the first cell of the Tables tab
in the master file
objReport.Worksheets("Tables").Range("A1").Value = strCode

'Select the each worksheet selected in the Subjects list box
If lstSubjects.ItemsSelected.Count = 0 Then
MsgBox "PDFs cannot be produced because no worksheets have
been selected by the user."
Else

'In the open Excel workbook, select each worksheet selected by
the user in the lstSubjects listbox.
For Each varItem In lstSubjects.ItemsSelected
Worksheets(lstSubjects.ItemData(varItem)).Select (False)
Next

'Produce a PDF containing those for worksheets, saving it to
the specified folder.
objReport.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
FileName:=strPath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End If

What you're doing is grouping sheets for export to PDF. The way you're
doing it includes Sheets("Tables") if it happens to be the active sheet
when you start 'selecting' other sheets using the 'Replace' arg. This
adds other sheets to the group. Works ok if what you want to do is
include the active sheet, but there's a better way to group sheets
where you can specify which sheets to include OR exclude.


'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
' GroupSheets()
' This procedure requires only the necessary amount of coding be used
' in the Caller. By default, it requires passing only the first arg.
' Use Example: GroupSheets "Sheet1,Sheet3"
' creates a group of only those sheets.
' To group all sheets in a workbook except those sheets:
' GroupSheets "Sheet1,Sheet3", False
' To group all sheets in a workbook pass an empty string:
' GroupSheets "", False
' You can pass the Wkb arg to specify any open workbook.
' (The Wkb doesn't need to be active for this purpose)
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
Public Sub GroupSheets(Sheetnames As String, _
Optional bInGroup As Boolean = True, _
Optional Wkb As Workbook)
' Groups sheets in Wkb based on whether Sheetnames
' are to be included or excluded in the grouping.
' Arg1 is a comma delimited string. (ie: "Sheet1,Sheet3")

Dim Shts() As String, sz As String
Dim i As Integer, wks As Worksheet, bNameIsIn As Boolean

If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
For Each wks In Wkb.Worksheets
bNameIsIn = (InStr(Sheetnames, wks.name) 0)
If bInGroup Then
If bNameIsIn Then sz = wks.name
Else
If bNameIsIn Then sz = "" Else sz = wks.name
End If
If Not sz = "" Then '//build the array
ReDim Preserve Shts(0 To i): Shts(i) = sz: i = i + 1
End If
Next
Wkb.Worksheets(Shts).Select
End Sub 'GroupSheets()

In your case, put the selected sheetnames in a comma delimited string
var and pass it to GroupSheets()...

Dim sWksList As String
For Each varItem In lstSubjects.ItemsSelected
sWksList = sWksList & "," & lstSubjects.ItemData(varItem)
Next
sWksList = Mid$(sWksList, 2) '//remove leading comma

GroupSheets sWksList

'Produce a PDF...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On May 4, 6:35*pm, GS wrote:
What you're doing is grouping sheets for export to PDF. The way you're
doing it includes Sheets("Tables") if it happens to be the active sheet
when you start 'selecting' other sheets using the 'Replace' arg. This
adds other sheets to the group. Works ok if what you want to do is
include the active sheet, but there's a better way to group sheets
where you can specify which sheets to include OR exclude.

'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
' GroupSheets()
' This procedure requires only the necessary amount of coding be used
' in the Caller. By default, it requires passing only the first arg.
' Use Example: *GroupSheets "Sheet1,Sheet3"
' * * * * * * * creates a group of only those sheets.
' To group all sheets in a workbook except those sheets:
' * * * * * * * GroupSheets "Sheet1,Sheet3", False
' To group all sheets in a workbook pass an empty string:
' * * * * * * * GroupSheets "", False
' You can pass the Wkb arg to specify any open workbook.
' * (The Wkb doesn't need to be active for this purpose)
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''
Public Sub GroupSheets(Sheetnames As String, _
* * * * * * * * Optional bInGroup As Boolean = True, _
* * * * * * * * Optional Wkb As Workbook)
' Groups sheets in Wkb based on whether Sheetnames
' are to be included or excluded in the grouping.
' Arg1 is a comma delimited string. (ie: "Sheet1,Sheet3")

* Dim Shts() As String, sz As String
* Dim i As Integer, wks As Worksheet, bNameIsIn As Boolean

* If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
* For Each wks In Wkb.Worksheets
* * bNameIsIn = (InStr(Sheetnames, wks.name) 0)
* * If bInGroup Then
* * * If bNameIsIn Then sz = wks.name
* * Else
* * * If bNameIsIn Then sz = "" Else sz = wks.name
* * End If
* * If Not sz = "" Then '//build the array
* * * ReDim Preserve Shts(0 To i): *Shts(i) = sz: * i = i + 1
* * End If
* Next
* Wkb.Worksheets(Shts).Select
End Sub 'GroupSheets()

In your case, put the selected sheetnames in a comma delimited string
var and pass it to GroupSheets()...

* Dim sWksList As String
* For Each varItem In lstSubjects.ItemsSelected
* * sWksList = sWksList & "," & lstSubjects.ItemData(varItem)
* Next
* sWksList = Mid$(sWksList, 2) '//remove leading comma

* GroupSheets sWksList

* 'Produce a PDF...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks, Garry. That works quite well.

You're welcome! Glad I was able to be of help...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc