VBA - Save Invoice Worksheet as PDF - to Specific Folder | Filename Based on Cell Value
Вставка
- Опубліковано 5 лип 2024
- Buy the Simple Sheets Invoice Template (all purchases help support this channel)
www.simplesheets.co/a/2147525...
Download the featured invoice template here
www.bluepecantraining.com/exc...
Full playlist for this video series: • Create an Automated In...
This tutorial is part of a series of tutorials that demonstrate how to create an automated invoice system in Microsoft Excel. This video looks saving the invoice worksheet as a pdf.
Other videos in this series show you how to write VBA macros that
1) Automatically generate the next invoice number
2) Add the invoice details to a record of invoices
3) Save the invoice as an Excel workbook
4) Automatically email a PDF version of the invoice to the customer
Table of Contents:
00:00 - Introduction
01:48 - Declaring the variables for the VBA macro
04:38 - VBA code for saving and Excel worksheet as pdf
06:48 - VBA code to copy invoice details to the invoice tracker and create hyperlink to pdf
10:32 - Create a button to run the macro
------------------------ - Навчання та стиль
I just finished watching all the series and made me understand the whole potential Excel has. GREAT VIDEOS!
I went through this whole series and it was so well explained and easy to implement. It was my first time working with VBA and I was able to understand each of the steps you made, follow along in my own sheet and debug where needed Thanks!
Thanks Chester, a great series well explained. I am new to excel and I basically did everything you said and it worked. I even left things out that I didn't think I´d need and the program still worked. Exactly what I needed. Thanks again.
More VBA invoice project goodness! Thanks Chester!!
You're the man, Chester Tugwell! Thank you!!
Briliant series. Thank you so much. I have never used VBA, but I could follow along and you've helped me so much with the record keeping for our (very) small business. I can't thank you enough.
That's great to hear - all the best with your business!!!
I went through the whole series and everything lived to its billing. Many thanks for making such knowledge available.
Great to hear!
Thank you so so much for this series!! It was extremely useful :)
wo wo,i have actually learnt alot from your videos,i was planning on taking a computer short course at a cafe,but now i know alot that its even helping in planning my company work...thanks alot again\
from zambia
Simple code and it works!! Thank you for the help Chester!!
Thank you sir for your tutorial. Now I can manage my documentation properly. It helps a lot. Thank you sir.
timeless. Thank you!
Thank you very much for these videos!
This is great! Thank you for being soo helpful
Subcribed! Super thanks!
Just wow,,Incredible
Then there at that default email,how to make like two or more lines.thanks
Hi Chester, so helpful. Do you cover how to have the buttons on a separate sheet? I don't want the buttons on the form sheet.
thanks bro, it works,
Great explanation Video. i seem to be struggling with Run-time error 1004 code. wondered if you have experienced this at all and could help? Thanks in advance
Hi, thanks for this.
as far as you know, will I be able to build the same on Libreoffice, as far as VBA and macros go? Thanks
I really enjoy your videos Chester, one question, instead of making 2 different buttons to save as PDF and as new Excel Workbook, can we not just put them both on the same macro ..?
IT'S NICE AND I WANT ONE FULL WORKSHEET AS YOU SHOWED ABOVE 5 OPTIONS INCLUDING CUSTOMERS AND RECORDS OF INVOICE SHEETS
Hello Chester! This is by far the easiest tutorial I have followed and thank you for sharing, you are brilliant!❤ Can I ask if the path for saving the xls and the pdf can also work if the path is located in teams folder instead of my personal folder? In that way anyone who will generate an invoice will get it saved on just 1 specific path. Thank you and happy new year!!! All the best!
As previously mentioned, very good step-by-step instructions. One question I do have is, is it possible to have the records on sheet 3 entered once whilst using 2 or more buttons? So when I save as xlsx and pdf, I only have one row in sheet 3 (for arguments sake) thanks
Thank you for some great videos :D I have made the pdf file name starting with date and then a name, but how do i make that date start with YYYY-MM-DD instead of DD-MM-YYYY?
Hi, love your vidoes! My filename has special characters in it ( - and : ). What macros would you use to eliminate the special characters so that I can save the file?
Your videos are very very helpful, God Bless you!
So now when I remove the shps from the copied form it removes my logo :-( what is the work around?
Hi Chester, thank you for this, I use it monthly. Just a quick question: if my description box and amount is identical across my client database (because the service amount is the same for everyone), how do I create a new macro that allows me to click a button and generate all the invoices at once, in PDF for my entire database? hope that makes sense.
Hello, thank you for this video, is it works in with $ too?
Great advice and I am adapting the codes for my applications at work, however I have hit a run-time error code 424, on the nextrec.offset(0, 1) = tyofwor (tyofwo meaning type of work), I have the same problem in using the code to record to file with the hyperlink.
If I delete that section of the code, the details are recorded on the record minus the hyperlinks to the files, xlsx & pdf
are you able to advise where I might be going wrong?
Were you able to figure this out?
Thanks
Welcome
I want to select a template from a list of templates and fill up the forms accordingly. Also want to attach some photos in the sheet before exporting. Can the code be used in a dynamic way depending upon my fields?? Please make a video on that. This video teaches a lot. Thanks a lot.
Thank you for thiis video series! This is the first time I have used mcaros/VBA and your videos were very easy to follow. I do have one question in regards to saving as .pdf. I have set my invoice to be saved in sharepoint, so the filepath uses my username to get to the desired folder. I would like my coworkers to be able to use the same invoice template and have the invoices they submit go to the same folder. Is there a way to change the code so that any user's invoice will go to the same folder in sharepoint while using the same invoice template?
Hey Chester love your work! Question, I have 7 worksheets (1 is invoice and other 6 are terms and conditions). How can I edit the VBA script to export those sheets and save them in the same pdf?
Very nice tutorial, however what if i'd like to save my pdf inside a custom folder reference to the path. Appreciate your kind response.
If you are having trouble with your logo being deleted use this code:
For Each shp In ActiveSheet.Shapes
If shp.Type msoPicture Then shp.Delete
Next shp
This works perfectly! Thanks.
G'day Chester, I have been following and using your tutorials with great success thank you, I have been asked if I could make the Record of invoices sortable, yep easy I thought just make the sheet into a table, man was I wrong, I have 2 questions 1. Can it be done, and 2. do you have a tutorial on how to do it.
Very nice, clear instructions and I was able to tweak some things for my needs. However, the buttons are copied to a second pdf page. This can be avoided by using "To:=1" (assuming the invoice is a single page). I also used the parameter "OpenAfterPublish:=True" to immediately see the file after it is created.
how to export two or more sheets in PDF by one command with cell value in a specific folder?
Thank you for the great videos, I followed all the steps, changing some cells following my invoice format. It's amazing and will help me in my small business.
I have one concern, when I'm adding to the invoice record, it is saving the same info in 3 different rows:
Row 1- without the xlsx and the pdf files
Row 2- including the xlsx file and without the pdf
Row 3- including the pdf file and without the xlsx
Please how can I make add the invoice record all in the same Row?
Thank you 🙏
did you ever figure this out? As I'd love to know!
if you need to save the same record in 1 line but with 2 different files, which is in excel and PDF, you have to do combine some of code
'save the new workbook to a specified folder
With ActiveWorkbook
.Sheets(1).Name = invno
.SaveAs Filename:=path & fname, FileFormat:=51
.Close
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=path & fname
Set nextrec = Sheet9.Range("A1048576").End(xlUp).Offset(1, 0)
nextrec = invno
nextrec.Offset(0, 1) = idno
nextrec.Offset(0, 2) = custname
nextrec.Offset(0, 3) = amt
nextrec.Offset(0, 4) = dt_issue
nextrec.Offset(0, 5) = dt_issue + term
Sheet9.Hyperlinks.Add anchor:=nextrec.Offset(0, 7), Address:=path & fname & ".pdf"
Sheet9.Hyperlinks.Add anchor:=nextrec.Offset(0, 8), Address:=path & fname & ".xlsx"
@@riisma Thank you for the tips! It works! To point out to everyone, add this code to the previous code (SaveToExcel) by Chester. Enter it right after the line of codes to remove shapes/buttons. Also, do remember to replace the above "Sheet9" to whatever sheet number you need.
any solution for macbook. i have set my export to pdf button for windows, but wont worknon mac. Any propositions or solution?
Hi Chris, pls help. Even though I specify the path, it saves in a different folder.
On the record sheet it displays the correct path as the link details.
Well explained series but two of my commands don't work, one in saving in PDF and the other in saving as excel. For the excel one it export it to new workbook but doesn't save it in the Path, and for the PDF one gives me error #1004, although the spelling and all other points matches with the video, I had to suspend them in order to continue. In both cases invoice and the path and file name add to Record of Invoices. Is anybody knows what the problem might be? Thank you.
Hi Chester! These videos are incredibly useful for me and my company. thank you for that :) I only have 1 problem: when I want to run the "save as PDF" macro. My pdf-file is only being sent to a printer queue, and not to my documents on my computer. Any advice on how to bypass this? I'm working on a macbook I should note.
I'm having the same problem on mac T^T
I have same problem on mac. Do you know how to fix?@@haileylee3236
I just fix it.
The other two codes ran on mac but not this one. Any suggestions?
Evening, First time using excel, VBA or Macros. I am having trouble getting my macro to run. Who can I contact for help getting it to work properly
Hi Chester, another question; just finished Save Invoice Worksheet as PDF and I've copied literally all your code to my VB on my Macintosh. Mine prints the file instead of doing what you showed in your tutorial. Any thoughts?
What if the file path is based on a cell value. Please show what vba code I should use. Thank you.
Is there away to have the hyperlink to the pdf and excel files appear on the same line in the record of invoices instead of a whole new line being created each time? Please help 😅
when I save the invoice to an Excel sheet I have to create the button I'm losing my logo. can I understand why? Thank you so much for this wonderful series.
Is there a way to have both, excel and PDF hyperlinks show on one (the same) line on the record of invoices?
Don’t suppose you figured this out?
Could someone help me with an error I keep getting?
The error occurs when I press the [Save as . xlsx button]
The error says: "(My file name).xlsm'!saveinv'. The Macro may not be available in this workbook or all macros.....
All other buttons work correctly.
Outstanding set of videos. Well organized. Well Timed. Well Presented.
Using the code provided in the video, I attempted this macro. The code below creates a PDF to the desktop but, despite hours of iterations, I cannot get the filename to save as the data from the cell. The filename is always the name of the workbook.
Any help provided would be much appreciated
Sub SaveAsPDF()
Dim wks As Worksheet
Set wks = ActiveSheet
Dim Path As String
Path = "Users\keithfrost\Desktop\"
Dim filename As String
filename = wks.Range("F4").Value
ActiveSheet.ExportAsFixedFormat Type:=TypePDF
End Sub
Kindly tell me the version of excel ur were teaching us
Everything seems to work well until I get to the "activesheet". It continues to ask me to debug, but I'm not sure what I'm doing wrong. Any suggestions?
I have been able to create invoice, and the first two buttons but when it comes to saving the items to excel or pdf files I keep getting an error. Working on a mac it highlights an error with this line: activesheet.Exportasfixedformat Type:=xltypePDF, ignoreprintareas:=False, FileName:=path & fname
Running into the same! Did you happen to figure this out?
Hi, did you manage to find a fix for your issue, I'm experiencing the same issue :)
Is there a way that you can record the invoice saved as excel and invoice saved as PDF on the same record line in the Record Of Invoices worksheet? It seems to creat a new record line for both of these actions for the same invoice number
I'm having the same "issue". Anyone know the answer? (Great videos by the way! I feel so efficient!)
@@TCThompson74 looking at it, i assume the end and offset command look at the pdf/excel column.
The first blank is filled in, which should be the correct one.
So long as the offset is correct, it should work.
I haven't tried it but just my quick thoughts
@@Avvura please can you explain further 😂 I can’t figure this out
@@kanekhan3424 I also want to understand.
@@Avvura Please explain in detail, if you don't mind.
How match debit credit amount with vba
I keep getting "variable not undefined" message for "dt_issue = range{"C3"). I have not recieved this with the previous variable as I have followed your series. How do I address this?
G'day Joan, I am not sure if this will help but I noticed 2 possible problems,(if you copied & pasted the code) there is a " at the beginning of the code, and the first (open) bracket is a { rather than a ( hopefully this won't help because you managed to get it sorted.
Dear Sir, i am trying to save as pdf the same way but facing error 1004, can you please help to resolve it Thank you
I am getting error in this code (ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=path & Fname)
It was working, then it stopped yesterday. What was the fix?
while converting to pdf windows popups "PUBLISHING" i do not want this window to appear please guide
Just add line at the end "OpenAfterPublish:=False" without the quotes
My advice don't follow those videos if you are using Excel 2007, some of the stuff don't work