Free Microsoft Excel 2013 Quick Reference

Convert xls to pdf using VBA

Hi,

My issue is that I am trying to write an macro that will automatically convert the excel document I am currently in to PDF using the PDFDistiller.

When I do this I run into the problem where a dialouge box pops up asking me "Save PDF As". Is there a way to bypass this dialouge box or a way for excel to recognize this prompt and press save.

Thanks for your help!


Post your answer or comment

comments powered by Disqus
I have a small problem when trying to to convert Excel to *.PDF using VBA.
All I need to do is to open adobe and the save as dialog box to be straight
on to a shared directory rather than displaying the My Docs folder for the
save point.

Any ideas?

Thanks!!

I have a small problem when trying to to convert Excel to *.PDF using VBA.
All I need to do is to open adobe and the save as dialog box to be straight
on to a shared directory rather than displaying the My Docs folder for the
save point.

Any ideas how I can write this in VBA?

Thanks!!

Hi all,

I've been happily using the code on the webpage below to automatically convert an XLS file into a PDF through VBA for a while now (almost 2 years). Our machines here at the office had been running Adobe 6.0 Standard, but were recently upgraded to Adobe 8.0 Standard. Now the VBA code gets as far as creating a postscript (.ps) file but fails to convert that into a PDF. All machines are still running Windows XP Pro SP2 with Excel 2002 SP3.

Has anyone run into this situation? If so have you found a solution?
The automation saves me literally hours each month and I need to find a solution.

Original code here: http://www.igetit.net/SampleCode/Exc...F_vba_code.asp

Thanks in advance,
s_c

Hi All,

i am new to excel coding....

I have one excel sheet. in that 3 sheets are there,they are sheet1,sheet2,sheet3. so i want convert sheet1 data to pdf and also convert sheet2 data to pdf using coding vba(Macro). so please provide code or any examples.

please help me.....

Thanks in Advance
suresh

Hi, this is what i need to do:

1. Choose a folder from the Hard Drive.
2. Inside the choosen folder, i have some rtf files.
3. Loop between those files to open them and then convert them to pdf (using the included plugin on word 2007).
4. Close the files (if the conversion can be done without even open the documents would be great, there are lots of documents).

I know it should be a simple task for most, but i'm more than new to this world. Thanks in advance to everyone.

I've managed to create the following macro, but it just open the document and does nothing afterwards or word just generates an error and closes (most of the time):

Option Explicit
Sub SavePDF()
'
' SavePDF Macro
' Save documents in PDF format
'
Dim txtFolder As String, a, f
Dim strNombreArchivo As String

  'Get an existing txt folder name
  txtFolder = GetFolder
  If txtFolder = vbNullString Then Exit Sub

    a = GetFileList(txtFolder & "*.rtf")
    
  If Not IsArray(a) Then
   MsgBox "No rtf files found on: " & txtFolder, vbCritical, _
   "Macro Ending"
   Exit Sub
  End If
  
  'Iterate the txt files, open, SaveAs PDF
    Application.DisplayAlerts = False
    For Each f In a
      Documents.Open FileName:=f, ConfirmConversions:=False, ReadOnly:= _
        False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:= _
        "", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _
        Format:=wdOpenFormatAuto, XMLTransform:=""
        
      'strNombreArchivo = ActiveDocument.Name
      
      'ChangeFileOpenDirectory "C:Macro"
      ActiveDocument.ExportAsFixedFormat OutputFileName:=strNombreArchivo, _
        ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
        BitmapMissingFonts:=True, UseISO19005_1:=False
      
     'Workbooks.Open f
     'Edit
     'ActiveWorkbook.SaveAs txtFolder & Left(f, Len(f) - 3) & "xls",
     'FileFormat:=xlNormal, ConflictResolution:=xlLocalSessionChanges
     'ActiveWorkbook.Close False
    Next f
    Application.DisplayAlerts = True
 
End Sub

Function GetFolder(Optional sTitle As String = "Select Folder", _
  Optional sInitialFilename As String)
  Dim myFolder As String
  With Application.FileDialog(msoFileDialogFolderPicker)
    If sInitialFilename = "" Then sInitialFilename = "C:" 
    If Right(sInitialFilename, 1) <> "" Then
      sInitialFilename = sInitialFilename & ""
    End If
    .InitialFileName = sInitialFilename
    .Title = "Greetings"
    If .Show = -1 Then
      sInitialFilename = .SelectedItems(1)
    End If
    If Right(sInitialFilename, 1) <> "" Then
      GetFolder = sInitialFilename & ""
    End If
  End With
End Function


Function GetFileList(FileSpec As String) As Variant
'   Returns an array of filenames that match FileSpec
'   If no matching files are found, it returns False

    Dim FileArray() As Variant
    Dim FileCount As Integer
    Dim FileName As String
    
    On Error GoTo NoFilesFound

    FileCount = 0
    FileName = Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound
    
'   Loop until no more matching files are found
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function

'   Error handler
NoFilesFound:
    GetFileList = False
End Function


Hello,
I installed Acrobat on my computer. and there is an add-in function by Acrobat to tranform .xls to .pdf.
I would like to use Macros to transform the worksheets to .pdf automatically. Is there any methods to achieve it?
Thanks in advance.

Hi everyone,

I know that there are many programs to convet excel worksheets to pdf, but is there also a vba code that can convert an excel worksheet to PDF?

Thanks,

Hi for all,

I have 30 tables in 15 worksheets and now I want to make those be PDF format in one file. It seems that I just can convert one worksheet to PDF every time and then conbime into one. Can I write a VBA code to solver this problem? If you can provide some examples or links, it will be great.

Thanks!

Fei

hi frnds,

i am new to excel coding...

my doubt is

1) how to convert excel sheet to pdf using macro
2) if User selects macro within “sheet1” to send internal PDF copy via email using Outlook, and saves PDF to default directory

Thanks & regards
u r frnd..

Hi folks,

Please help me convert this to macros or vba.

I need some help with a macro

I have an item in column C, I want a macro that will will return '1' for unique items and '0' when it is not. It should return 1 or 0 in column B

I used this formula IF(MAX(COUNTIF(C$23:C$446010;C23:C446010))>1;0;1) but because is a huge file it take days

Thanks in advance

Cheers

We have a very strange issue with the use of SendKeys to print to pdf. It seems to work only with excel 2000. We have variables to give the name of the file we want (name of workbook & sheet), but when we try to use the same code in a 2003 version of excel, the dialog box needs to be filled in manually - not very efficient when you have 2000 sheets to print. I have read in many places that the use of SendKeys is not recommended but I have yet to find a different way to print to PDF using what we already have - Acrobat 7.0. Thanks in advance

HERE IS THE CODE

Sub printToPdf()

Dim x As Integer
Dim i As Integer
Dim j As Integer
Dim sheetName As String
Dim bookName As String
Dim compAbbr As String
Dim numReports As Integer
Dim WordObj As Word.Application
Dim PSFileName As String, PDFFileName As String, DistillerCall As String
Dim ReturnValue As Variant
Dim keepOpen

Workbooks("pointBook.xls").Activate
Sheets("specSheet").Select
numReports = Range("b10").Value
compAbbr = Range("b8").Value

For x = 1 To 10

Workbooks("BookName.xls").Activate
Sheets("SheetName").Select
Cells(1, 132 + x).Activate

'determine if section was generated
If x > 1 Then
Sheets("specSheet").Select
If ActiveSheet.CheckBoxes("Check Box " & x - 1).Value = xlOff Then
GoTo 500
End If
End If

Sheets("SheetName").Select
keepOpen = 1
bookName = ""

For i = 1 To numReports

keepOpen = 1
Workbooks("BookName.xls").Activate
Sheets("SheetName").Select
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Value = bookName Then
keepOpen = 2
GoTo 300
End If
If i > 1 Then
Workbooks(bookName).Save
Workbooks(bookName).Close
End If

bookName = ActiveCell.Value

300 If bookName = "" Then
GoTo 200
End If

sheetName = Cells(ActiveCell.Row, 2).Value

'Define the path and filenames
PSFileName = "c:folderName" & compAbbr & "ReportspdfParts" & sheetName & "_" & bookName & ".PS"
PDFFileName = "c:folderName" & compAbbr & "ReportspdfParts" & sheetName & "_" & bookName & ".PDF"
ActiveCell.Offset(0, 12).Value = PDFFileName

'open workbook
If keepOpen = 2 Then
GoTo 400
End If
Workbooks.Open "c:folderName" & compAbbr & "ReportsexcelParts" & bookName

400 Workbooks(bookName).Activate
Sheets(sheetName).Select

SendKeys PSFileName & "{ENTER}", False
ActiveSheet.PrintOut , PrintToFile:=True

'Add double quotes around the PS filename and PDF filename:
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)
DistillerCall = "C:Program FilesAdobeAcrobat"

200 Next i

Workbooks(bookName).Save
Workbooks(bookName).Close

500 Next x

End Sub

PDF Creator is an all-functional PDF maker tool that can create PDF documents from any

printable files, such as txt, images (JPEG, GIF, PNG, BMP, EMF, EWF, TIFF), compatible with

Microsoft Office 2003/2007/2010, support converting Word (doc, docx), PowerPoint (ppt,

pptx), Excel (xls, xlsx) to PDF file. PDF Creator can also save file format as PNG, JPEG, BMP, PCX, TIFF and PS, EPS under "option"

settings. More advanced pdf software
Text to PDF Converter
Word to PDF Converter

Hi

Im running into trouble writing an XLS sheet to PDF conversion. The
code writes the PDF file but it coms back with an error.

Sub pdfPrint()

Dim MyPath As String
Dim SourceString As String, OutputString As String, Suffix As String
Dim fName As String

fName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
MyPath = "C:Documents and SettingsEitanS.SSIDesktop"
Suffix = Format(Date, "ddmmmyy")
OutputString = MyPath & "" & fName & Suffix & ".pdf"

Application.ActivePrinter = "Adobe PDF on Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, printtofile:=True,
ActivePrinter:="Adobe PDF on Ne01:", Collate:=True,
prtofilename:=OutputString

End Sub

I have come across other code which works in older versions of Adobe
but not with Adobe 7 in Windows XP Pro.

All help appreciated.

Eitan

hi,

this is my first post.
i use windows 2000, with office 2000.

i was wondering whether it is possible to copy/export data from excel to word using VBA or any other features of excel.

basically i want to be able to click a button and the data gets pasted into a word template/document.

is this at all possible?

Hi all,

I am trying to copy specific information from a web site to Excel using VBA. I have tried a couple of methods found on the internet to no avail. The type of copy proceedure that I tried was execCommand "copy". When I wrote the code ie.document.execCommand "copy". It just wants to copy the whole page. I just need on piece of the page. If a form name is needed, I think it is form1. I cant put the entire source code in here, due to this being a private site. Thanks in advance for your help.

Hi,

I'm having trouble printing a document to pdf using a macro. When i record my actions i get the following code:

Sub Macro1()

Sheets("Sheet1").Select
Application.ActivePrinter = "Acrobat PDFWriter on LPT1:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat PDFWriter on LPT1:"
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

The bit i'm stuck on is the file path and name which i specified in the "Save PDF File As" box - How can this be placed into the code? - The file name is the same for all sheet but the path changes?

Any help will be greatly appreciated,

Cheers.

Hi all,
I am trying to convert .xls to .csv, and i have done that.
But problem is when i am trying to convert another .xls file .csv,
previously generated .csv file get disappers or lost.
Is any body facing same problem?

I want to save previously generated .csv file also..

Can any body give me solution?

Reply soon

Thank you,

sujay

How do you convert xls to plain ascii text without having to install
anything on the computer?

Hi All,

I have the below macro which prints all excel sheets to pdf using pdfcreator as printer, it works absolutely fine but I want to come up with a userform where all excel sheets of a workbook will be listed down and then the user will be able to select the sheets and create the pdf as per his requirement. The Userform will do the following things for user.

1) Userform will allow user to select the sheets which he want to print to pdf.
2) It will allow him to select a option wherein he will able to print multiple sheets to one pdf.
3) If he doesn't select that option it will print each sheet to a separate pdf and save it on a selected path.

I have come up with a userform but I need a help to incorporate the below macro to the same so that they work as per above requirements.

' Print Multiple Worksheets to a Single PDF File:
Option Explicit
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub PrintToPDF_MultiSheetToOne_Early()


    Dim pdfjob As PDFCreator.clsPDFCreator
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim lSheet As Long
    Dim lTtlSheets As Long

    '/// Change the output file name here! ///
    sPDFName = "Consolidated.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
    Set pdfjob = New PDFCreator.clsPDFCreator

    'Make sure the PDF printer can start
    If pdfjob.cStart("/NoProcessingAtStartup") = False Then
        MsgBox "Can't initialize PDFCreator.", vbCritical + _
                vbOKOnly, "Error!"
        Exit Sub
    End If

    'Set all defaults
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    lTtlSheets = Application.Sheets.Count
    For lSheet = 1 To Application.Sheets.Count
        On Error Resume Next 'To deal with chart sheets
        If Not IsEmpty(Application.Sheets(lSheet).UsedRange) Then
            Application.Sheets(lSheet).PrintOut copies:=1, ActivePrinter:="PDFCreator"
        Else
            lTtlSheets = lTtlSheets - 1
        End If
        On Error GoTo 0
    Next lSheet

    'Wait until all print jobs have entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = lTtlSheets
        DoEvents
    Loop

    'Combine all PDFs into a single file and stop the printer
    With pdfjob
        .cCombineAll
        .cPrinterStop = False
    End With

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
    MsgBox ("The PDF has been successfully created as " & sPDFName)
    pdfjob.cClose
    Sleep 1000
    Set pdfjob = Nothing
End Sub
Attached is my macro file.

Thanks a lot for your help in advance.

I will be merging data into a new CRM, the requirements for the CRM are to post everything from a CSV file, unfortunately most of the information is stored in a XLS format. How does one convert XLS to CSV?

I receive a report that has personnel numbers starting with zeros. They show in Excel with an apostrophe at the beginning to tell Excel to consider the number as text. I need the personnel number to show as a text value with the leading zeros (to copy and paste to another program). In the worksheet, I can use
=Text (reference cell,"00000000")
to convert the number to text with the leading zeros and without the apostrophe. I want to select a range and use VBA to automate the process but I can't find the correct macro function anywhere.

Thank you.

Hi,

Can anyone please provide me a code to copyall the pages of pdf file to excel using VBA.

I tried some of the code,But it works for only one page.

I want to copy all the pages in the PDF in a single stretch and copy the same to Excel.

Can anyone please provide the code for the same in VBA.

Thanks in Advance.
Karthik.

Is it possible to convert an xls workbook to pdf and save it within a beforesave sub. I have managed to cobble together a piece of code that saves using cell values as the filename, but I also need another copy saved as a pdf. I am using excel 2004 on a mac. As you can save file as pdf manually with File_Print_Save as PDF I thought it would be easy. Perhaps I need a later copy of excel. If anyone can help I would be very grateful.

Hi,

I receive an excel spreadsheet of daily orders received which I copy to a new workbook (with a macro) and then run another macro that does some formatting and other things ready for the data to be used in a MS Word mail merge for customer welcome letters and order receipts (don't ask why, I just do it!).

Here's the problem. The original sheet has columns E to H formatted as USD currency ([$$-409]#,##0.00) and even though I use the following code to paste the sheet information to the destination new workbook:

  	Code:

Windows("new_orders.xlsm").Activate 'this is the original workbook
With ActiveWorkbook.Sheets("orders")
Lastrow = Sheets("orders").Cells(Rows.Count, "B").End(xlUp).Row
Range("A1:XFD" & Lastrow).Select
Selection.Copy
End With

Windows("new_customers.xlsm").Activate 'this is the destination workbook
ThisWorkbook.Sheets("import_customers").Activate
With ThisWorkbook.Sheets("import_customers")
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E:H").Select
Selection.NumberFormat = "@"
End With

I still end up with the destination formatting for columns E to H being of type currency (£#,##0.00) although it is now in GBP for some unknown reason..... but I need it as text so that it imports correctly to word mail merge.

How can I change the cell format in VBA so that it is text?

There is a second problem. When the value of the cell is an exact pound amount, e.g. £32.00, if I manually convert it to format type text, it changes the cell contents to 32 when I actually want it to be 32.00.

Any ideas on how I can take a currency format and change it to text without losing the post decimal place digits when they are both zeros?

Any help would be greatly appreciated!

Thanks,

Nick

P.S. I'm using Office 2007


No luck finding an answer? You could always try Google.