PDA

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



frank_m
02-02-2011, 05:11 AM
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
02-02-2011, 07:38 AM
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
02-02-2011, 08:11 AM
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
02-02-2011, 08:14 AM
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
02-02-2011, 08:29 AM
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
02-02-2011, 08:51 AM
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
02-02-2011, 09:05 AM
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
02-02-2011, 09:12 AM
I like using this. It's based on the INI file with sections and values.

'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


If you wanted more control later on, you could just do all the work yourself with:
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

frank_m
02-02-2011, 09:22 AM
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
02-02-2011, 10:02 AM
Hi David,

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


Sub Stump()

...and what up with that? :jester:

Mark Stump

Tinbendr
02-02-2011, 10:51 AM
Wouldn't the OP need to reference Word to use PrivateProfileString?Yeah, I guess I did forget to mention that. :doh:

I was just giving him my Stump version! :rotlaugh:

frank_m
02-02-2011, 04:40 PM
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
02-02-2011, 04:51 PM
I changed
Print #FF, ActiveSheet.Range("A2").Value) To:
Print #FF, Val(ActiveSheet.Range("A2").Value) + 1 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
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

Tinbendr
02-02-2011, 05:45 PM
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
02-02-2011, 06:11 PM
HI David,

Even the greatest of the great :bow: 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.

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 Thanks

Tinbendr
02-02-2011, 06:29 PM
This seems to work.

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

frank_m
02-02-2011, 07:03 PM
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:
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 Thanks a million Man, you're coding has been exceptional. You Mark and Bob have been my three Gods lately :bow: xld I guess though is more accurately a God of the Gods :beerchug:

frank_m
02-05-2011, 10:01 AM
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
02-05-2011, 06:34 PM
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.

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

frank_m
02-05-2011, 07:28 PM
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.

frank_m
02-06-2011, 09:32 AM
I took a stab at this. Seems to work other than if I include (-1) the code below appends every invoice number within the range with -1 whether they are duplicates or not. - If I begin at (-2), then all are handled correctly

- I'm pretty sure that I have something wrong with my implementation of the count if function, but I'm too lame brained to see what

To reiterate, the code I pieced together below, is satisfactory

The only other real issue I see is that checking 15,000 rows is very slow, that's why I limited the range to 2000 rows.
Sub Append_InvNumber()

Dim iCounter As long
Dim x As Long

ActiveSheet.Unprotect

For x = 2000 To 2 Step -1

iCounter = WorksheetFunction.CountIf(Range("A2:A2000"), Cells(x, 1))

If iCounter > 1 Then

Cells(x, 1) = Cells(x, 1) & "-" & iCounter

End If

Next

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, userinterfaceonly:=True

End Sub

frank_m
02-06-2011, 06:02 PM
I haven't been able to get a full understanding for what all of this code should do, and/or how it would work or be called

I realize that I probably am implementing it incorrectly. - I've been attempting to get the next invoice number using the command: Call ReadRandomFile
Option Explicit

Type MyRecordInfo
InvNum As Long
End Type

Sub test()

Dim MyInvoiceNumber As Long

'Starting with a 5 digit# like 23942. the result is always 7 digits. Something like: 4232432
'But if put no number, it uses "1" and each additional call adds "1" as expected

MyInvoiceNumber = ReadRandomFile

MsgBox MyInvoiceNumber

End Sub

Sub CreateRandomFile()

'Running this is not creating any file at all. I created the random .dat file manually to test

'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\RandomFile.dat" _
For Random As #FileNum Len = Len(MyRecord)
' write to the random file
With MyRecord
.InvNum = ActiveSheet.Range("B16").Value
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
Dim NextInvNumber As Long
FileNum = FreeFile ' next free filenumber
MyPath = "C:\Documents and Settings\Owner\My Documents\"

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 '<- I can't figure out what this should do

.InvNum = .InvNum + 1

'I added this next line to try to get the next invoice number into cell A2
ActiveSheet.Range("A2").Value = .InvNum

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

frank_m
02-06-2011, 06:34 PM
Found a typo in the last post:
Range("B16"), should be Range("A2")

But still get the same problem's

Edit: I realize now that the CreateRandomFile procedure is not supposed to create the file..
It's purpose is to set the starting invoice number and to be run only one time. However it is not setting any number all. Even if I hard code a number.

Tinbendr
02-07-2011, 05:50 AM
I don't know why you're not getting a number back from the file. I ran it numerous times here and got a new number each time.

I store the NEXT available invoice number, not the last used.

When the file is opened and the InvNum read, that is the next available number. Then I add one to it and store it.
NextInvNumber = .InvNum
.InvNum = .InvNum + 1


But after rereading everything, this won't help you if the user skips protocall and manually enters a invoice number. This was the closest I could get to fixing that. It will handle one duplicate, but not multiple duplicates.

Sub Macro12()
Dim A As Long

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

For A = InvDiff To 1 Step -1

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

Range("A3").Value = Int(Range("A2").Value - A)
Debug.Print WorksheetFunction.CountIf(Range("A:A"), Range("A2").Value - A)
If WorksheetFunction.CountIf(Range("A:A"), Range("A2").Value - A) > 1 Then
Range("A3").Value = Range("A2").Value - A & "." & _
(WorksheetFunction.CountIf(Range("A:A"), Range("A2").Value - A) - 1)
End If
Next

End Sub

frank_m
02-07-2011, 06:32 AM
HI David,

yeah I don't know. Here it will store numbers 1 thru 10, but larger numbers it usually changes to 7 digits for example 205 manually entered into the file might be saved back to the file as 6325987 after it's incremented.

I even noticed that it some times increments the number from the left instead of the right. For example 23973 might be saved back as 33973

Guess I'll try rebooting my pc, but I sure doubt that will help. Also I'll try it in excel 2003 as right now i'm using a 2007 .xlsm file.

frank_m
02-07-2011, 07:18 AM
Good news, it's working now. The only odd thing at all now is that if I look inside the .dat file all I see is ^]NUL|NUL
It does however output to the next correct number, so all is cool by me.

I'm going to have to trace back everything I've done to see if it really was the .xlsm file or 2007 that was having issues. Or might be able to determine some sort of typo that I made in your code

Anyway, hope that's a relief for you to hear that it's working now. It certainly is for me.

Thank you much for your efforts David

I'm not sure when I'll be attempting to trace down what I had been doing differently before, but I will post back when I do.

As for the count if function that corrects invoice numbers, the version that I have in post# 21, is doing a satisfactory job for me. And that version is appending all duplicates with -2, -3, -4 etc, even if there are several different invoice number duplicates. The original though cannot be appended to become -1 without appending all original invoice number's with -1 (If I understood you correctly when we spoke before, I believe that is also true with your version)

The only issue with my version is that for 15,000 rows its very slow, so I reduced the range to 2,000 rows. that runs in about one second. (15,000 Rows takes about 10 seconds) -- I don't have time right now to test your version, but I will eventually.

Thanks again.

frank_m
02-07-2011, 07:52 AM
Ran it in 2007 after converting back to .xlsm and it still is working. It's looking more and more like it was my own self created delema, as I doubt rebooting took care of it. - I saved all the versions from previous tests, so I believe that I eventually will be able to figure it out.

I feel a little bad about wasting your time with all of what seems to have been faulty testing.

Thanks again.

Tinbendr
02-07-2011, 07:53 AM
The only odd thing at all now is that if I look inside the .dat file all I see is ^]NUL|NUL
Yeah, can't remeber just now what format it saves it in, but you can't read it with something like Notepad.

Glad you got it sorted.

David