Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd Export as PDF problem - Killing me.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd Export as PDF problem - Killing me.
Just curious why your code only exports ActiveSheet rather than...
ActiveWindow.SelectedSheets ...since the sheets are grouped! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd Export as PDF problem - Killing me.
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd Export as PDF problem - Killing me.
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd Export as PDF problem - Killing me.
On Oct 12, 12:23*pm, GS wrote:
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. -- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd Export as PDF problem - Killing me.
Michael formulated on Wednesday :
On Oct 12, 12:23*pm, GS wrote: 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. -- 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 Have you checked this out? http://www.microsoft.com/download/en...ang=en&id=9943 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Odd Export as PDF problem - Killing me.
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This is killing me | Excel Programming | |||
Simple problem for you all....killing me though | Excel Discussion (Misc queries) | |||
Syntax or Something is KILLING Me | Excel Programming | |||
help me out with this - it's killing me.. | Excel Programming | |||
killing a sub from within an if statement | Excel Programming |