PDA

View Full Version : VBA Excel increase invoice number and save (need help with code) Excel 2003



Galdorf
07-06-2016, 10:17 AM
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub

Sub NextInvoice()
Sheets("InvoiceB").Range("F5").Value = Sheets("InvoiceB").Range("F5").Value + 1
'Range(“A20:E39”).ClearContents
End Sub

Sub SaveInvWithNewName()
Dim NewFN As Variant
ActiveSheet.Copy
NewFN = “C:aaaInv” & Range(“F5”).Value & “.xls” '<-get compile error here not sure why?
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

mdmackillop
07-06-2016, 10:35 AM
Your quotation marks are the "wrong type"; just replace them. Also, I think your missing "\" here. "C:aaaInv"

Galdorf
07-06-2016, 12:30 PM
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub

Sub NextInvoice()
Sheets("InvoiceB").Range("F5").Value = Sheets("InvoiceB").Range("F5").Value + 1
End Sub

Sub SaveInvWithNewName()
Dim NewFN As Variant
ActiveSheet.Copy
NewFN = "C:\invoices\Inv" & Range("F5").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub



Get error 400 not sure why my guess is 2003 can't save that type of format , does 2003 have non macro save format?

mdmackillop
07-06-2016, 01:13 PM
does 2003 have non macro save format?
No, Just save as xls.

Galdorf
07-06-2016, 01:51 PM
does 2003 have non macro save format?
No, Just save as xls.

so

Sub SaveInvWithNewName()
Dim NewFN As Variant
ActiveSheet.Copy
NewFN = "C:\invoices\Inv" & Range("F5").Value & ".xls"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close
NextInvoice
End Sub


should work?

mdmackillop
07-06-2016, 02:01 PM
Looks OK, I don't believe the FileFormat is necessary.

Does the code need to work for more than one person? If so, you should look at saving the Invoice number in a separate file.

Galdorf
07-07-2016, 07:47 AM
Looks OK, I don't believe the FileFormat is necessary.

Does the code need to work for more than one person? If so, you should look at saving the Invoice number in a separate file.

Only going to be used by one person although the file i save should be read only not sure how to do that in excel 2003 i should also make function to print 2 copies of invoice as well not sure how to do that.