Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Pdf Run-time Error 462:

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Ok I have a bug in my code and it is driving me crazy. Please help me!

I have several different subs that call a general pdf function (that uses PdfDistiller to create pdfs of the Excel s/s that the subs create) .

So for example:

Sub 1 will run and at the end of the process call the pdfing function then exits the sub

Sub 2 will run and at the end of the process call the pdfing function then exits the sub.

Sub 1 and Sub 2 work perfectly when they are run separately!

BUT if I run a Sub that runs Sub 1 and then calls and runs Sub 2 I get the following error message when the pdf function runs in Sub 2:

Run-time error '462':
The remote server machine does not exist or is unavailable.

This occurs on the following line:

PDFapplication.FileToPDF PSFileName, PDFFileName, ""

I have found a few things in relation to this error including:
http://support.microsoft.com/kb/178510

But I'm yet to figure out how to fix it. I suppose the frustrating thing is that it works in isolation but when run consecutively from different subs it fails.

I think it's something to do with not exiting the distiller application properly (like what must be done when the sub stops running)? or could it be that the pdf distiller is still busy with the jobs from the other sub? Because if I step through the sub that calls sub 1 and sub 2 (using F8) I do not get the error...... so do I need make the VBA wait for the distiller to finish? I am very confused.

I hope this makes sense to someone out there.

Any help would be much appreciated!!!!

The full pdf function is code is:

Public Function fPDFWorkbook(ReportsToPDF As Range) As Boolean
'For this sub to work the reference to 'Acrobat Distiller' in Tools --> References... needs to be selected.
Dim wbtoPDF As Workbook
Dim sCurrentPrinter As String
Dim PDFapplication As PdfDistiller '.Application
Dim X As Range
Dim FilePathName As String
Dim FileName As String
Set PDFapplication = New PdfDistiller
Dim BaseFileName As String
Dim PSFileName As String
Dim PDFFileName As String
Dim LogFileName As String
'Save the currently active printer
sCurrentPrinter = Application.ActivePrinter
For Each X In ReportsToPDF
Select Case X.Column
Case 4
FilePathName = X.Offset(0, 1)
FileName = X.Offset(0, 2)
Case 3
FilePathName = X.Offset(0, 2)
FileName = X.Offset(0, 3)
Case 2
FilePathName = X.Offset(0, 3)
FileName = X.Offset(0, 4)
End Select

BaseFileName = FilePathName & Left(FileName, Len(FileName) - 4)
PSFileName = BaseFileName & ".ps"
PDFFileName = BaseFileName & ".pdf"
LogFileName = BaseFileName & ".log"

UpdateProgress ("Opening " & FileName & " so that it can be converted to PDF...")
Set wbtoPDF = Excel.Workbooks.Open(FilePathName & FileName, ReadOnly:=True)

UpdateProgress ("Converting " & FileName & " to a Post Script File...")
'Print the workbook to the postscript file .ps
wbtoPDF.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, collate:=True, prtofilename:=PSFileName

'Colse the workbook
wbtoPDF.Close savechanges:=False

UpdateProgress ("Converting " & Left(FileName, Len(FileName) - 4) & ".ps file to a pdf file...")
PDFapplication.FileToPDF PSFileName, PDFFileName, ""

UpdateProgress ("Deleting the .ps and .log files...")
Kill PSFileName
Kill LogFileName
Next X
Application.ActivePrinter = sCurrentPrinter
Set PDFapplication = Nothing
fPDFWorkbook = True
End Function

Cheers Alex

View Answers     

Similar Excel Tutorials

Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
Simplify VBA Coding for Common Functions
' These modules exist just to make other VBA code easier to write and more readable ' For example, you can just ent ...
Calculate the Total Time Worked Minus Lunch Breaks in Excel
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...

Helpful Excel Macros

Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Output the File Path to and Name of a Workbook in Excel - UDF
- Free Excel UDF (user defined function) that displays the full file path and name of an Excel workbook. This function is

Similar Topics







We have a message box that pops up when we want to print and select whether we want to print to the printer or to Acrobat Distiller. It works when printing to a printer, but if you want it to print to pdf, you have to have Distiller selected as the default printer. However it is now bombing out on this first line. Is there anything in this that looks strange that could cause it to bomb?


Function Print_To_PDF1(MyFrom As Integer, MyTo As Integer, PDF_Print As Boolean, PSFileName As String)
'
'
Dim MyCurrentDir As String
Dim MyPSFileName As String


Dim MyCurrectDir As String, PDFFileName As String

MyCurrentDir = "C:\Temp\Benefit Data Individuals\"

PDFFileName = MyCurrentDir + PSFileName + ".pdf"
MyPSFileName = MyCurrentDir + PSFileName + ".ps"

If Dir(MyCurrentDir + PSFileName + ".ps") "" Then
MsgBox "File Already Exist! " + PSFileName + ".ps"
Exit Function
End If

If PDF_Print = True Then 'print to PDF if TRUE
ActiveWindow.SelectedSheets.PrintOut Copies:=1, printtofile:=True, _
collate:=True, prtofilename:=MyPSFileName
End If

'convert postscript file to PDF
Dim MyPDF As PdfDistiller
Set MyPDF = New PdfDistiller
MyPDF.FileToPDF MyPSFileName, PDFFileName, ""

Set MyPDF = Nothing

End Function


hey--

I am using this code to try to print an excel worksheet to a pdf file

Public Sub Distill_it()

' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\tmp\myPostScript.ps"
PDFFileName = "c:\tmp\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

MySheet.Range("A1:E4").PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller on Ne01:", printtofile:=True, collate:=True, prttofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub

I am getting and error that highlights the 3rd to last line and says: user-defined type not defined. Why am I getting this message and how do I fix the code to make this work?


Hi.
I'm trying to use VBA to automatically generate PDFs directly from an Excel worksheet.
I found some code online, which is shown he

Code:

' Define the postscript and .pdf file names.
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "C:\Specs Generator\PDF Creation Tests\myPostScript.ps"
PDFFileName = "C:\Specs Generator\PDF Creation Tests\myPDF.pdf"

' Print the Excel range to the postscript file
Dim MySheet As Worksheet
Set MySheet = ActiveSheet

MySheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, _
prtofilename:=PSFileName

' Convert the postscript file to .pdf
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""


But this doesn't seem to work.
The code creates a postscript file, but instead of the associated PDF file I get a .log file with an error message in it:

%%[ Error: undefined; OffendingCommand: JZJZ ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%

I'm using Excel 2003 with Adobe Acrobat Professional 8 with Distiller.
BTW, in the VBE 'Tools' menu --> 'References' list, I have made sure that 'Acrobat Distiller' is ticked.

Any ideas?
Thanks for your help.


We have a message box that pops up when we want to print and select whether we want to print to the printer or to Acrobat Distiller. It used to work to print to pdf just fine (provided you had previously selected Distiller selected as the default printer), but within the last week, we started getting the above VB Compile error when we select print to Distiller.

When you click debug after getting this VB error message, the row that it goes right to is the row
Code:

Dim MyPDF As PdfDistiller


but when you scroll up, the one with the yellow highlight and the arrow that it bombs out on is the first row he


Code:

Function Print_To_PDF1(MyFrom As Integer, MyTo As Integer, PDF_Print As Boolean, PSFileName As String)
'
'
Dim MyCurrentDir As String
Dim MyPSFileName As String


Dim MyCurrectDir As String, PDFFileName As String

MyCurrentDir = "C:\Temp\Benefit Data Individuals\"

PDFFileName = MyCurrentDir + PSFileName + ".pdf"
MyPSFileName = MyCurrentDir + PSFileName + ".ps"

If Dir(MyCurrentDir + PSFileName + ".ps") <> "" Then
MsgBox "File Already Exist! " + PSFileName + ".ps"
Exit Function
End If

If PDF_Print = True Then 'print to PDF if TRUE
ActiveWindow.SelectedSheets.PrintOut Copies:=1, printtofile:=True, _
collate:=True, prtofilename:=MyPSFileName
End If

'convert postscript file to PDF
Dim MyPDF As PdfDistiller
Set MyPDF = New PdfDistiller
MyPDF.FileToPDF MyPSFileName, PDFFileName, ""

Set MyPDF = Nothing

End Function



When I asked online what might be wrong with the code, I was told

Code:

Dim MyPDF As PdfDistiller


PdfDistiller I don't believe is a system-defined type.

As this is the row it first takes you to when you click debug, I am inclined to agree with the person, but I don't know how to "define" it or change the code to make it work.
Also have no clue why it used to work and now it doesn't. Makes me think that some new VB/Excel changes have come out that is now caused the code to be "defunct?"

I also had a suggestion from someone that said

If you want to keep the code as it is you'll need a reference to the PDFDistiller DLL Library, Tools>References...

Or you could try late-binding using CreateObject to create and reference an instance of Distiller.

But I have no clue how to do either. Again, any help would be greatly appreciated.


I didn't write the script and know very little about VB, but when we try to print to PDF within a huge excel program, the VB breaks when selecting printing to PDF. I get an error message on the Dim MyPDF As PdfDistiller line near the bottom of the code below. When I click OK, the first line in the code then highlights. Is the language "old" where the defining the As Integer, no longer needed?

Any thoughts on this would be most appreciated.

Function Print_To_PDF1(MyFrom As Integer, MyTo As Integer, PDF_Print As Boolean, PSFileName As String)
'
'
Dim MyCurrentDir As String
Dim MyPSFileName As String


Dim MyCurrectDir As String, PDFFileName As String

MyCurrentDir = "C:\Temp\Benefit Data Individuals\"

PDFFileName = MyCurrentDir + PSFileName + ".pdf"
MyPSFileName = MyCurrentDir + PSFileName + ".ps"

If Dir(MyCurrentDir + PSFileName + ".ps") "" Then
MsgBox "File Already Exist! " + PSFileName + ".ps"
Exit Function
End If

If PDF_Print = True Then 'print to PDF if TRUE
ActiveWindow.SelectedSheets.PrintOut Copies:=1, printtofile:=True, _
collate:=True, prtofilename:=MyPSFileName
End If

'convert postscript file to PDF
Dim MyPDF As PdfDistiller
Set MyPDF = New PdfDistiller
MyPDF.FileToPDF MyPSFileName, PDFFileName, ""

Set MyPDF = Nothing

End Function


Good morning Demigods

I have the follwoing code that save the current worksheet to .pdf in a directory.


Sub Print_PDF()

Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\PDF Files\Sample.ps"
PDFFileName = "c:\PDF Files\Booking Confirmation.pdf"
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, collate:=True, prtofilename:=PSFileName
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

End Sub


All I need to happen now, is when the user clicks a button, the above code runs and then the email window opens and it attaches itself to an email address located in cell A1.

I've read the guides, namely Ron de Bruin's, and whilst interesting, it is unfortunately way above my level of a) inteligence, b) knowledge and c) awesomeness.

If someone can help appreciation will be granted.

Regards
manc


Hi Demigods

I have a macro that creates a .pdf of the active sheet and adds it as an attachment to an email. I would like to change the font and size of the email message via the same macro, so the whole process is automated

I want the font to be "Calibri" and size 11

What lines of code can I add?

Code is pasted below...

Your help, as always, is appreciated.

Regards
manc


Sub Create_and_SendPDF()
Dim PSFileName As String
Dim PDFFileName As String
PSFileName = "c:\PDF Files\Sample.ps"
PDFFileName = "c:\PDF Files\Booking Confirmation.pdf"
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, collate:=True, prtofilename:=PSFileName
Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm

.Subject = "Booking Confirmation: " & Range("D20")
.To = Range("AB17")
.Body = "Dear " & Range("AB18") & vbCrLf & vbCrLf
.Body = .Body & Range("E24") & " " & Range("K24") & vbCrLf
.Body = .Body & Range("E25") & " " & Range("K25") & vbCrLf & vbCrLf
.Body = .Body & "Please find attached your booking confirmation." & vbCrLf & vbCrLf

.Attachments.Add "C:\PDF Files\Booking Confirmation.pdf"
.Attachments(1).Position = Len(.Body)
.Body = .Body & vbCrLf & vbCrLf & vbCrLf
.Display

End With

End Sub


Hi

I am trying to output to a pdf from Excel.
I found the code below from a search of previous topics

I understand it works in two parts:
- creates a postscript file
- uses distiller to create a pdf

The macro is running without errors and it is creating the postscript file
But it doesn't seem to be creating the PDF.

I was wondering if the files need to be a particular place for the FiletoPDF to work (the ps file is currently being generated in My Documents.

I am running Acrobat and Distiller v7.0 and Excel 2003 and I have installed the Acrobat libraries in Tools-->References

Any assistance would be greatly appreciated

Here is the code

Sub Printt()

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String

tempPDFRawFileName = "test"

tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"

ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName

Dim mypdfDist As New PdfDistiller

mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""

Kill tempPSFileName
Kill tempLogFileName
End Sub


Using an Excel macro, I want to create a pdf using Docudesk's deskPDF software. The installed ps interpreter (distiller) is Ghostscript. Additionally, I want to use code to define the ps and pdf file names, and the respective folder paths.

I have a working macro that does all of this, using Adobe PDF and Acrobat Distiller. My users have deskPDF only, so I'm looking for similar code for deskPDF. Alternatively, does anyone know the code to do this using Shell or SendKeys commands. My working code for Adobe PDF is as follows.

Sub makeMyPDF()
'##########################################
'# The Acrobat Distiller reference library must be enabled. #
'# In the Excel VB Editor window, select Tools > References #
'# and insure that Acrobat Distiller is checked. #
'##########################################
Dim pdfPrinter As String 'Adobe pdf printer
Dim ps_interpreter As PdfDistiller 'Acrobat distiller

Set ps_interpreter = New PdfDistiller

Application.Dialogs(xlDialogPrinterSetup).Show 'Prompt user to select PDF printer
pdfPrinter = Application.ActivePrinter 'Capture user's PDF printer

ActiveWindow.SelectedSheets.PrintOut Copies:=1, preview:=False, ActivePrinter:=pdfPrinter, _
printtofile:=True, Collate:=True, PrToFilename:="D:\Junk\myPDFtest.ps"

ps_interpreter.FileToPDF "D:\Junk\myPDFtest.ps", "D:\Junk\myPDFtest.pdf", ""

Kill "D:\Junk\myPDFtest.ps" 'Delete the ps file
Kill "D:\Junk\myPDFtest.log" 'Delete the log file

End Sub


I am very much a newbie on VBA macros. I had one we obtained from Mr. Excel and used to print an Excel worksheet to PDF. But over time I have moved from Acrobat 6 to Acrobat 8 professional and from Excel 2002 to Excel 2003. Oh, to make things more fun, I control the printing by running all of this from SAS (my true love). Now when I try run the same programming, this VBA macro fails at Dim mypdfDist As New PdfDistiller and says there is no project or library available. I have made sure that the VBE Tools|References| includes the appropriate references. I do recall in Acrobat 6 that it created a printer icon, in addition to Adobe PDF called Distiller something, perhaps even PdfDistiller. In acrobat 8 there is no such printer available. Based on my best guess, I'm assuming the creation of the PDF file is a 2 step process, step 1 creating the .ps file and step 2 converting .ps to .pdf. Not sure if the two step process is unnecessary in Acrobat 8. I can open up Acrobat distiller but it's a separate program, and not a printer. I'm missing something here, or the code change is simple. Any thoughts?
Thanks.

Code:

 
Sub LegisreportPDF()
'STEP1: create one set of filenames
Dim temPDFfilename As String
Dim temPSfilename As String
Dim temlogfilename As String
temPSfilename = Worksheets("Sheet1").Range("D1") & ".ps"
temPDFfilename = Worksheets("Sheet1").Range("D1") & ".pdf"
temlogfilename = Worksheets("Sheet1").Range("D1") & ".log"
 
Sheets("Card").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF on Ne02:", _
printtoFile:=True, Collate:=True, Prtofilename:=temPSfilename
Dim mypdfDist As New PdfDistiller
mypdfDist.FileToPDF temPSfilename, temPDFfilename, ""
Kill temPSfilename
Kill temlogfilename
'Code came from http://www.mrexcel.com/board2//viewt...c901810ebceabd
End Sub





I have Adobe Distiller 8.0. Using Excel '07.

The following code runs fine, but the pdf output that occurs is not a pdf but rather a log file stating:

%%[ Error: undefined; OffendingCommand: E*t600R&u600D*r0F&l1o&l2A&l7H&l0S&l1X&l8c1E*p0x0Y*c0t7632x5880Y*b0M ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%

There is lots on the internet about the error, but I can't find a solution. I need to use distiller rather than Excels PDF solution, because the PDF solution from microsoft messess up the format of the charts where distiller does not. Any help would be greatly appreciated. Thanks!

Code:

Sub InFlightUpdate()
    Dim I As Integer
    Dim wbJIRA As Workbook
    Dim wbResults As Workbook
    Dim wbInflight As Workbook
    Dim wbInflightLoc As String
    Dim InProcessFile As String
    Dim wbInflightLoc2 As String
    Dim PDFStartLoc As String
    Dim PDFEndLoc As String
    Dim PDFSheet As Worksheet
    Dim oPDF As PdfDistiller
    Dim TmpPSFile As String
 
    InProcessFile = "H:\Inprocess.xlsx"
 
    I = 2
    Do Until IsEmpty(Sheet2.Cells(I, 1))
'Action if effort is active, but not in test
        If Sheet2.Cells(I, 11).Value = 1 Then
            wbInflightLoc = Sheet2.Cells(I, 2).Value
            wbInflightLoc2 = Sheet2.Cells(I, 8).Value
            PDFStartLoc = Sheet2.Cells(I, 9).Value
            PDFEndLoc = Sheet2.Cells(I, 10).Value
            Name PDFStartLoc As PDFEndLoc
            Set wbInflight = Application.Workbooks.Open(wbInflightLoc)
                wbInflight.Sheets(2).Cells(75, 2).Value = Sheet2.Cells(I, 6).Value
                Kill InProcessFile
                wbInflight.SaveAs (InProcessFile)
                wbInflight.Close True
                Kill wbInflightLoc2
            Set wbInflight = Application.Workbooks.Open(InProcessFile)
                Set PDFSheet = ActiveSheet
                Set oPDF = New PdfDistiller
                TmpPSFile = "H:\TmpPSFile.ps"
                PDFSheet.PrintOut copies:=1, preview:=False, _
                ActivePrinter:="Acrobat Distiller", printtofile:=True, _
                collate:=True, PrToFileName:=TmpPSFile
                oPDF.FileToPDF TmpPSFile, PDFStartLoc, ""
                Kill TmpPSFile
                wbInflight.SaveAs (wbInflightLoc2)
                wbInflight.Close False
        Else
            'do nothing
        End If
'Action if effort is active, and in test
        If Sheet2.Cells(I, 11).Value = 2 Then
            'code to come
        End If
 
        I = I + 1
        Set wbJIRA = Nothing
        Set wbResults = Nothing
        Set wbInflight = Nothing
    Loop
End Sub





I am trying to write VBA code to automitically print to an ADOBE file.
I am somewhat familiar with VBA, but most of my experience is with Active Controls and User Menus and I have VERY limited experience with Adobe. I am saying this, because I have searched for examples online, and cannot get any of them to work. Probably because I am not properly defining the post script files etc...
One example:

Private Sub CommandButton1_Click()
Dim oSheet As Worksheet
Dim oPDF As PdfDistiller ( I get an error here)
Dim TmpPSFile As String
Dim PDFFile As String
Set oSheet = ActiveSheet
Set oPDF = New PdfDistiller
TmpPSFile = "c:\TmpPSFile.ps"
PDFFile = "c:\" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & ActiveSheet.Name & ".pdf"
oSheet.PrintOut copies:=1, preview:=False, _
ActivePrinter:="Acrobat Distiller", printtofile:=True, _
collate:=True, PrToFileName:=TmpPSFile
oPDF.FileToPDF TmpPSFile, PDFFile, ""
Kill TmpPSFile
End Sub

I am using Adobe Acrobat/Distiller 8. Notes say to "Do not send fonts to Distiller" Option, but I cannot find that option within Distiller.

I am just looking for something very simple. I print about 20-25 reports out of my macro enabled worksheets a day - and just have to convert so supervisors can open with their IPADS.
Thank You,
Todd


I have successfully used the code detailed below to convert from excel to PDF from my PC but when the macro is used on another machine it halts at the line "Dim myPDFDist As New PdfDistiller" saying printer not found.

Could anyone suggest a solution that could be run on any PC, if they are using a different PDF printer to Adobe. Id it that PDF distiller needs to be installed on the other PC?

Many thanks in advance
Martin



Sub Create_PDF()

'Created by Dom Hill with considerable asistance from Biggoan and Mr Excel

Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Dim tempshowWindow As String
Dim SaveLocation As String


SaveLocation = Sheets("data page").range("H49")
Sheets("P&L Output").Activate

tempPDFRawFileName = SaveLocation & safeFileName(range("a1").Value)

'Define the postscript and .pdf file names.

tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"

' Print the Excel range to the postscript file

ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName

'Create PDF File

Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF tempPSFileName, tempPDFFileName, tempshowWindow

'Delete PS File

Kill tempPSFileName
Kill tempLogFileName

End Sub


Hey all -

I am trying to write a macro that will print a worksheet to PDF and automaically name the file. This is the code I have so far:

Option Explicit

Private Sub PrintToPDF_Click()

Dim CurrentPrinter As String
Dim fileName As String

Let fileName = Cells(7, "e")

'Sets the sheet to print out in black and white
ActiveSheet.PageSetup.BlackAndWhite = True

CurrentPrinter = Application.ActivePrinter ' save the currently active printer
On Error Resume Next ' ignore errors
Application.ActivePrinter = "Adobe PDF on Ne05:" ' change to PDF printer
SendKeys fileName & "{ENTER}" ', False
ActiveSheet.PrintOut ' print the active sheet (Network Funding)
Application.ActivePrinter = CurrentPrinter ' change back to the original printer
On Error GoTo 0 ' resume normal error handling
End Sub

The problem is the sendkeys function does nothing and I still have to manually input the file name. Can anyone please help?!?!

Thanks -
Cara


I'm using the following code to attempt to add a workbook. It was functioning in this exact form but now just fails to add the new workbook. No error message, no nothing.

Code:

Dim FilePathName As String
Dim Element As String
Dim savebook As Workbook
Dim newbook As Workbook
Dim currentbook As Workbook
Application.ScreenUpdating = False
Savings
Set currentbook = ActiveWorkbook
Workbooks.Add.Activate
ActiveWorkbook.SaveAs Filename:=Application.GetSaveAsFilename("MyFileName.xls", _
        "Excel files,*.xls", 1, "Select your folder and filename")
Element = ActiveWorkbook.Name


has me baffled!

Any help appreciated!


I'm having a problem writing a VBA function. I want to call a function that looks something like this... GetLink("CCF-2000",K18) where the CCF-2000 is a project folder on my computer and K18 references a code related to the city. Using K18 and the Case function I'll differentiate between different file paths related to different cities.

Anyways, I got the Case function to work properly, but when I try to run the Excel Hyperlink function in VBA it returns #VALUE. I want it to return something simliar to =HYPERLINK("G:\CCF-2000", "CCF-2000"), but using a function to figure out the path based on the city code.

The K column only contains data such as SG, SP, LE, ENW, etc... that specify a city. Each city has it's own path on the local computer.

Here's the function I was trying to build to accomplish this. I wanted to call this function in another column... AC, to return a hyperlink, but to no avail.

Quote:

Function GetLink(Filename as String, City as String) as String

Select Case City Case "ENW", "ENE", "ESW", "ESE"
GetLink = Application.WorksheetFunctions.Hyperlink("G:\" & Filename, Filename)
Case "SG"
GetLink = Application.WorksheetFunctions.Hyperlink("G:\SG\" & Filename, Filename)
Case Else
GetLink = "Escape"
End Select

Hopefully, there's something in there that makes sense. It's my first VBA function, so hopefully that's not too far from the truth.


Hello All,

I used to use the code below to convert all XLS files in a specified directory to SYLK on Excel 97, but now that I updated to Excel 2007 I get the following error:

Compile error: Sub or Function not defined.

Can anyone tell me what went wrong?

Code:

Sub SYLKConversion()
    Dim Path As String, ThisWB As String, lngFilecounter As Long
    Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
    Dim FileName As String, Wkb As Workbook
    Dim CopyRng As Range, Dest As Range
    Dim RowofCopySheet As Integer

    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

    ThisWB = ThisWorkbook.Name
    
    Path = GetDirectory("Select a Folder Containing Excel Files you want to Convert")
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set shtDest = ActiveWorkbook.Sheets(1)
    FileName = Dir(Path & "\*.xls", vbNormal)
    If Len(FileName) = 0 Then Exit Sub
    Do Until FileName = vbNullString
        If Not FileName = ThisWB Then
            Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
            Wkb.SaveAs FileName:=Path & "\" & Left(FileName, Len(FileName) - 4) & ".slk", FileFormat:= _
xlSYLK, CreateBackup:=False
            Wkb.Close False
        End If
        
        FileName = Dir()
    Loop

    Range("A1").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    MsgBox "Done!"
End Sub





I've been trying to automate a process to print a series of PDF files. The
code below generates the file, but the file is courrupted in some way and
won't open in Acrobat. I change the "Do not sent fonts" option for the
Acrobat Distiller to false. I also tried taking the references to the
distiller out of the code. I'm sure it must be possible and suspect there
may be other Distiller options that may need to be changed. Any tipes would
be greatly appreciated.


Code:
Application.ActivePrinter = "Acrobat Distiller on Ne01:"

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat Distiller on Ne01:", PrintToFile:=True, Collate:=True, _
PrToFileName:=PrFile

Sincerely,

Wayne





I am trying to swith between different printers to print out sheets within a single workbook. Recording the macro doesn't work very well and the code I found online doesn't work either (error: object doesn't support property).
How can I set this up to be a dynamic printer setting?
Code I tried:
Code:

Sub MyPrint()
Dim sCurrentPrinter As String

sCurrentPrinter = ActivePrinter
ActivePrinter = "my printer name"
Application.PrintOut Filename:="my file name"
ActivePrinter = sCurrentPrinter

End Sub


I would really appreciate some help.


I have this macro that currently opens both Excel Workbooks and Word Documents. Now, I have been asked to also have it open WordPerfect documents. I need some help please.

'

'ONE BUTTON CALLS THIS MACRO WHICH IN TURN CALLS THE MACRO AND FUNCTION TO OPEN THE CORRECT WORKBOOK OR DOCUMENT

'ALMOST ALL OF THE COMPUTERS WILL "NOT" HAVE THE "WORDPERFECT" APPLICATION INSTALLED. I WANT IT TO OPEN IN "WORD".

'THE FOLLOWING MACRO WORKS CORRECTLY

Code:

Sub ExcelOrWordFile()

Select Case ActiveCell.Column
Dim cl As Range
    Case 1, 3, 5, 7, 9, Is > 11
    MsgBox "Please select the name of a file you want to open."
    End
    End Select

Set cl = ActiveCell.Offset(0, -1)
   Select Case LCase(Right(cl.Value, 3))
   Case "xls", "xla"
      Call NewExcelWithWorkbook
   Case "doc"
      Call NewWordWithDocument
   Case "wpd"
      Call NewWordPerfectWithDocument
   Case Else
      MsgBox "Please select the name of a file you want to open.", vbInformation
   End Select
End Sub


'
'
'
'
'

'THIS IS THE WORKING MACRO THAT OPENS A WORD DOCUMENT
Code:

Sub NewWordWithDocument()
    Dim WdApp  As Object
    Dim sDoc   As String
    Dim cl     As Range
    Dim testFileFind As String
    
        'TESTS TO SEE IF BOTH PATH & FILE ENTERED
Set cl = ActiveCell.Offset(0, -1)
    If Len(Trim(cl)) = 0 Then
        MsgBox "You have not entered a Path & File Name."
    End
End If
    
        'TESTS TO SEE IF FILE NAME EXISTS
testFileFind = Dir(cl)
    If Len(testFileFind) = 0 Then
        MsgBox "Invalid selection." & Chr(13) & _
               "Filename " & cl.Value & " not found"
    End
    End If
        
        'THIS TESTS TO SEE IF DOCUMENT ALREADY OPEN
    If FileAlreadyOpen(cl.Value) = True Then
        MsgBox "File is already open"
    End
    Else
    End If
        
        'GET THE FILE NAME
    sDoc = ActiveCell.Offset(0, -1)
    Set WdApp = CreateObject("Word.Application")
    WdApp.documents.Open (sDoc)
    WdApp.Visible = True

End Sub


'
'
'
'
'

'THIS IS THE MACRO THAT I NEED TO OPEN A WORDPERFECT DOCUMENT
'THIS NEEDS TO BE CORRECTED AS IT WAS JUST COPIED FROM THE OPENING A WORD DOCUMENT MACRO

Code:

Sub NewWordPerfectWithDocument()
   Dim WdApp  As Object
    Dim sDoc   As String
    Dim cl     As Range
    Dim testFileFind As String
    
        'TESTS TO SEE IF BOTH PATH & FILE ENTERED
Set cl = ActiveCell.Offset(0, -1)
    If Len(Trim(cl)) = 0 Then
        MsgBox "You have not entered a Path & File Name."
    End
End If
    
        'TESTS TO SEE IF FILE NAME EXISTS
testFileFind = Dir(cl)
    If Len(testFileFind) = 0 Then
        MsgBox "Invalid selection." & Chr(13) & _
               "Filename " & cl.Value & " not found"
    End
    End If
        
        'THIS TESTS TO SEE IF DOCUMENT ALREADY OPEN
    If FileAlreadyOpen(cl.Value) = True Then
        MsgBox "File is already open"
    End
    Else
    End If
        
        'GET THE FILE NAME
    sDoc = ActiveCell.Offset(0, -1)
    Set WdApp = CreateObject("Word.Application")
    WdApp.documents.Open (sDoc)
    WdApp.Visible = True

End Sub


'
'
'

'THIS FUNCTION WORKS CORRECTLY
Code:

Function FileAlreadyOpen(FullFileName As String) As Boolean
' returns True if FullFileName is currently in use by another process
' example: If FileAlreadyOpen("C:\FolderName\FileName.xls") Then...
' The above example is only used when hard coded.
Dim f As Integer
    f = FreeFile
    On Error Resume Next
    Open FullFileName For Binary Access Read Write Lock Read Write As #f
    Close #f
    ' If an error occurs, the document is currently open.
    If Err.Number <> 0 Then
        FileAlreadyOpen = True
        Err.Clear
        'MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
    Else
        FileAlreadyOpen = False
    End If
    On Error GoTo 0
End Function





After much searching and many times of trial and error, I have finally found a macro that will quickly and easily convert an excel worksheet to PDF and automatically do a save as with a filename based on content in a specified cell. Hope it works for you as well as it works for me!

The following code has been tested on Excel 2003 and Acrobat 8.0 Pro:

' This line of code specifies your directory as well as the cell or range which you want the filename to come from. As you can see, I have a specific cell with the range name "InvNbr" so that the macro knows to pull the filename from there. If you don't want to use a range name, just replace InvNbr with your cell reference, such as C4.

Filename = "C:\Folder1\SubFolder1\" & ActiveSheet.Range("InvNbr").Value & ".pdf"

' This line of code sends the filename characters and the ENTER key to the active application. The "False" statement allows the macro to continue running without waiting for the keys to be processed.

SendKeys Filename & "{ENTER}", False

' This line of code calls the Adobe PDF printer and runs the conversion. To ensure that you replace this code correctly with your own PDF printer, simply record a macro to print to Adobe PDF and then copy and paste it here.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:", Collate:=True



If you don't have Acrobat Pro and are using a free version of PDF conversion software, try the following (it has been tested on Excel 2003 and CutePDF):

' This line of code calls your PDF printer and runs the conversion. Record your own macro to call your PDF printer and copy and paste it here.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True

' This set of code tells the macro to pause for 2 seconds. This will allow for the PDF printer to run through its process and prompt you for a filename.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

' This line of code specifies your directory as well as the cell or range which you want the filename to come from.

Filename = "C:\Folder1\SubFolder1\" & ActiveSheet.Range("InvNbr").Value & ".pdf"

' This line of code sends the filename characters and the ENTER key to the active application (i.e. the prompt window). The "False" statement allows the macro to continue running without waiting for the keys to be processed.

SendKeys Filename & "{ENTER}", False


The beautiful thing about these macros is that you can specify your directory, so you can customize them to send your worksheet to whatever directory you want and save it under whatever filename you want. Awesome!

I hope someone else finds this useful!!!


I have code like this:

Code:

Private Sub CommandButton2_Click()

    Filename = " " & ActiveSheet.Range("H6").Value & ".pdf"
    SendKeys Filename
    
    Application.ActivePrinter = "Adobe PDF porttiin Ne04:"
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, _
    ActivePrinter:="Adobe PDF porttiin Ne04:", Collate:=True
        
End Sub


It opens save window well and it creates also correct name automatically. When I press save, windows closes but nothing else happens. No PDF was created.

You probably see that there's " " in the beginning of the filename creating row. If I use
Code:

Filename = ActiveSheet.Range("H6").Value & ".pdf"


It creates pdf indeed but name is the same as my worksheet's name is.

What is going wrong?

I noticed that there's topics which recommends to use distiller directly after adding acrobat dostiller to references. But I don't get it to work either.


I'm using Word 2002 SP3 and Adobe Acrobat Professional 7.
I am trying to automate a save process that will create a PDF File.

The code below will create the Distiller File, however it will not run the distiller to complete the conversion to PDF.
I can manually go into the Distiller and click on the file and it creates the Pdf w/ the correct file name

I have already added the Acrobat Distiller, Create Adobe PDFMAker for Office and Adobe PDFMakerX to the Refereneces in VBA

Code:

Sub Create_PDF()
 
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
 
tempPDFRawFileName = ("H:\CreatePDF")
 
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
 
ActiveDocument.PrintOut OutputFileName:=tempPSFileName

Dim mypdfDist As New PdfDistiller

mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""

Kill tempPSFileName
kill templogFileName
End Sub


I am getting a RunTime Error '-2147417851 (80010105)' Method 'FileToPDF' of object 'IPdfDistiller' failed.

Any help would be most appreciated.
-Eric


I'm using Excel 2003.

I'm looping through files, opening them, and writing info into another Excel spreadsheet. I need to check for corrupt excel files. I'm getting close, but I'm getting the error message "this file is not in a recognizable format" and the user would have to remember to select "cancel" (and not "OK"). I'd like to suppress this error message.

My code:

In my sub:

Global FileIsCorrupt as boolean
Global NewFileToCheck as string
Global FileName as string
Global Path as string

(Filename and path are set at this point...)

OpenFileAndCheck 'call function

If FileIsCorrupt = True then 'if true, file is corrupt or unrecognizable
msgbox ("msg to user that it is corrupt and file is being skipped')
GoTo FoundCorruptFile ' skips over writing info from file
End if

=====
Function OpenFileAndCheck() as Boolean

On Error GoTo ErrHandler
FileIsCorrupt = False 'set to false--it will be reset to true if file is corrupt

Set NewFileToCheck = Workbooks.Open(Filename:=Path & FileName)

Exit Function

ErrHandler:
If Err 0 then
FileIsCorrupt = True
End if

End Function
=====

Your help would be so appreciated!


Hello,
I am trying to use VBA to copy the entire contents (data) of "Sheet1" from multiple workbooks located under a single folder to a master workbook named Headcount Rollup Template. Each sheet should be imported to a specific tab in the master workbook.

I have set up a sheet within my master called "Tab Names" that contains the table I am referencing to map the file to the sheet tab name. It is set up like this:

Column A
Wbk Name
Active HC - US Data.xls
Active HC - UK Data.xls
Active HC - France Data.xls

Column B
Sht Name
US HC
UK HC
FRA HC

Example: all data in "Sheet1" in Active HC - US Data.xls should be copied to tab "US HC" in master workbook

I have been working with the code below but getting an "Object Required" error on this line: "Wbk.Sheets("Sheet1").Cells.Copy Destination:=sht.[A1]" when it runs.

Here is my current code that returns an object required error:

Sub ImportSheets()
Dim Path As String
Dim filename As String
Dim sht As Worksheet
Dim wkB As Workbook
Dim i As Integer
Dim r As Range
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
'Import the first worksheet for each file under R:\HC Data
Path = "R:\HR Ops Team Folders\Reporting & Analysis\Headcount\HC Data"
filename = Dir(Path & "\*.xls")
i = 1
Do While filename ""
For Each r In Worksheets("Tab Names").Range("A2:A9")
If r.Value = filename Then
'this assumes that the sheet name is in the next column
Set sht = ThisWorkbook.Sheets(r.Offset(0, 1).Value)
Exit For
End If
Next
Workbooks.Open filename:=Path & "\" & filename
If SheetExists("Sheet1") Then
Set wkB = ActiveWorkbook
Wbk.Sheets("Sheet1").Cells.Copy Destination:=sht.[A1]
'ActiveSheet.Name = "Sheet" & i
i = i + 1
wkB.Close savechanges:=False
Else
MsgBox "Sheets1 does not exist in file '" & filename & "'", vbExclamation, "ERROR !!"
ActiveWorkbook.Close savechanges:=False
End If
filename = Dir
Loop
Sheets("Tracking #").Select
MsgBox "All files have been imported successfully!"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

__________________________________________________ _________
Function SheetExists(ByVal sName As String)
Dim sht As Worksheet

SheetExists = True
For Each sht In ActiveWorkbook.Sheets
If sht.Name = sName Then Exit Function
Next sht

SheetExists = False
End Function


I am trying to get the code to loop through and map the file name to the tab name and copy the data in the tab location of the mast worksheet.

Any help is greatly appreciated!!