0

I have a .txt file, Supplier Count.txt and in my excel spreadsheet, each time I run a VBA code I want this file to be opened, to read the number value in my text file, e.g. '21' and then increment it by 1.

So say our text file has one line of text, and this line of text is a number, '21'. the vba code should open the file, read this number and increment it by 1 and replace the text, save it and close the text file. so our value is then '22'

does anyone know how I can do this as I am completely new to vba and so far all ive been able to come up with is the opening the text file and reading the number out as a msgbox

Application.ScreenUpdating = False
On Error GoTo ErrHandler12:
Dim FilePath12 As String
Dim Total12 As String
Dim strLine12 As String
FilePath12 = "\\ServerFilePath\assets\Supplier Count.txt"
Open FilePath12 For Input As #1

While EOF(1) = False
    'read the next line of data in the text file
    Line Input #1, strLine12
    Total12 = Total12 & vbNewLine & strLine12
    'increment the row counter
    i = i + 1
Wend
Close #1
MsgBox Total12
ErrHandler12:

Application.ScreenUpdating = True
Jamie Bull
  • 12,889
  • 15
  • 77
  • 116
kyle ridge
  • 97
  • 1
  • 2
  • 15
  • 1
    "notepad file"? No such thing, really. You have a ".txt" file, which happens to open in notepad, most likely. – Marc B Dec 02 '14 at 15:58
  • Does this text file have only one number in it? Will it ever have more than one line? – Jamie Bull Dec 02 '14 at 16:02
  • it will be one number but may have multiple decimals or may be a single or double digit number etc but only ever one number value yes and will only ever be one line – kyle ridge Dec 02 '14 at 16:05

1 Answers1

0

First include a reference to the FileSystemObject (see https://stackoverflow.com/a/5798392/380384)

Then run this

Private fso As New FileSystemObject

Public Sub IncrCount()
    Dim path As String
    path = fso.BuildPath("\\server\share\folder", "SupplierCount.txt")
    Dim fs As TextStream
    Set fs = fso.OpenTextFile(path, ForReading)
    Dim counter As Long
    counter = CInt(fs.ReadLine())
    fs.Close

    Set fs = fso.OpenTextFile(path, ForWriting, True)
    fs.WriteLine CStr(counter + 1)
    fs.Close
End Sub
Community
  • 1
  • 1
John Alexiou
  • 28,472
  • 11
  • 77
  • 133
  • thanks for your suggestion however this causes an user defined object undefined error. I don't think it likes the Dim fs As TextStream line – kyle ridge Dec 02 '14 at 16:10
  • You _have_ to include a reference to the Microsoft Scripting Runtime (see link in post) to get access to `TextStream` and `FileSystemObject`. – John Alexiou Dec 02 '14 at 16:12