Odd Export as PDF problem - Killing me.

[Michael]

I have the routine below in a number of workbooks. Typically it works
without a problem. I have a sheet with sheet names that I select and
it exports the sheets to a PDF. For some reason sometimes it only
produces a PDF with the first sheet in it instead of all sheetnames
that were selected.

I have made sure the sheetname array has more than one value and have
also made sure (by exiting before the export) that it is selecting the
correct sheets in a group. Both of those work fine. I have also made
sure that there was not a problem with the SortableDateString
fuction.

If I manually select the sheets (which is a pain in a book of 70 or
more sheets) export works fine - gives me all the sheets I have
selected.

It is like the file is in some state that is making exporting multiple
sheets a problem. I can open other workbooks with the same exact sub
in it and it will work fine.

Driving me crazy. Must be something too simple to see.

any help would be appreciated!

Sub PDFSet()
Dim c As Variant
Dim sName As String
Dim sh As Worksheet
Dim OrgSheet As String
Dim ExportFileName As String
Dim sheetnames() As String
Dim i As Integer
Dim DefaultFileName As String

i = 0
DefaultFileName = SortableDateString(Now())

OrgSheet = ActiveSheet.Name
ExportFileName = Application.GetSaveAsFilename(ActiveWorkbook.Name &
"." & DefaultFileName & ".pdf")

If ExportFileName = "" Then
'If ExportFileName = False Then ' was causing error so changed to zero
length string as test
End
End If

On Error GoTo er

For Each c In Selection

If Len(c.Value) > 0 Then
ReDim Preserve sheetnames(i)
sheetnames(i) = c.Value
i = i + 1
End If
Next c

Sheets(sheetnames).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\mgray\My Documents\xtemp
\junk1..pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, _
OpenAfterPublish:=True


'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,
Filename:=ExportFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Debug.Print "sheets to print " & UBound(sheetnames)

Sheets(OrgSheet).Select

er:
If err.Number = 9 Then
MsgBox "The Sheet named - " & sName & " - does not exist"
Resume Next
End If

End Sub

 

[GS]

Just curious why your code only exports ActiveSheet rather than...

ActiveWindow.SelectedSheets

...since the sheets are grouped!

 

[Michael]

ExportAsFixedFormat does not work with ActiveWindow.SelectedSheets
unless I am missing something, i tired

ActiveWindow.SelectedSheets.ExportAsFixedFormat Type:=xlTypePDF,
Filename:=ExportFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Is that what you were suggesting?

I have to think that problem is somehow related to the state the file
is in becuase it worked this morning with same file as last night that
was not working

Any other ideas

 

[GS]

Michael wrote on 10/12/2011 :

ExportAsFixedFormat does not work with ActiveWindow.SelectedSheets
unless I am missing something, i tired

ActiveWindow.SelectedSheets.ExportAsFixedFormat Type:=xlTypePDF,
Filename:=ExportFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True,
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Is that what you were suggesting?

I have to think that problem is somehow related to the state the file
is in becuase it worked this morning with same file as last night that
was not working

Any other ideas

I think the approach I was thinking needs some further looking into
because I was using a PDF printer not XL2007 SaveAs PDF. Try the SaveAs
approach and specify the XPS or PDF option OR just execute that
menuitem from VBA against the grouped sheets.

 

[Michael]

Michael wrote on 10/12/2011 :










I think the approach I was thinking needs some further looking into
because I was using a PDF printer not XL2007 SaveAs PDF. Try the SaveAs
approach and specify the XPS or PDF option OR just execute that
menuitem from VBA against the grouped sheets.

--
Garry

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

- Show quoted text -

that is what I am doing save as xps or pdf. Works sometime and then
other times just make one page pdf

 

[GS]

Michael formulated on Wednesday :

that is what I am doing save as xps or pdf. Works sometime and then
other times just make one page pdf

Have you checked this out?

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9943

 

[GS]

I also grouped some sheets and ran this line of code...

CommandBars.ExecuteMso("FileSaveAsPdfOrXps")

to get the dialog open and set any other options I wanted. It published
all selected sheets.