Saving a file created from an Template

[pjpienaar]

Dear All


First and foremost thank you to all those whom helped with my previous enquiery this site is defnitly the best and friendly.


I am in the process of testing a referred code from “Old Chippy” http://www.mcgimpsey.com/excel/udfs/sequentialnums.html, now I would like to post another question.

Is it possible to apply the above code to a template (create the template), apply the template to create the first workbook e.g. invoice with a invoice number 1 and upon saving the invoice lets say as "Nav Computers", I would like this to be recorded in a record keeping file (XL file)as "Nav Computers" in a column and next to that in the next column number 1 (the invoice number) to read “Nav Computers Invoice 1” this all to be done automatically with only the name "Nav Computers" as the typed save input from the user, so that this file could be used as a report of invoices later on.


Kindest Regards


Pine Pienaar

 

[pjpienaar]

Dear All again


Seven days ago I posted this question above and it seems no one can or want to help with that......any recommendations where/whom I could turn to


Kindest Regards


Pine

 

[SirJB7]

Hi, pjpienaar!


Just a few observations/questions in order to clarify your requirement:


a) The first procedure retrieves a new invoice number from a portion of the Windows registry, adds 1 and stores the new value: for using it you should copy the whole code to the ThisWorkbook section code (Alt-F11 to access VBA project), then save the workbook, and when you open it that code will be executed.


b) The second procedure performs something alike but with the value stored in a text file instead of within the registry: for using it you can refer to the below examples.


c) The difference between those methods is that the first one takes action only at open file time and the second one each time the function is called. It's up to you to define which method is more suitable for your job.


d) About keeping record of invoices, please upload the sample files involved and the opportunity or event or user action that triggers a new number generation. Include all data you want to keep recorded, what is the source, where, how and when the output should be generated.


Regards!

 

[pjpienaar]

SirJB7


Thank you for your prompt responce and the following on your points


I fully understand your comments A, B and C of above as I was testing that and it work.


However the second question are as follows:


Once I apply the template using the code above to create the first workbook e.g. invoice with a invoice number 1 (lets say the number in B2 is 1) and upon saving the invoice (the work book)lets say as "Nav Computers"


I would like this to be recorded in a different record keeping file (different workbook)as "Nav Computers" (the name that the end user type to be saved) in a column number A2 (the invoice number which are taken from the cell B2 of the workbook being saved) combine that with the end user input when saved to read “Nav Computers Invoice 1”


eg. The user type "Nav Computers" when prompted and when the saving takes place simmutaniously save the info in the second work book but as "Nav Computers Invoice 1, where 1 is taken from the cell containing the Invoice number.


This all to be done automatically with only the name "Nav Computers" as the typed save input from the user, so that this file could be used as a report of invoices later on.


I have not created this file but are in a planing stage on paper.


Once again thank you for looking at my post


Kindest Regards


Pine

 

[SirJB7]

Hi, pjpienaar!


Here's the link to the test file for record keeping:

http://dl.dropbox.com/u/60558749/Saving%20a%20file%20created%20from%20an%20Template%20-%20Keeping%20Record%20%28for%20pjpienaar%20at%20chandoo.org%29.xlsx


The modified code for the procedure for AutoOpen (which I assume you have modified or you will modify to ask for the filename to be saved) is this:


-----

[pre]

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
'SirJB7 add - start
Const sRECORDINGFILE = "C:PathFilename.xls"
'SirJB7 add - end
Dim nNumber As Long
'SirJB7 add - start
Dim I As Long, sName As String
'SirJB7 add - end

With ThisWorkbook.Sheets("Invoice")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
'SirJB7 add - start
sName = ThisWorkbook.FullName
On Error Resume Next
Workbooks.Open sRECORDINGFILE
If Err.Number <> 0 Then
MsgBox "Error opening recording file " & sRECORDINGFILE, _
vbApplicationModal + vbCritical + vbExclamation + vbOKOnly, _
"Warning!"
Err.Clear
Exit Sub
End If
On Error GoTo 0
I = ActiveSheet.Cells(1, 1).End(xlDown).End(xlDown).End(xlUp).Row + 1
ActiveSheet.Cells(I, 1).Value = nNumber
ActiveSheet.Cells(I, 2).Value = sName
ActiveWorkbook.Close True
'SirJB7 add - end
End If
End With
End With
End Sub

[/pre]
-----


Change value for constant sRECORDINGFILE to the real workbook.


Hope it helps.


Regards!

 

[pjpienaar]

SIRJB7


First and foremost thank you for all the help, I am not an expert with Excel and therefore very thankfull for your help.


I will test it over the weekend and let you know.


Kindest Regards


Pine

 

[SirJB7]

Hi, pjpienaar!

Glad to help you. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!

 

[Montrey]

Nice one Sirjb7

 

[SirJB7]

@Montrey

Hi, PEEG!

Thank you.

Regards!