Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoicing using excel macro
Hello,
I want to use MS Excel for invoicing our customers and I can not figure out how. I need a macro to open three files 1 - Customer database 2 - Services database 3 - Invoicing template, Then another macro will find customer number in services database then look for that customer's details in customers database and copy data from both databases to invoice template then after all the calculations it will print invoice. This process will be continued until all the services provided in a month are invoiced and printed in PDF and emailed to indivudual customers. I know what to do but i don't know how to do it, is there anyone who can help me with this? Many thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoicing using excel macro
Hi
I'd be inclined to have the data in one workbook sheet1-Customers, Sheet2-Services, you can link these on an invoice sheet using Lookups and copy the invoice details values into yet another sheet. Regards Peter "Immaculate" wrote: Hello, I want to use MS Excel for invoicing our customers and I can not figure out how. I need a macro to open three files 1 - Customer database 2 - Services database 3 - Invoicing template, Then another macro will find customer number in services database then look for that customer's details in customers database and copy data from both databases to invoice template then after all the calculations it will print invoice. This process will be continued until all the services provided in a month are invoiced and printed in PDF and emailed to indivudual customers. I know what to do but i don't know how to do it, is there anyone who can help me with this? Many thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoicing using excel macro
On 15 Oct, 16:34, Billy Liddel
wrote: Hi I'd be inclined to have the data in one workbook sheet1-Customers, Sheet2-Services, you can link these on an invoice sheet using Lookups and copy the invoice details values into yet another sheet. Regards Peter "Immaculate" wrote: Hello, I want to use MS Excel for invoicing our customers and I can not figure out how. I need a macro to open three files 1 - Customer database 2 - Services database 3 - Invoicing template, Then another macro will find customer number in services database then look for that customer's details in customers database and copy data from both databases to invoice template then after all the calculations it will print invoice. This process will be continued until all the services provided in a month are invoiced and printed in PDF and emailed to indivudual customers. I know what to do but i don't know how to do it, is there anyone who can help me with this? Many thanks- Hide quoted text - - Show quoted text - Hi Billi I've tried doing this but but i can not automate this process. I have to change customer number and invoice number manually which is time consuming. I was wondering if there is any possibility to make all this process automated. Regards |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoicing using excel macro
There's a multitude of ways in which you can do this. Here's an
example Let's suppose you have a table of customers called CustomerList, and a ServiceList for list of services performed. Your source data for a list of customers can be queried along the lines of, with two parameters of BillingPeriodStartDate and BillingPeriodEndDate: SELECT CustomerList.Name, CustomerList.Address, CustomerList.ID FROM CustomerList WHERE CustomerList.ID IN (SELECT ServiceList.CustomerID FROM ServiceList WHERE ServiceList.ServiceDate BETWEEN BillingPeriodStartDate AND BillingPeriodEndDate) This will give you the list of customers for whom to generate invoices. Then, you can have a procedure to determine which services were performed for each customer, from another query based on the criteria above: SELECT ServiceList.CustomerID, ServiceList.ServiceType, ServiceList.BillableAmount FROM ServiceList WHERE ServiceList.ServiceDate BETWEEN BillingPeriodStartDate AND BillingPeriodEndDate Go through the first query. Open up an invoice template. Fill in customer information from the first query; populate line items for the invoice from the second query (match on ID). Template should have a total amount built-in. Save the resulting spreadsheet in some location - perhaps a folder called "Invoices" with a naming convention for files to reflect service period. Finally, go through and print out each invoice. You might have some message to end user informing him/her to "insert letterhead into printer tray" prior to doing this. On Oct 15, 11:46 am, Immaculate wrote: On 15 Oct, 16:34, Billy Liddel wrote: Hi I'd be inclined to have the data in one workbook sheet1-Customers, Sheet2-Services, you can link these on an invoice sheet using Lookups and copy the invoice details values into yet another sheet. Regards Peter "Immaculate" wrote: Hello, I want to use MS Excel for invoicing our customers and I can not figure out how. I need a macro to open three files 1 - Customer database 2 - Services database 3 - Invoicing template, Then another macro will find customer number in services database then look for that customer's details in customers database and copy data from both databases to invoice template then after all the calculations it will print invoice. This process will be continued until all the services provided in a month are invoiced and printed in PDF and emailed to indivudual customers. I know what to do but i don't know how to do it, is there anyone who can help me with this? Many thanks- Hide quoted text - - Show quoted text - Hi Billi I've tried doing this but but i can not automate this process. I have to change customer number and invoice number manually which is time consuming. I was wondering if there is any possibility to make all this process automated. Regards- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Invoicing with Excel | New Users to Excel | |||
How do I create a unique number for invoicing in excel? | Excel Discussion (Misc queries) | |||
Want Excel 2003 add ins for Car Body Shop Invoicing | Excel Discussion (Misc queries) | |||
need help with invoicing | New Users to Excel | |||
Can I merge Excel spreadsheets with Quickbooks invoicing? | Excel Discussion (Misc queries) |