Excel invoicing spreadsheet

DogsBody

DogsBody

DogsBody

Permabanned
Joined
6 Aug 2010
Posts
941
Location
Ashbourne, Derbyshire
I need some (read quite a bit) of help with a function I would like to perform in excel.

My invoices are currently done via an excel spreadsheet I created, it is very simple and requires everything doing manually, ie order number updating etc.

What I would like to do is add a few function buttons at the bottom to simplify a few tasks, can the following be done and if so could someone point me in the right direction please.

Button 1, "Save"
1 - saves the spreadsheet in .PDF format using current order number as file name
2 - then updates current order number to next sequential number
3 - exports certain fields to another spreadsheet
4 - clears certain fields
5 - closes the spread sheet

Button 2, "Save and print"
Same as above but also prints the invoice out or opens the .PDF file (haven't decided which is neater yet).

Ta
James
 
might be wise if you could knock up a fake invoice for people to look at as I would imagine this like saving as order number would require them to know which cell contains that info.

Not even sure if its all possible but I am sure some vba expert might be able to help :)
 
Just do all the actions yourself while recording a macro, it will write the code for you... then you just add any logic needed and assign that macro to the button.

Edit: Some code for you to get started.
Code: 
Sub savePDF()
  Dim orderNumber
  orderNumber = Range("orderNumber").Value

  ActiveWorkbook.SaveAs Filename:=("Invoice" & orderNumber & ".pdf"), _
    FileFormat:=xlPDF

  Range("orderNumber").Value = orderNumber+1

  ActiveWorkbook.SaveAs Filename:=("Invoice.xls"), _
    FileFormat:=xlExcel8

End Sub

Assuming that your excel document is called Invoice.xls and the cell with the order number is the named range orderNumber. Also, the file format for the second one may not be xlExcel8, as it depends on your version.
 
Last edited:
dasyad said:
Sounds like you actually need a small Access database.
Click to expand...

I'm reasonably handy with Excel and can create macros by recording what I am doing etc, I am absolutely useless with access though.

It would be handy to be able to export my customer details directly from this spreadsheet but not 100% sure how to do that.

Thanks James
 
If VBA is used it can be done.

As previously said, an example of your Invoice would be helpful, along with details on what you'd liked saved to another spreadsheet and how you want it to be saved in that spreadsheet.

Regards
 
Very similar to the previous: -

Code: 
Option Explicit

Sub SaveMe()

Dim intNo As Integer

Dim strPath As String
Dim strFileName As String

    '* Get Current Invoice/Order Number.
    intNo = Cells(7, 9).Value
    
    '* Save as PDF.
    strFileName = "Invoice" & Format(intNo, "00000") & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strFileName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False
    
    '* Increment Number for next use.
    Cells(7, 9).Value = intNo + 1
    
End Sub

Sub SavePrintMe()

Dim intNo As Integer

Dim strPath As String
Dim strFileName As String

    '* Get Current Invoice/Order Number.
    intNo = Cells(7, 9).Value
    
    '* Save as PDF.
    strFileName = "Invoice" & Format(intNo, "00000") & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strFileName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False
    
    '* Print Invoice.
    ActiveSheet.PrintOut
    
    '* Increment Number for next use.
    Cells(7, 9).Value = intNo + 1
    
End Sub

For saving the Customer details, you'll have to give us a bit more info.

Also, what if you need to lookup a previous invoice?

Regards
 
izzop said:
Very similar to the previous: -

Code: 
Option Explicit

Sub SaveMe()

Dim intNo As Integer

Dim strPath As String
Dim strFileName As String

    '* Get Current Invoice/Order Number.
    intNo = Cells(7, 9).Value
    
    '* Save as PDF.
    strFileName = "Invoice" & Format(intNo, "00000") & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strFileName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False
    
    '* Increment Number for next use.
    Cells(7, 9).Value = intNo + 1
    
End Sub

Sub SavePrintMe()

Dim intNo As Integer

Dim strPath As String
Dim strFileName As String

    '* Get Current Invoice/Order Number.
    intNo = Cells(7, 9).Value
    
    '* Save as PDF.
    strFileName = "Invoice" & Format(intNo, "00000") & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strFileName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False
    
    '* Print Invoice.
    ActiveSheet.PrintOut
    
    '* Increment Number for next use.
    Cells(7, 9).Value = intNo + 1
    
End Sub

For saving the Customer details, you'll have to give us a bit more info.

Also, what if you need to lookup a previous invoice?

Regards
Click to expand...



What do you mean what if I need to look up a previous quote ?
I assume you mean how will I find it?
Part of the data I want to export is customer details to a database of some sort and I also need to look into how it wont duplicate customers details as well, then to a seperate sheet the job details, ie invoice number parts cost etc.

Then all I would do is go back to that sheet and lookup the date or the customer name and cross reference it to an invoice number which I could then go and find in the folder that I have told the macro to save the invoices to.


As for providing a copy of my invoice, I was planning in writing it from the ground up so don't have a current version, so if we can work with hypothetical cells for now I would appreciate it.

Thanks
James
 
It sounds to me that you really need an access database over just a spreadsheet.

Or you could go web based with PHP & SQL.
 
AHarvey said:
It sounds to me that you really need an access database over just a spreadsheet.

Or you could go web based with PHP & SQL.
Click to expand...


Maybe I do need that however, access knowledge is probably in the region of 0.5% knowledge of how to operate. PHP & SQL I know they exist and that VBB is built on PHP, and that is the total of my SQL & PHP knowledge.

So any help you can offer with regard to this would be appreciated.

Ta
James
 
You must log in or register to reply here.
Back
Top Bottom