Automated Invoice name

[kmendez]

Hello there, this is my first post so please be patient with me first let me explain what I'm trying to do; I've designed an Invoice template wich I want to:

1- Upon Open :
Auto Increment cells "E5" and "D9" *(Invoice number, and Ref code ) and after doing that , auto-save itself

NOTE: * don't need string name or location paths at this point, just save (overwrite) itself with it's own name in it's own location.


2- Before Save:
Once the invoice is filled, and the user clicks "save" or "save as", excel must sugest both the name and location where the file needs to be saved.

NOTE2: *the invoice name should be a combination of "E5" (Invoice#) and "D12" (Client name).

e. g. Invoice # is 1000001 and client name is Amy Moore,
then the new filename should be "1000001Amy Moore.xls"
and location must be like C:\Documents and Settings\Company name\Invoices\


HERE IS MY VB CODE:

Private Sub Workbook_Open()

[E5] = [E5] + 1

[D9] = [D9] + 1

With ActiveWorkbook
ActiveWorkbook.Save
End With

End Sub
--------------------------------------------------------------------------------------------------------------------------------------
Sub SaveAsNameInCells()

Dim sFileName As String

Const sPath As String = "C:\Documents and Settings\Company name\Invoices\"
On Error Resume Next

With ActiveWorkbook
Sheets(1).Name = sFileName
sFileName = Range("E5").Value & Range("D12").Value
End With

Application.EnableEvents = False
If Len(Dir(sPath)) = 0 Then MkDir sPath
ChDir sPath

'Show the open dialog and pass the selected file name to the String variable "sFileName"
sFileName = Application.GetSaveAsFilename(sFileName, "Excel Files (*.xls), *.xls")

'They have cancelled
If sFileName = "False" Then Exit Sub
ThisWorkbook.SaveAs sFileName
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
exit_handler:
MsgBox "The required cells are empty", vbCritical, "Input required"
Application.EnableEvents = True

End Sub
--------------------------------------------------------------------------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Call SaveAsNameInCells
Application.EnableEvents = True

End Sub


So far the ONLY thing Not working in VB code is auto-save upon open, wich is really important in order to keep the invoice number secuence,
I just want the template to save itself each time "E5" increments, that way every time user opens it the new invoice# its up to date. Help!

 

[GlennUK]

In your workbook open event, wouldn't it be more suitable to reference the ThisWorkBook object, rather than the ActiveWorkbook object. Are you sure that the Workbook open event is firing?

 

[kmendez]

Thank's Glenn,

since i'm a newbie in VB i don't really know what's better, I'm open to any suggestions

 

[kmendez]

Ok I think i know where the problem lies...

The invoice template is in ".xltm" format (excel template with macros)
and "ThisWorkbook.Save" will bring a "save as" window asking me to save the document right after I open it.

Since i have a "Workbook_BeforeSave" sub specifying the file format and location for the new invoices (Sub SaveAsNameInCells), it will only let me save the document as ".xls"


:-> what i really want is that when I open the document, right after E5 and D9 increments, it will save itself in its own location with its own file name and extension whitout asking me.

That way the template will keep invoice number sequence and will remain as a template. Is this posible?

 

[kmendez]

Thank you Glenn,I realise it wasn't working because i have a "Workbook_BeforeSave" Sub that was triggering the "save as" window. It works now, but I had to change the "Workbook_BeforeSave" to "Workbook_BeforeClose" and I don't even know where to start. Let me explain for what I need the code for:

*When attempting to close the workbook excel asks me if i want to save the changes

1-If I hit "No" close the workbook like it would normally do
2-If I hit "Yes" it will call "SaveAsNameInCells" Sub *(see VB code below)
3-If I hit "Cancel" it should just return to the workbook like it would normally do

HERE is the whole code (it's an Invoice template BTW)

Private Sub Workbook_Open()

[E5] = [E5] + 1

Me.Save

End Sub

Sub SaveAsNameInCells()

Dim sFileName As String

Const sPath As String = "C:\Documents and Settings\Invoices\"
On Error Resume Next

With ActiveWorkbook
Sheets(1).Name = sFileName
sFileName = Range("E5").Value & Range("D12").Value
End With

Application.EnableEvents = False
If Len(Dir(sPath)) = 0 Then MkDir sPath
ChDir sPath

'Show the open dialog and pass the selected file name to the String variable "sFileName"
sFileName = Application.GetSaveAsFilename(sFileName, "Excel Files (*.xls), *.xls")

'They have cancelled
If sFileName = "False" Then Exit Sub
ThisWorkbook.SaveAs sFileName
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
exit_handler:
MsgBox "The required cells are empty", vbCritical, "Input required"
Application.EnableEvents = True

End Sub
--------------------- Here is where I need your help-------------------------------

Private Sub Workbook_BeforeClose

?????????????????????


Too complicated?, asking too much? let me know please :wink:

 

[kmendez]

I DID IT!


Private Sub Workbook_Open()

[E5] = [E5] + 1

[D9] = [D9] + 1

Me.Save

End Sub

Sub SaveAsNameInCells()

Dim sFileName As String

Const sPath As String = "C:\Documents and Settings\carolina\Mis documentos\Cuentas\In Models Management\Agencia\Administrativo\Facturacion\Fiscal\"
On Error Resume Next

With ActiveWorkbook
Sheets(1).Name = sFileName
sFileName = Range("E5").Value & Range("D12").Value
End With

Application.EnableEvents = False
If Len(Dir(sPath)) = 0 Then MkDir sPath
ChDir sPath

'Show the open dialog and pass the selected file name to the String variable "sFileName"
sFileName = Application.GetSaveAsFilename(sFileName, "Excel Files (*.xls), *.xls")

'They have cancelled
If sFileName = "False" Then Exit Sub
ThisWorkbook.SaveAs sFileName
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
exit_handler:
MsgBox "The required cells are empty", vbCritical, "Input required"
Application.EnableEvents = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True

Application.DisplayAlerts = False

Dim exit1 As Integer

exit1 = MsgBox(" In Models deseas salvar esta factura?", vbYesNoCancel, "Salir?")
Select Case exit1



Case vbYes
Call SaveAsNameInCells
Exit Sub


Case vbCancel
Cancel = True
Application.EnableEvents = True
Exit Sub

Case vbNo
ThisWorkbook.Close savechanges:=False

End Select

End Sub

*The only thing not working is Case vbNo (workbook not closing)

 

[GlennUK]

Well done!

Did you try putting a breakpoint in to trace what is happening in the Select Case exit1 part of the code? Maybe have a Watch on exit1 so that you can easily see what it contains at that point?