Solved: Excel, append & retrieve numbers in a txt file with next number in increments one

[frank_m]

Excel 2003

I have a workbook used for creating invoices. Each new row added is always done by way of clicking a button, that both inserts the new row in row 2,(shifting the other rows down), and also calculates the next invoice number in (Column A) of row 2, based on the (Column A) cell from what has now become row 3

What I'd like to do instead of the above, is to calculate the next invoice number to use in the new row 2, based on the last listed number contained within a preexisting text file... And of course at the end of the procedure, append the bottom of the list in the text file with that most current invoice number used.

The text file will reside within the same folder.

Thanks

[Tinbendr]

Seems like a lot of trouble for something you already have working; unless you're having trouble with the current code.

Doing that is relatively simple, but before I suggest code, will this file hold anything else other than the invoice number?

[frank_m]

This is to be used so that two separate workbooks (let's say wb1 and wb2) can always be aware of the next available number.

wb1 is the primary app,,,,, wb2 a secondary app that needs to be able to reserve available invoice numbers,
so that later the reserved numbered rows in wb2 can be one at a time copied > insert row > paste, into the appropriate row/s in wb1

Yes, only a single column will be in the text file.

Also I'd like to delete 100 numbers off the top of the list in the text file, when the line count gets to 200

Thanks for the response

[Tinbendr]

only a single column list of invoice numbers will be in the text file.

One more question. A list of invoice numbers? Wouldn't this just need to be the last number used?

[frank_m]

One more question. A list of invoice numbers? Wouldn't this just need to be the last number used?

Technically you're correct, and it would work with only the one number, but usually quite a few numbers need to be reserved at a time and later I want to expand on the code to add a date and time stamp and user name, placed in the row above, or to the right of each number,,,,,, kind of a log reference for my personal use, giving me the ability to observe what and when, and by whom, rows are being added. (I definitely do not need all those extra bells and whistles right now though)
I would though prefer keeping a list of the last 100 numbers

When the development gets further along, as described above, then obviously the descriptive information will need to be ignored, which I realize complicates things, but I figure that issue can be dealt with at the time the code gets expanded.

[Tinbendr]

I would though prefer keeping a list of the last 100 numbers

The simpiest solution would be to create another workbook with the history you speak of. Everything is handled in one process.

You could purge unwanted records later.

I would tend to create two files. One text file with only the invoice number and one workbook with the history. They can be easily updated, one after the other. The amount of overhead would be minimal.

Which one would you like to presue?

[frank_m]

I like your idea of the history workbook, but the text file will open read write then close a lot faster, as to make for very little chance of the same number being grabbed by both wb1 and wb2.

yes I'm agreeing with you now, the one number in the text file will be perfectly adequate.

* At a later time I will incorporate the history workbook as well.

Thanks

[Tinbendr]

I like using this. It's based on the INI file with sections and values.

[vba]'Write invoice number
System.PrivateProfileString(MyPath & "\LastInv.txt", "InvNumber", _
"LastRecord") = ActiveSheet.Range("A2").Value
'Read invoice number
LastFile = System.PrivateProfileString(MyPath & "\LastInv.txt", _
"InvNumber", "LastRecord")
If LastRecord <> "" Then ActiveSheet.Range("A2").Value = Val(LastFile) + 1[/vba]


If you wanted more control later on, you could just do all the work yourself with:
[vba]Sub Stump()
'Get next invoice number
ActiveSheet.Range("A2").Value = GetNextInvNum
'Save current invoice number
SaveInvNum
End Sub
Function GetNextInvNum() As Long
Dim FF As Long
Dim InvNum As Long
FF = FreeFile
Open "InvNum.txt" For Input As FF
Line Input #FF, InvNum
Close #FF
GetNextInvNum = InvNum
End Function
Sub SaveInvNum()
Dim FF As Long
FF = FreeFile
Open "InvNum.txt" For Output As FF
Print #FF, ActiveSheet.Range("A2").Value
Close #FF
End Sub[/vba]

[frank_m]

Hi David,

I need to go rest now, but the routines look sweet.. I have very little doubt that they will achieve what I need..

I'll have to report back tomorrow.

I sure appreciate you doing all that. And so promptly.

[GTO]

Hi David,

I may have gone blind by now and hope no intrusion... Wouldn't the OP need to reference Word to use PrivateProfileString?

[vba]Sub Stump()[/vba]

...and what up with that?

Mark Stump

[Tinbendr]

Wouldn't the OP need to reference Word to use PrivateProfileString?

Yeah, I guess I did forget to mention that.

I was just giving him my Stump version!

[frank_m]

HI David,

In testing the second method you posted, I created the text file and placed it in the same folder as wb1, and manually entered the number 2350

At first I got a type mismatch error. I then changed Dim InvNum As Long to As Variant and it used the 2350 number, but does not save 2351 back to the text file as the next available number. It just keeps using 2350 with each use.

Am I missing something?

[frank_m]

I changed
[vba]Print #FF, ActiveSheet.Range("A2").Value)[/vba] To:
[vba]Print #FF, Val(ActiveSheet.Range("A2").Value) + 1[/vba] Now the number increment's successfully.
- Are my modification's ok?

Edit: Fine tuning it to be what I actually wanted. Changed the incrementing to when it uses the number rather than when it saves it.

Working well as far as I can tell after limited testing
[vba]Sub InvNumAvailable()
'Get next invoice number
ActiveSheet.Range("A2").Value = GetNextInvNum + 1 ' increment by 1
'Save current invoice number
SaveInvNum
End Sub

Function GetNextInvNum() As Long
Dim FF As Long
Dim InvNum As Variant ' Changed Long to Variant
FF = FreeFile
Open "InvNum.txt" For Input As FF
Line Input #FF, InvNum
Close #FF
GetNextInvNum = InvNum
End Function

Sub SaveInvNum()
Dim FF As Long
FF = FreeFile
Open "InvNum.txt" For Output As FF
Print #FF, ActiveSheet.Range("A2").Value
Close #FF
End Sub[/vba]

[Tinbendr]

Man, I could have swore I put the + 1 in there. Oh, well, glad you got it sorted.

Looks good and it'll be easy to add to it later on.

[frank_m]

HI David,

Even the greatest of the great occasionally forget to add a minor tib bit in their code.

Thanks for your great help with that.

----
I'm thinking that my next question might be best understood if I post it here, rather than in a new thread.

[vba]Sub Macro1()

'After a new row is inserted using the next available invoice number from the text file,
'I need to doing something like I've tried to describe below

If Not Range("A2").Value = Range("A3").Value + 1 Then

'How might I accomplish this ??
'if for example A2 = 204 and A3 = 200 repeat the insert three times

'Edit: Changed Rows("2:2") to Rows("3:3")
Rows("3:3").Insert Shift:=xlDown 'in this case repeat insert three times

'Also fill in the appropriate invoice numbers in the range of Column A cells that were inserted
'with the example above the result should be A2 = 204, A3 = 203, A4= 202, A5 = 201

End If

End Sub[/vba] Thanks

[Tinbendr]

This seems to work.

[VBA]Sub Macro1()
'After a new row is inserted using the next available invoice number from the text file,
'I need to doing something like I've tried to describe below

InvDiff = (Range("A2").Value - Range("A3").Value) - 1
'if for example A2 = 204 and A3 = 200 repeat the insert Row 2 three times
For a = InvDiff To 1 Step -1
Rows("2:2").Copy ' used copy rather than just insert,
'because a plain insert will use the header formatting
Rows("2:2").Insert Shift:=xlDown 'in this case repeat insert three times
Range("a3").Value = Range("A2").Value - a
Next
End Sub
[/VBA]

[frank_m]

HI David, (Edit: sorry David, you probably saw that before editing I accidentally had addressed you as Mark)

Just had to tweak it a little, as I gave you misinformation about copying row 2 and inserting that the appropriate number of times.

I believe it should be row 3 instead, which also makes using a plain insert good

Working version below:
[vba]Sub Macro11()

'After a new row is inserted using the next available invoice number from the text file,

InvDiff = (Range("A2").Value - Range("A3").Value) - 1

'if for example A2 = 204 and A3 = 200 repeat the insert Row 3, three times

For a = InvDiff To 1 Step -1

Rows("3:3").Insert Shift:=xlDown 'in this case repeat insert three times

Range("A3").Value = Range("A2").Value - a

Next

End Sub[/vba] Thanks a million Man, you're coding has been exceptional. You Mark and Bob have been my three Gods lately xld I guess though is more accurately a God of the Gods

[frank_m]

Hi David,

Would you or anyone here, be so kind as to add another feature to the last bit of code?

After the procedure inserts row/s, I need it to scan through the column looking for possible duplicate's.

If an invoice number has been used previously, then append the end of the number with -1. If the number has been used twice previously -2, and so on....

In other words if invoice 2100 has been used three times, the duplicate farthest down in the column would remain 2100, the next one up would become 2100-1, the next one up from there, (if any), would become 2100-2 and so on.

New rows are always supposed to be inserted at the top (row 2),in which case duplicates would never happen, but as you know rules sometimes get broken.

I surely will appreciate it.

Edit: Only the invoice numbers used in the rows inserted by this procedure should be checked for duplicates.

[Tinbendr]

I think you need a different approach.

This will give you a sequential number from a random file. (You can name it whatever you want BTW. Even the extension.)

Since this code locks the file when opened, the other users can't get a new number until the file is closed. Give it a try.

[VBA]Type MyRecordInfo
InvNum As Long
End Type

Sub test()
Dim MyInvoiceNumber As Long

MyInvoiceNumber = ReadRandomFile
MsgBox MyInvoiceNumber

End Sub

Sub CreateRandomFile()
'Run this one time to set starting invoice number
Dim MyRecord As MyRecordInfo
Dim FileNum As Integer

FileNum = FreeFile ' next free filenumber
' create the new file
Open "C:\Documents and Settings\Owner\My Documents\VBA\Misc\RandomFile.dat" _
For Random As #FileNum Len = Len(MyRecord)
' write to the random file
With MyRecord
.InvNum = 2110 'Change to desired starting number
End With
Put #FileNum, , MyRecord ' adds a new record at the end of the file
Close #FileNum ' close the file
End Sub

Function ReadRandomFile() As Long
Dim MyRecord As MyRecordInfo
Dim FileNum As Integer
Dim MyPath As String
Dim lCount As Long

FileNum = FreeFile ' next free filenumber
MyPath = "C:\Documents and Settings\Owner\My Documents\VBA\Misc\"

On Error GoTo ErrorHandler
TryAgain:
' open the existing file
Open MyPath & "RandomFile.dat" For Random Lock Read Write As #FileNum Len = Len(MyRecord)
' read from the random file
Get #FileNum, 1, MyRecord ' reads the next record
' do something with the input
With MyRecord
NextInvNumber = .InvNum
.InvNum = .InvNum + 1
End With
Put #FileNum, 1, MyRecord ' Updates the record
Close #FileNum ' close the file
ReadRandomFile = MyRecord.InvNum
Exit Function

ErrorHandler:
Select Case Err
Case 70
'File access error.
'If another uses tries to open file, this error occurs.
lCount = lCount + 1
If lCount < 10000 Then 'I have no idea how much time this is.
Resume TryAgain
Else
Close #FileNum
MsgBox "File access error"
End If
Case Else
Close #FileNum
MsgBox Err & " " & Err.Description
End Select
End Function
[/VBA]

[frank_m]

HI David,

yeah I see what you mean, but I also need to check for duplicates in case someone decides to break the procedure by inserting and numbering a row manually. That is until I can find out if I can build an workbook specific .xlsm file that removes the ability to manually insert rows.