Copy link to clipboard
Copied
I am currently using the following to create a list of hyperlinks in a spreadsheet, then save as a pdf with the acrobat pdfmaker references. (See code below)
We have it working almost fully but are not sure where to set the output preference for PDFMaker Conversion Options, right now it is defaulting to "Fit Worksheet to a single page" We would like it to default to "Fit to paper width"
Is this possible?
Any help would be greatly appreciated.​
Sub Example3()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim folderPath As String
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
folderPath = Application.InputBox("Enter path to files.")
Set objFolder = objFSO.GetFolder(folderPath)
i = 3
'loops through each file in the directory
For Each objFile In objFolder.Files
'select cell
Range(Cells(i + 0, 1), Cells(i + 0, 1)).Select
'create hyperlink in selected cell
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
i = i + 0
ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ$("username") & _
"\Desktop\Hyperlinks\" & Range("A3").Text & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, Password:=vbNullString, WriteResPassword:=vbNullString, _
ReadOnlyRecommended:=False, CreateBackup:=False
Dim pdfname, j, a
Dim pmkr As AdobePDFMakerForOffice.PDFMaker
Dim stng As AdobePDFMakerForOffice.ISettings
If Not ActiveWorkbook.Saved Then
MsgBox "You must save the document before converting it to PDF", vbOKOnly, ""
Exit Sub
End If
Set pmkr = Nothing 'locate PDFMaker object
For Each a In Application.COMAddIns
If InStr(UCase(a.Description), "PDFMAKER") > 0 Then
Set pmkr = a.Object
Exit For
End If
Next
If pmkr Is Nothing Then
MsgBox "Can not find PDFMaker add-in", vbOKOnly, ""
Exit Sub
End If
pdfname = ActiveWorkbook.FullName 'construct output name
j = InStrRev(pdfname, ".")
pdfname = IIf(j = 0, pdfname, Left(pdfname, j - 1)) & ".pdf"
'Delete PDF file if it exists
'If You (pdf name) <> "" Then Kill pdfname
pmkr.GetCurrentConversionSettings stng
stng.AddBookmarks = True 'make desired settings
stng.AddLinks = True
stng.AddTags = True
stng.ConvertAllPages = True
stng.CreateFootnoteLinks = True
stng.CreateXrefLinks = True
stng.OutputPDFFileName = pdfname
stng.PromptForPDFFilename = False
stng.ShouldShowProgressDialog = True
stng.ViewPDFFile = False
pmkr.CreatePDFEx stng, 0 'perform conversion
'If Dir(pdf_name) = "" Then 'see if conversion failed
'MsgBox "Could not create" & pdfname, vbOKOnly, "Conversion failed"
'End If
Next objFile
End Sub
Karl Heinz Kremer​ Maybe you have thoughts?
You won't like this answer: The PDFMaker is not Ann object that is available to developers - it's use is not documented and therefore unsupported. This means that Adobe has left the door open for them to change this interface anytime they want, so even if you can get this working, it could breaker with the next minor update to Acrobat.
Copy link to clipboard
Copied
You won't like this answer: The PDFMaker is not Ann object that is available to developers - it's use is not documented and therefore unsupported. This means that Adobe has left the door open for them to change this interface anytime they want, so even if you can get this working, it could breaker with the next minor update to Acrobat.