PDF issue when converting from Excel

[Guest]

I am using Excel XP with Windows 2000 and Adobe Acrobat 6.0 Professional.

This may not be the appropriate forum for this question, but I get better
response here than any where else. The Excel pros are the best!

When I convert an Excel sheet to a PDF file, the last character of the last
word on the ends of some of the lines are chopped off. This occurs even
though the column in which the text resides is plenty wide enough. It seems
sporadic, some longer text lines are not affected (and some are), while some
shorter lines are affected (and some aren't).

Also, this seems to occur mostly in documents that have reduced font sizes
(Arial is used for everything). By reduced, I mean like 7 points.

Please, if anyone has a clue why this occurs, and most especially how to
correct it or where else to go for help, please post back. Thanks for your
assistance.

By the way, I already looked on the PDF site without success. TIA.

 

[Barb Reinhardt]

This sounds suspiciously like a graphing problem where the Y axis labels are
cut off at the end. I wonder if they are related.

 

[Guest]

I don't know as I have not experienced that issue. My problem is just text in
a cell. Any ideas?

 

[Guest]

Could you post your VBA code that you use to convert your Excel worksheet to
PDF, as I have been trying to find code that uses Acrobat 6 that will convert
to PDF. Since Adobe went to version 6 and removed the PDFWriter printer, all
my VBA code no longer works, that used to work marvelously with Version 5 of
Acrobat.

 

[Guest]

Sure, I hope this helps. I've broken down the code into functions, so I hope
you can follow it. First, you need to create a reference to "Acrobat
Distiller" and "Acrobat PDFMaker". Sorry, but my code is so customized it is
hard to strip it down to just the PDF stuff, but hopefully this will give you
something to play with.

First it converts the file to a postscript file, then it converts the
postscript file to a PDF, then there is a function that combines all the
files into one PDF document. I have a lot of variables for file names and
paths, but I think you will be able to tell what they are, if not, post back.

This has been stripped away from a program that is designed to deal will
hundreds of files at a time. So it uses arrays a lot.

1) Code that converts files into postscript files:

'Get original printer name
strOriginalPrinterName = Application.ActivePrinter

'Application.ActivePrinter = "Adobe PDF on Ne01:"
ActiveWindow.SelectedSheets.PrintOut _
Copies:=1, _
ActivePrinter:="Adobe PDF on Ne01:", _
PrintToFile:=True, _
Collate:=True, _
PrToFileName:=pstrPath_PST & strNewFileName

'Reset printer
Application.ActivePrinter = strOriginalPrinterName


Function that converts a postscript file into a PDF (I had to build in a
pause to give distiller time to run):

Public Function PDFConvertPSToPDF(argPSPath As String, argPSFileName As
String, argPDFPath As String, argPDFFileName As String)
'FUNCTION CONVERTS ACROBAT POSTSCRIPT FILES (PS) TO PDF FILES;
Dim strPSFullPath As String
strPSFullPath = argPSPath & argPSFileName
Dim strPDFFullPath As String
strPDFFullPath = argPDFPath & argPDFFileName

Application.Wait (Now + TimeValue("0:00:01"))

Dim objDistiller As PdfDistiller
Set objDistiller = New PdfDistiller
objDistiller.FileToPDF strPSFullPath, strPDFFullPath, ""
Set objDistiller = Nothing
DoEvents

End Function

Function to combine all the PDF files in a folder into one PDF document:

Public Function PDFCombineFiles(argPDFSourcePath As String,
argDestinationPath As String, argDestinationFileName As String)
'FUNCTION COMBINES ALL PDF FILES IN THE SOURCE PATH INTO A SINGLE PDF
DOCUMENT;
Dim arrPDFFileList() As Variant
Dim strFullPath As String
Dim intX As Integer
Dim intLastPage As Integer
Dim intNumPagesToInsert As Integer

'Copy all PDF files in source path into an array
arrPDFFileList = FileList(argPDFSourcePath, "*.pdf", False)
If IsEmpty(arrPDFFileList) Then MsgBox "No PDF files were found!",
vbCritical, "FILES NOT FOUND!": Call CommonEnd(True)

'Dimension required objects
Dim objAcroExchApp As Object
Dim objAcroExchPDDoc As Object
Dim objAcroExchInsertPDDoc As Object

'Establish object references
Set objAcroExchApp = CreateObject("AcroExch.App")
Set objAcroExchPDDoc = CreateObject("AcroExch.PDDoc")

'Optionally show the Acrobat Exchange window - just to see if it works
'oAcroExchApp.Show

'Open the first file in the list
strFullPath = argPDFSourcePath & arrPDFFileList(1)
objAcroExchPDDoc.Open strFullPath

'Initialize a loop through each file in the PDF folder
For intX = 1 To UBound(arrPDFFileList)

'Concatenate source path and file name into a single string
strFullPath = argPDFSourcePath & arrPDFFileList(intX)

'If intX > 0 Then
If intX > 1 Then

'Sequentially open each remaining file in the directory
objAcroExchPDDoc.Open strFullPath

'Get the total pages less one for the last page num [zero based]
intLastPage = objAcroExchPDDoc.GetNumPages - 1

'Obtain an object reference to the Exchange program in PDF
Set objAcroExchInsertPDDoc = CreateObject("AcroExch.PDDoc")

'Open the file to insert
objAcroExchInsertPDDoc.Open strFullPath

'Count the pages in the current document to insert
intNumPagesToInsert = objAcroExchInsertPDDoc.GetNumPages

'Insert the pages
objAcroExchPDDoc.InsertPages intLastPage, objAcroExchInsertPDDoc, 0,
intNumPagesToInsert, True

'Close the document
objAcroExchInsertPDDoc.Close
End If
Next intX

'Save the entire document using SaveFull [0x0001 = &H1]
objAcroExchPDDoc.Save &H1, argDestinationPath & argDestinationFileName

'Close the PDDoc
objAcroExchPDDoc.Close

'Close Acrobat Exchange
objAcroExchApp.Exit

'Toss objects and release memory
Set objAcroExchApp = Nothing
Set objAcroExchPDDoc = Nothing
Set objAcroExchInsertPDDoc = Nothing

End Function

Hope this helps you out.