VBA/Excel automated invoicing

**here4help** · 01-29-2014, 05:12 PM

Hi

Im looking to speed up my invoicing process and would like to know if the below is possible through Excel and VB:

I want to put weekly data in a spreadsheet and for it to read this information, generate an invoice which it saves to a PDF and also have some of the information from the invoice copied to a new workbook.

The new work book would be a file to upload the invoice details to Sage so I would be only wanting the info from maybe 4 or 5 cells copied.

How I imagine it:
WORKBOOK
Sheet 1 - Weekly import data
Sheet 2 - Customer
Sheet 3 - Invoice

IMPORT WORKBOOK
Sheet 1 - Invoice details for import

I think the best way to do this would be to have the VB in WORKBOOK Sheet 1 - Weekly import with a button and for it to read across the cells to calculate the information for the invoice, also looking up the company details in Sheet 2 - Customer returning the information on to Sheet 3 - Invoice.

From here I would want the invoice to automatically save as a PDF, titled account_number-invoice_number.pdf

As i would need information from the invoice to import in to sage there would be certain details I would need from the invoice copied to IMPORT WORKBOOK Sheet 1 - Invoice details for import. This would need to populate all the invoice details and move down a cell once it has done one invoice and start the whole process again until it has completed what info its been provided.

This is just an idea kicking round my head and I just wanted to know if this would be possible.

Thanks for taking the time to read and I look forward to you replies

**jmac1947** · 01-29-2014, 06:04 PM

Hello here4help, and welcome to the forums

What you are asking for is not actually all that difficult in concept although without knowing how you get your weekly import data details I am not sure that it would be "end to end" faster than simply doing it all in Sage (although I am not a Sage user so maybe that isn't possible).

I suspect the next step if for your to mock up a sample of the various workbook and worksheets including some sample data.

That would probably be enough to get the creative brains in this forum "on the case"

If you do go this way, please make sure that your requirements have been thought through carefully so that we don't end up producing something beautiful but not what you actually wanted

once again, welcome

**xladept** · 01-29-2014, 06:16 PM

Hi Here4,

Consider a double-click event in lieu of a button and:

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

BTW - We use VBA

And Welcome!

**here4help** · 01-30-2014, 02:35 PM

Hi jmac1947 and xladept

Really appreciate the time you have taken to read and reply my post, I will get the info together and provide examples shortly maybe after the weekend.

Again thanks for the warm welcome and advice