none
Create PDF from Excel using macro RRS feed

  • General discussion

  • Hi, 

    I need to create a macro in order to be able to automatically generate PDF's and then send this via email using the Office Outlook. Is there anyone that can suggest anything to this? 

    I did try the below marco, but when I try to run it comes up with 1004 error: 

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\Users\andreea.constantin\Desktop\PDF\Export.pdf", _
            OpenAfterPublish:=False
    End Sub

    Error: Document not saved. The document may be open, or any error may have been encountered when saving. 

    Thanks in advance,

    Andreea

    Tuesday, May 30, 2017 8:22 AM

All replies

  • It is likely that your path is incorrect. I tested and if the path is not correct then it returns the error message that you are getting. Desktop folder could also be write protected. while not normally protected on stand alone systems but could be if the computers are set up in a work environment where changes by the user are restricted by the Administrator.

    To ensure that you have the correct path, if you record the code to save the file as PDF then you will get the correct path. If this also fails then it is an indication that the folder is protected.

    The "Rule of Thumb" with computer coding is no defaults. Therefore it is good programming to use all of the arguments (or parameters) in the commands in VBA code. All of the arguments will be included in the recorded code. With some commands, Excel retains the last used setting for the arguments and this can cause problems if they do not match your current requirements.


    Regards, OssieMac

    Wednesday, May 31, 2017 6:10 AM
  • Hi OssieMac, <o:p></o:p>

    Thank you very much for the reply. I did the recorded macro and it worked perfectly. one more question to ask, if I have a pivot table with filters on, where I would like to export to pdf for each line in the filter is this possible to do in a macro automatically? <o:p></o:p>

    Thank you in advance, <o:p></o:p>

    Andreea<o:p></o:p>

    Thursday, June 1, 2017 11:01 AM
  • I believe that it is possible but how to code it will be completely dependent on how the data is arranged.

    With my testing it appears that you can set the filters and then simply save it as a pdf.


    Regards, OssieMac

    Friday, June 2, 2017 6:38 AM
  • Hi OssieMac, 

    So, i have the filters by vehicle & Date (only 1 week range). But I will have to have a new pdf to be generated by vehicle & then by date. Is this something possible? Can you give me an idea on how to do it? Or should it work with recording the macro? 

    Thank you

    Friday, June 2, 2017 10:59 AM
  • Try recording the macro and see how it goes. For me to do it for you I would need a copy of the workbook with the Pivot table set up and let me know what filters you want set etc. 

    If you don't succeed with the recording then if you upload a copy of the workbook to OneDrive then I will have a look at it for you.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    4. Go to this link.  https://onedrive.live.com
    5. Use the same login Id and Password that you use for this forum.
    6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    8. Right click the file on OneDrive and select Share.
    9. Select "Get a Link" from the popup menu.
    10. Click in the field displaying the link and it should highlight and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

    Regards, OssieMac

    Friday, June 2, 2017 8:37 PM
  • Hi OssieMac, 

    See below link from the file:

    https://1drv.ms/x/s!AqiUl7NlnzuXb6uVsBT7Wo79o7o

    Let me know if you can access it ok.

    Thank you so much

    Andreea

    Monday, June 5, 2017 11:09 AM
  • I have your workbook but I need confirmation of what you want done.

    Do you want the code to loop through all vehicles and set the filter for each vehicle and then for each vehicle a secondary loop through the dates and set the date filter. When the data for a vehicle and date is displayed, output the filtered data to a pdf file.

    If above is correct, how do you want to name the pdf? Suggest vehicle name concatenated with date. if the date is formatted as yyyy-mm-dd then the pdf files will sort in windows explorer by vehicle and date.

    If above is not correct then please explain in step by step what you want done.


    Regards, OssieMac

    Tuesday, June 6, 2017 2:06 AM
  • Hi OssieMac,

    That sounds perfectely as I would explain it. 

    Only one question to this, is it possible for the export to pdf to be done in a single page, this should include the details for the trips history also with the 2 coloumns (J to M) on the side with a summary of the trips? 

    Thank you, 

    Andreea 

    Tuesday, June 6, 2017 8:01 AM
  • Only one question to this, is it possible for the export to pdf to be done in a single page, this should include the details for the trips history also with the 2 coloumns (J to M) on the side with a summary of the trips? 

    I'll see what I can do with the single page when I get that far.

    I am making progress on the project but I am also a little busy with other commitments at the moment and it might be a few days before I can finish. Do you have any great urgency for this to be finished?


    Regards, OssieMac

    Wednesday, June 7, 2017 3:47 AM
  • Additional question:

    What version of Excel are you using? If more than one version with different users then list all versions so I can test that it will work in the required versions.


    Regards, OssieMac

    Wednesday, June 7, 2017 5:51 AM
  • it's version 1609 (build 7369.2130)

    thanks,

    Andreea

    Wednesday, June 7, 2017 8:05 AM
  • No urgency with this, but I would really appreciate the help on this.


    Wednesday, June 7, 2017 8:07 AM
  • it's version 1609 (build 7369.2130)


    As it is Office 2016 then I will continue development with Office 2016.

    There has been a couple of updates after the version you have quoted. It should eventually update automatically but if you can update now by Selecting File -> Account -> Update Options -> Update now and it should force an immediate update.

    Current version is 1704  8067.2157  June 1, 2017

    I am not sure if it updates to latest version when you are behind on updates so if you do run it then check again to see if you have the latest or need to run it again.

    Following link to update version information.

    https://support.office.com/en-us/article/Version-and-build-numbers-of-update-channel-releases-ae942449-1fca-4484-898b-a933ea23def7?ui=en-US&rs=en-US&ad=US

    I am well under way with the code. I still have some other commitments to fit in but I am hoping that I can finish it in to the next day or two. I am not good at estimating how long these things will take so no commitment as to when it will be finished.


    Regards, OssieMac

    Wednesday, June 7, 2017 10:23 AM
  • Hello Andreea,

    Download the zipped file example from the following link and test to see if it does what you want. Firstly, read the explanations below and then just run the code in the example workbook by clicking the button on the Summary worksheet and then open and print some examples of the pdf files.

    https://1drv.ms/u/s!ArAXPS2RpafCknPs1uT5JyfAGoDu

    Explanations:

    The PDF copies are in Landscape so they will fit to one page.

    I have shifted the trips summary to the left above the Pivot table so that it will fit on one page.

    The formulas in the trips summary have been modified. I changed the ranges from entire columns to row 13 to the end of the worksheets to avoid circular references after shifting the trips summary.

    If filters are set that do not display any data then the pdf is not processed. There is at least one vehicle for which a date is missing (probably not used that day) and hence there is no data when the filters are set so the system tests for data and if no data then no pdf is outputted.

    The average speed calculation in the trips summary has been modified. It is mathematically incorrect to average averages. The average speed is calculated from the sum of the miles divided by the sum of the hours. To get the total hours it is necessary to multiply the times by 24 to get a decimal number. (ie. 12 noon is 0.5 of a day and to get it into actual hours it is 0.5 X 24 = 12). See formula in cell E5.

    The processing is quite slow even with only 3 vehicles and 6 dates so if you are processing many then it will take some time. To alleviate the possibility of a User thinking that it is not doing anything, I have added a Userform with a Progress bar that will display the percentage completed and also the current vehicle and date that is being processed. The user can Cancel at any time but if it runs to the end then all pdf's should be completed.

    In Sub CreatePDF you will see that I have assigned the Save Path to the workbooks path with the following line of code (Has a comment after it referring to backslash suffix)

    strPath = ThisWorkbook.Path & "\" 

    Immediately following that line is a commented out example of coding to specify a different path if you so desire but you will need to modify the path to suit your purposes.

    Feel free to get back to me if any problems.


    Regards, OssieMac

    Thursday, June 8, 2017 11:34 AM
  • I forgot to include guidelines to export and import the Userform and VBA code if you want to install it in another workbook.

    If you want the Userform and VBA code to another workbook then the following procedure.

    To Export the Userform:

    1. Open VBA editor (Alt and F11)
    2. Select frmProgress in the Project Explorer. (Might need to expand the tree by clicking the plus sign against Forms so you can see frmProgress)
    3. Select menu item File -> Export file
    4. Navigate to required folder to save (I usually use same folder as the workbook but not essential so long as you remember where you save it.)
    5. Leave default filename and click Save button

    To Export Module1 code:

    • As for Userform but in Step 2, select Module1 in the Project explorer

    To Import the Userform to different workbook.

    1. Open the required workbook
    2. Alt and F11 to open the VBA editor
    3. Select Menu item File -> Import
    4. Navigate to where you saved the exported form
    5. Select the file (Name should be frmProgress.frm)
    6. Click Open and the Userform will import.

    To Import Module1:

    As for Import Userform but file name at step 5 will be Module1.bas

    Exporting and Importing the Userform automatically includes the VBA code in the Userform module.


    Regards, OssieMac

    • Edited by OssieMac Thursday, June 8, 2017 11:04 PM Corrected error with step numbering
    Thursday, June 8, 2017 11:02 PM
  • Hi OssieMac, 

    Everything looks very good, thanks a lot for this. 

    I;ve tested it for the 3 vehicles and it worked perfectly. I will just have to test it for all the vehicles that we have in our fleet and then I will let you know how did this worked. 

    Quick question, for using the code into a different spreadsheet, I only need to import the Userform and Module1 right? 

    thanks again, 

    Andreea

    Friday, June 9, 2017 9:15 AM
  • Hi OssieMac, 

    Everything looks very good, thanks a lot for this. 

    I;ve tested it for the 3 vehicles and it worked perfectly. I will just have to test it for all the vehicles that we have in our fleet and then I will let you know how did this worked. 

    Quick question, for using the code into a different spreadsheet, I only need to import the Userform and Module1 right? 

    thanks again, 

    Andreea

    Import the Userform and Module and also ensure that the worksheet names, Pivot table names and column headers etc are all the same and with the summary at the top of the Pivot table the same way as I have created it.

    In addition to the above, open "Page Setup" and set to Landscape and set Scaling to fit to 1 page wide by 1 page tall. The actual print area is set by the code based on the number of used rows on the worksheet after the filters are set. When the pdf is created, it uses the same page set up as is used if the worksheet is printed.

    The code resets the range for he defined name that you have for the range for creating the Pivot table. The code looks for the #Ref! error on the worksheet and resets the range to the data above the error. If there are no #Ref! errors, then it finds the bottom of the data and resets the Pivot table to the available data.

    if you run into problems with it then upload the entire workbook and I will try to sort it out.


    Regards, OssieMac

    Friday, June 9, 2017 10:20 AM
  • Hi OssieMac, 

    I tried to use your macro, I copied the data into report & data spreadsheets from your original file and when I run the create pdf macro it comes up with an error. 

    Run-Time error '1004':

    Unable to get the PivotItems property of the PivotField class

    "

    'Must have one item visible so set visible criteria first
                        .PivotItems(strDteCrit).Visible = True

    "

    See below a link to the file.

    Are you able to tell me what I did wrong or if you have any suggention in how to use your file when copying data into it? 

    https://1drv.ms/u/s!AqiUl7NlnzuXcSHpAoKRxjz82SQ

    Thanks in advance, 

    Andreea


    Friday, June 16, 2017 11:17 AM
  • I deleted my previous post. I have finally found the problem.

    I realized that after setting the vehicle filter, when I select the dropdown for the date I was getting multiple dates the same and that should not occur.

    I decided to interrogate the base data and found that from about row 3536 down, the data NumberFormat was "General" and hence none of the data was displaying in correct format.

    I copied a row with the correct formats and then selected the unformatted data and Paste Special -> Formats and all of the problem was fixed and I am not getting multiple dates in the Date Filter DropDown and no problem with "Out of Memory" error any more.

    I still need to do some more work on the Progress bar and will have a look at that in the coming days when I get some spare time but if you run the code, the Progress will start to display after some delay.

    New copy of your workbook at the following link.

    https://1drv.ms/u/s!ArAXPS2RpafCknlYovTXZZC44bEd


    Regards, OssieMac

    Saturday, June 17, 2017 9:55 PM
  • I have uploaded a new copy of the workbook. Same link as my previous post. I realize that I am still having problems with "Out of memory" after I get to about 75 PDF's created.

    I am still of the opinion that you might be better simply setting the filters on the data.

    You could set the required filters and copy the visible data to another worksheet with sufficient rows left at the top of the worksheet for your summary and then your summary formulas will be the same as you are using now.

    All of this can be done with VBA code and produce the pdf files but I believe that not having the Pivot Table could eliminate the memory problem.


    Regards, OssieMac

    Sunday, June 18, 2017 6:47 AM
  • Hi OssieMac, 

    I need the pipivot table for view, as a summary where that vehicle's been for the day. 

    Do you think anything can be done to this?? Or shhould only be donone in vba tataking off the pivot table?

    Thanks in advance, 

    Andreea 

    Monday, June 19, 2017 8:23 AM
  • It is 6:30pm in my part of the world and I have other things on tonight so I will have a look at this tomorrow with a view to providing your requirements as per my suggestions in my previous post. Maybe I can provide a satisfactory solution and maybe I can't but I will definitely try.

    Regards, OssieMac

    Monday, June 19, 2017 8:34 AM
  • Hi OssieMac, 

    Totally understand, sorry I wasn't trying to put pressure on you. 

    Whenever you can a response will be grately appreciated.

    Have a lovely evening

    Best Regards, 

    Andreea

    Monday, June 19, 2017 8:59 AM
  • No need for apology. I didn't interpret as putting pressure on me and my reply was really only a progress report to let you know that I could not provide an immediate answer.

    This is a further progress report. I have not been able to spend a great deal of time on it today and I have to be out most of the day tomorrow. However, it is coming along nicely and I will make every effort to get it to you ASAP.


    Regards, OssieMac

    Tuesday, June 20, 2017 8:18 AM
  • Hi OssieMac, 

    Thank you very much for this, no need to rush on it, you've helped us very much. 

    I did try to run the report yesterday, it works if I split the document in smaller parts.

    I have one more question for when you will get time for this report, is there any way that we can add another field to the pdf naming? I would like the field called "DeviceGroup" from the data spreasdsheet and to have this report before the vehicle name in order for me to be able to sort by device group. 

    Thanks in advance, 

    Andreea 

    Tuesday, June 20, 2017 8:31 AM
  • Just to confirm that we are on the same wave length are you asking for the pdf file name to be like the following example.

    Restricted Access AF63 EKJ - FORD RANGER 2017-06-05.pdf

    If above is correct then I don't think it should not be a problem.


    Regards, OssieMac

    Tuesday, June 20, 2017 11:10 AM
  • Yes, that's correct. 

    Thank you

    Andreea

    Tuesday, June 20, 2017 11:19 AM
  • Next edition for you to test. See the following link.

    https://1drv.ms/u/s!ArAXPS2RpafCknpQwXxyihwjKN0a

    1. The PDFs are created by filtering the "Report" worksheet and copying the visible data to "PDFs Prep" worksheet and then the worksheet is saved as a PDF.
    2. The formulas at the top of the "PDFs Prep" worksheet are same as you had on the Pivot Table worksheet.
    3. I have left the Pivot Table in place in case you still need it for other purposes.
    4. Select worksheet "PDFs Prep" (Disregard what is displayed on the worksheet because it will display the data for the last processed PDF).
    5. Select "Page Layout" ribbon
    6. In the Page Setup block click the little arrow bottom right of block to open the Page Setup dialog.
    7. Select Page tab
    8. Select your paper size.
    9. Ensure Landscape is selected
    10. Ensure that 1 page wide by 1 page tall is selected.
    11. The VBA code looks after the Print Area.
    12. The Lists worksheet is used to create unique lists of the vehicles and dates for setting the filters.
    13. Now select worksheet "Report" and click the "Start Process" button.
    14. I suggest that you Cancel after you have a few PDFs and check carefully that the data is correct and if not then let me know if any problems.

    Regards, OssieMac

    Wednesday, June 21, 2017 11:20 AM
  • Hi OssieMac, 

    I think it looks very good, i ran it as you said, no problems so far.

    Will keep you posted if any errors.

    Thanks, 

    Andreea

    Monday, June 26, 2017 10:46 AM
  • Hi Andreea,

    Thanks for the update and I hope it keeps working well for you. I am going away for a couple of weeks and while I will monitor my email I am not sure that I will have time to be doing any further work on your project during the next 2 weeks.


    Regards, OssieMac


    • Edited by OssieMac Monday, June 26, 2017 11:02 AM corrected typo
    Monday, June 26, 2017 11:01 AM
  • Hi OssieMac, 

    Have a good break then, enjoy it!

    Thanks again, 

    Andreea

    Monday, June 26, 2017 11:03 AM