Reading File and adding contents to spreadsheet
 

I need to read a number (counter) from a file (.txt probably), and add it to
my spreadsheet. Then increment that number by one, and store the result back
into the file. The purpose is so different users can open the spreadsheet
and get the next available number to number the form. Here's how I did it in
Word. How do I do it in Excel?

Public Sub MAIN()
Dim CheckNum$
Dim docnum$
CheckNum$ = WordBasic.[GetFormResult$]("DocNumField")
If CheckNum$ = "00000" Then
docnum$ = WordBasic.[GetPrivateProfileString$]("DocTracker", "DocNum", _
"U:\Counter\TemplateCounter.INI")
docnum$ = Str((WordBasic.Val(docnum$) + 1))
WordBasic.SetFormResult "DocNumField", docnum$
WordBasic.SetPrivateProfileString "DocTracker", "DocNum", docnum$, _
"U:\Counter\TemplateCounter.INI"
ActiveWindow.ActivePane.HorizontalPercentScrolled = 34
End If
End Sub

Reading File and adding contents to spreadsheet
 

On Jun 12, 4:26 pm, KermanPM
wrote:
I need to read a number (counter) from a file (.txt probably), and add it to
my spreadsheet. Then increment that number by one, and store the result back
into the file. The purpose is so different users can open the spreadsheet
and get the next available number to number the form. Here's how I did it in
Word. How do I do it in Excel?

Public Sub MAIN()
Dim CheckNum$
Dim docnum$
CheckNum$ = WordBasic.[GetFormResult$]("DocNumField")
If CheckNum$ = "00000" Then
docnum$ = WordBasic.[GetPrivateProfileString$]("DocTracker", "DocNum", _
"U:\Counter\TemplateCounter.INI")
docnum$ = Str((WordBasic.Val(docnum$) + 1))
WordBasic.SetFormResult "DocNumField", docnum$
WordBasic.SetPrivateProfileString "DocTracker", "DocNum", docnum$, _
"U:\Counter\TemplateCounter.INI"
ActiveWindow.ActivePane.HorizontalPercentScrolled = 34
End If
End Sub

Hello KermanPM,

VBA has 2 functions that are designed to make storage and retrieval of
program information easy and quick. You could have used these
functions in Word program as well. They are GetSetting and
SaveSetting. These functions provide better safety for your data
against loss or corruption since the data is stored in a predefined
Registry location that is exclusively for VBA's use. No need to worry
about permissions. Here is an example...

'This only needs to be run once to initialize the Registry entries
SaveSetting "DocTracker", "DocNum", "Counter"

'This return the Counter value. If it has not been set, it defaults to
1.
Dim Counter As Integer
DocNum =GetSetting("DocTracker", "DocNum", "Counter", "1")

'Increment and Save the new Count
Counter = Counter + 1
SaveSetting "DocTracker", "DocNum" Str(Counter)

Sincerly,
Leith Ross