VBA to save page as .pdf, with customer name & invoice no to come from cell

[anil21]

r/sir
Create a print & save button on excel sheet
I am create a tally format retail invoice in excel sheet & print the invoice in pdf format with invoice no & buyers name and save the invoice in d:/invoice folder/?
or auto change the invoice no for create a next invoice or clear the content on (a16 columns to e24 columns) or print a two copyes of the invoice (original for buyer's),(duplicate for seller) buyers name in (a9) columns , invoice no. is (c3) columns
Example for your help:-
buyers name : executive eng. iph is (a9) columns (create a next invoice the buyers name will change )
invoice no : si-2010 ( create a next invoice the invoice no. will change )
invoice saved in d:\invoice folder\ executive eng. iph si-2010 (create a next invoice the invoice are saved in for example d:\invoice folder\arun kumar si-2011)

r/sir
i am find the vba code in your web site to print & save the invoice in pdf format

Sub Button1_Click() Dim wbInv As Workbook Dim wsInv As Worksheet Dim intInv As String Dim strInvMkr As String Dim strTmp As String Dim strMsg As String Dim strSave As Boolean Set wbInv = ThisWorkbook On Error Resume Next With wbInv Set wsInv = wbInv.Sheets("Invoice") End With With wbInv intInv = .Sheets("Invoice").Range("D3") 'This is your invoice number (CORRECTION IN THIS AREA OF THE VBA CODE)) intInv = Mid(intInv, 6, Len(intInv)) ' get the number part from invocie number (CORRECTION IN THIS AREA OF THE VBA CODE ) strInvMkr = "AnilKumar" 'prefix for the pdf (CORRECTION INTHIS AREA OF THE VBA CODE)
End With strPath = "D:\Invoice Folder\" ' file folder strTmp = Dir(strPath) strCurInv = strInvMkr & intInv & ".pdf" 'new pdf file with invoice number and name strTmp = Dir(strPath & strCurInv) ' check the same invoice number exist in the folder If strTmp = "" Then 'if file with same invoice no does not exist strSave = True Else strMsg = "Invoice Number " & intInv & " already exist!" ' if file with same invoice number strMsg = MsgBox(strMsg, vbExclamation + vbYesNo) ' prompt for replace or quit End If If strMsg = vbYes Then strSave = True End If If strSave = True Then wbInv.ActiveSheet.ExportAsFixedFormat xlTypePDF, strPath & strCurInv ' save the pdf file MsgBox "Invoice " & intInv & " PDF saved.", vbInformation End If For x = 1 To 3 With wsInv If x = 1 Then .Range("H1").Value = "Original for Buyer" ElseIf x = 2 Then .Range("H1").Value = "Duplicate for Seller" Else .Range("H1").Value = "Transporter Copy" End If .PrintOut ' print on your default printer (CORECTION HARE INTHIS VBA CODE) '- change this if you need to print it on other than a default printer End With Next wsInv.Range("H1").Value = "" </pre>
End Sub This vba code is working but invoice saved in d:\invoice folder\anil kumar.pdf ( some problem found in this vba 1. Invoice not saved with buyers name & invoice no.) 2. Invoice saved in pdf format with defult vba name anil kumar.pdf 3. No print out in my default printer
Please help us :-
1) invoice saved with buyers name & invoice no.
2) clear the contents (a16 to e24 columes)
3) invoice no auto change
4) print 2 copyes of the invoice ((original for buyer's),(duplicate for seller)
</pre>

 

[Michael M]

Can you please repost using the code tags as descibed in my signature.
It makes it much easier to modify and Debug ...

 

[anil21]

R/sir
Create a print & save button on excel sheet
I am create a tally format retail invoice in excel sheet & print the invoice in pdf format with invoice no & buyers name and save the invoice in d:/invoice folder/?
or auto change the invoice no for create a next invoice or clear the content on (B16 columns to F24 columns) or print a two copyes of the invoice (original for buyer's),(duplicate for seller) buyers name in (B9) columns , invoice no. is (D3) columns
Example for your help:-
buyers name : executive eng. iph is (B9) columns (create a next invoice the buyers name will change )
invoice no : si-2010 ( create a next invoice the invoice no. will change )
invoice saved in d:\invoice folder\ executive eng. iph si-2010 (create a next invoice the invoice are saved in for example d:\invoice folder\arun kumarsi-2011)

R/sir
i am find the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">vba</acronym> code in your web site to print & save the invoice in pdf formatSub Button1_Click() Dim wbInv As Workbook Dim wsInv As Worksheet Dim intInv As String Dim strInvMkr As String Dim strTmp As String Dim strMsg As String Dim strSave As Boolean Set wbInv = ThisWorkbook On Error Resume Next With wbInv Set wsInv = wbInv.Sheets("Invoice") End With With wbInv intInv = .Sheets("Invoice").Range("D3") 'This is your invoice number (CORRECTION IN THIS AREA OF THE <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> CODE ) invoice no intInv = Mid(intInv, 6, Len(intInv)) ' get the number part from invocie number strInvMkr = "AnilKumar" 'prefix for the pdf (CORRECTION IN THIS AREA OF THE <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> CODE ) custmour name will change in create a every invoice End With strPath = "D:\Invoice Folder\" ' file folder strTmp = Dir(strPath) strCurInv = strInvMkr & intInv & ".pdf" 'new pdf file with invoice number and name strTmp = Dir(strPath & strCurInv) ' check the same invoice number exist in the folder If strTmp = "" Then 'if file with same invoice no does not exist strSave = True Else strMsg = "Invoice Number " & intInv & " already exist!" ' if file with same invoice number strMsg = MsgBox(strMsg, vbExclamation + vbYesNo) ' prompt for replace or quit End If If strMsg = vbYes Then strSave = True End If If strSave = True Then wbInv.ActiveSheet.ExportAsFixedFormat xlTypePDF, strPath & strCurInv ' save the pdf file MsgBox "Invoice " & intInv & " PDF saved.", vbInformation End If For x = 1 To 3 With wsInv If x = 1 Then .Range("H1").Value = "Original for Buyer" ElseIf x = 2 Then .Range("H1").Value = "Duplicate for Seller" (CORRECTION IN THIS AREA OF THE <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> CODE ) Else .Range("H1").Value = "Transporter Copy" End If .PrintOut ' print on your default printer '- change this if you need to print it on other than a default printer (CORRECTION IN THIS AREA OF THE <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> CODE ) End With Next wsInv.Range("H1").Value = "" End SubThis <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">vba</acronym> code is working but invoice saved in d:\invoice folder\anil kumar.pdf ( some problem found in this <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">vba</acronym> 1. Invoice not saved with buyers name & invoice no.) 2. Invoice saved in pdf format with defult <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">vba</acronym> name anil kumar.pdf 3. No print out in my default printer
Please help us :-
1) invoice saved with buyers name & invoice no.
2) clear the contents (B16 to F24 columes)
3) invoice no auto change
4) print 2 copies of the invoice ((original for buyer's),(duplicate for seller)

I AM POST THIS VBA CODE TO MANEY WEBSITE BUT NOT REPLY TO ME PLEASE HALP US

 

[anil21]

Please correct this vba code ( please halp me sir )