Jun 30 2021 12:54 PM
Jun 30 2021 12:54 PM
I'm not sure if this formula even exists and when I try and type something into Google to help me find out, I'm not coming up with much.
I use an Excel template to create quotes for my customers and one of the cells has that customer's unique quote number in it. Each completed quote is then saved with that unique quote number as an Excel spreadsheet so that I can easily find the saved documents by quote number.
Is there a way to create a formula that essentially counts up that quote number each time I open the template?
For example: if I completed and saved a quote number 1589 as an Excel Template, I would have typed "1589" in the cell for "Quote Number" and saved my document as "1589". Next time I open up my template, is there a way to have that Quote Number cell auto-populate for "1590" using a formula?
Jun 30 2021 01:50 PM
This needs a bit of VBA.
Step 1: in your default file location for Excel, create a text file named Quote.txt
Open Quote.txt in Notepad and enter the last used quote number, then save and close it.
Step 2: open the template (don't create a new workbook from it).
Press Alt+F11 to activate the Visual Basic Editor.
Double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left.
Copy the following code into the module:
Private Sub Workbook_Open() Dim TextFile As String Dim Quote As String Dim f As Integer f = FreeFile TextFile = Application.DefaultFilePath & "Quote.txt" Open TextFile For Input As #f Line Input #f, Quote Close #1 Quote = Val(Quote) + 1 Open TextFile For Output As #f Print #f, Quote Close #f ' Change the name of the worksheet and the address of the cell ' to the ones where you store the quote number Worksheets("Sheet1").Range("A1").Value = Val(Quote) End Sub
Switch back to Excel.
Save the template as a macro-enabled template (*.xltm)
Use the new xltm template.
Jul 01 2021 05:23 AM
Hans, thanks for your reply. I followed your steps but came up with an error when I open the file:
When I choose to debug, this is what happens next:
Do you have any more insight for me? I have never even attempted to use VBA before.