• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HOW TO DOWLOAD EXCEL INVOICE INTO PDF FORMAT

N

nkms143

Member
Dears,

i have created an invoice template for which i used table in one sheet and deployed the values by vlookup formula in another sheet (Sheet name is Page_Print). Now what i want is if i press Blue Oval shape then the Page_Print invoice should be downloaded in pdf file where the user want. is is possible
 

Attachments

  • EXCEL TO PDF.xlsm
    40.4 KB · Views: 13
So you want to save a copy of the invoice on the Page_Print as a PDF some where that the user choose to save it to?

How do you know where the user is going to save it to?

Quicker and easier to set the print area of Page_Print and tell the use to save it as a PDF in a common directory. You could set this up with a macro assigned to a shape button.


.
 
That's exactly, i tried recording a macro but the 2nd invoice generated pdf is overwriting the 1st generated existing pdf file. hence i need a solution such that it should download the pdf with any unique name so that the previous pdf file shouldn't be overwritten.
 
Hi,

(NON VBA SOLUTION)
Since it is a matter of saving file as PDF, why not just install any PDF Printer?
This will let you to save your file to the desire location + name.

Regards,
 
Have a look at the attached - you have to specify the folder you want to save to in the Excel file (cell G1 in the DATA sheet).
 

Attachments

  • EXCEL TO PDF.xlsm
    40.2 KB · Views: 13
Sub Macro1()
'
' Macro1 Macro
'

'Change the directoryname
Range("A1:D33").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\directoryname\Documents\Book1.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


' Base file name
strFileBaseName = Trim(rngNamedCell.Value)
Debug.Print "File Name will contain: " & strFileBaseName



' Loop until we find a free file number
Do
If intFileCounterIndex > 1 Then
' Build test path base on current counter exists.
strTestPath = strSaveDirectory & strFileBaseName & Trim(Str(intFileCounterIndex)) & ".pdf"
Else
' Build test path base just on base name to see if it exists.
strTestPath = strSaveDirectory & strFileBaseName & ".pdf"
End If

If (Dir(strTestPath) = "") Then
' This file path does not currently exist. Use that.
strFileName = strTestPath
Else
' Increase the counter as we have not found a free file yet.
intFileCounterIndex = intFileCounterIndex + 1
End If

Loop Until strFileName <> ""

' Found useable filename
Debug.Print "Free file name: " & strFileName
GetFileName = strFileName


End Sub




I AM NOT THE AUTHOR OF THIS MACRO



.
 
Thank you Sir Bob for your inputs.
i'm getting the following Error "Run Time error '424':Object required"
at the following line:"strFileBaseName = Trim(rngNamedCell.Value)"
Please clarify!!!!
 
nkms143 said:
The VBA is showing "Run Time Error 5" and gets debugging...
Click to expand...
Do you have a valid filepath reference in cell G1 on the DATA sheet?
That causes a different run time error for me, though. I'm not sure why you would get that particular error, sorry, as the code works fine for me.

With SirBob's code, rngNamedCell has not been assigned a value, which is why it's throwing the error.
Try changing the line to

Code: 
 strFileBaseName = trim(sheets("DATA").range("D2").value)

which puts the invoice name in the file name.
 
Thanks all for your valuable suggestions.
@WelshIan Sir, thanks very much. your suggestion i have worked as i expected. further last problem, i would like to hide the invoice sheet (Page_Print)so that the user can get only pdf file to view. The given macro is not working when the invoice sheet is hidden.
Any valuable suggestions will be helpful...
 
I'm glad it's working!
Hide the Page_Print sheet in your workbook.
You have to unhide the sheet at the start of the macro and then hide it at the end. Add this code at the start:
Code: 
Application.ScreenUpdating = False
Sheets("Page_Print").Visible = True
and this at the end:
Code: 
Sheets("Page_Print").Visible = False
Application.ScreenUpdating = True

Application.ScreenUpdating stops the screen flickering when the macro runs.
 
However, the code doesn't work if the Workbook Structure is protected with password after hiding the "Page_Print" Sheet. Any Suggestions.
 
ok.. Problem Solved. I have added Password Protection and Unprotection Codes to work. Now it works fine as i expected.
 
@nkms143 Good to hear you have it working now! I'm very much a novice at VBA, just know how to do some stuff.

Just a warning on turning the protection back on, if your code looks like this:
Code: 
ActiveWorkbook.Protect "password",True,True

The 2nd True fixes the window size, if the workbook opens full screen then users may not be able to change to a smaller view size (or change to full screen from smaller). Leave out the 2nd true and your workbook structure should still be protected - the 1st true protects the structure.
 
I have got it... but one thing is sure, you are damn good in VBA. One Small Request, is it Possible to get footer as the Filename (Executed from VBA) in PDF. I'm trying but not getting. Thanks in Advance.
 
Here's a selection of header/footer code. Just delete the ones you don't need.

I'm not sure which code you are using, mine or SirBob's. With mine, put it in before the code to export as fixed format to pdf. With SirBob's, I'm not sure where you need to put it - you'll need to experiment a little to see where it should go.

Code: 
With Sheets("Page_Print").PageSetup
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = strFileName
        .RightFooter = Filepath & Filename
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
        .CenterHorizontally = True
        .CenterVertically = True
        .PaperSize = xlPaperA4
End With
 
@WelshIan i'm using your code and it worked perfectly as i expected. I deleted all codes except the footer and pasted your strFilename code. it worked perfectly. Thank you very much for your suggestions. It was very valuable and i learnt a lot from your suggestions.
 
You must log in or register to reply here.
Back
Top