get data from a file, increment and save back to file

why would you do this, opening different formatted files uses different methods to do it, is this for an invoicing system?

You can use registry to keep the number and increment it via code:
Following code will retrieve the stored number in registry and increment it after setting Sheet1 A1 cell.

Open VBA (Alt+F11) and point to Insert->Module to add a new module and copy & paste the following code into this new module. Return Excel and create a command button on the sheet by using Forms toolbar and assign GetID macro to this button. Click on the button to see what's happening.

'----Sample Code Start----
Sub GetID()
    'This module will use the following registry key
    'HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyApp\Auto
    Sheet1.Range("A1").Value = GetSetting("MyApp", "Auto", "AutoNumberID", 1)
    SaveSetting "MyApp", "Auto", "AutoNumberID", Sheet1.Range("A1").Value + 1
End Sub
'----Code End----

You can also use following macro to reset the registry value by assigning another button which says "Reset Number".

'----Sample Code to Reset Value in Reg----
Sub ResetID()
    'This module will use the following registry key to reset its value
    'HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyApp\Auto
    SaveSetting "MyApp", "Auto", "AutoNumberID", 1
End Sub
'----Code End----

I hope this helps.
Suat

More explanation:

I have used:

Sheet1.Range("A1").Value = GetSetting("MyApp", "Auto", "AutoNumberID", 1)

1 parameter means the Default Value. So if your number starts from 1050 (for example) then you can use 1050 as default value. So it will first retrieve the value 1050 and set it to 1051.

According to assumption above better version of ResetID code (this version deletes the AutoNumberID key, so nothing different but I would use this instead setting it to 1 so GetSetting will use the 1050 again instead 1 in our sample).

'----Sample Code to Reset Value in Reg----
Sub ResetID()
   'This module will use the following registry key to reset its value
   'HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyApp\Auto
    On Error Resume Next
    DeleteSetting "MyApp", "Auto", "AutoNumberID"
End Sub
'----Code End----


If  you want to delete the key that you have created for your application:

'-----Delete Registry Key Code-----
Sub DeleteMyApplicationKey()
   'This module will use the following registry key to delete
   'HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MyApp
   On Error Resume Next
   DeleteSetting "MyApp"
End Sub
'-----Code End----

Suat

Yes, it's for an invoicing system.

I can't use the registry method as this invoicing template will be accessed by a number of computers on a network.

How can I use a similar method, but retrieve and update the count from a file on the network instead?

you can find a invoice template in this thread for download maybe it gives you an idea

http://ozgrid.com/forum/viewthread.php?tid=905

Hi,

What business functionality would you like to be invoked when two network users attempt to access the file (Excel, or Text) at the same time?

Do you wish one to be informed that the file is busy and wait, or return back with a error, or something else?

The text file approach is probably the easiest, but of course the file will need to be available to all users on your LAN.

Will you be calling the "routine" from a cell or from another VBA function?

Please clarify then we can offer a better solution.

BFN,

fp.

Fanpages,

Sorry, let me explain...  I've created an invoice form as an Excel Template which can be accessed by a number of computers on a network.  I want the form to be able to automatically increment the invoice number of the form by accessing a number from a separate file and immediately updating it so that if someone else uses the template, they will get the next successive invoice number.  Since it is on a network, the count file that the number is stored in has to be on the network too.  So what I'm looking for is VBA code that will call up the number from maybe a text file on the network, add it to a cell in the invoice form, then immediately increments that number and saves it back to the text file so that the next user who accesses the invoice template will get the incremented invoice number.

I guess it'll be really similar to smozgur's code in the thread above, but instead of using the GetSetting function to access the count from the registry, I'll have to use some sort of "get value" function to retrieve the count from a file on the network.  It seems really simple, but like I said, I'm very new to VB, so I'm not very familiar with what functions and methods are available to me!

PS. Don't forget to change the "h:\invoice.txt" value to the file (and path) where you wish to maintain the invoice number on your LAN.

(But I'm sure you knew that!)

:)

BFN,

fp.

Fanpages,

It's knock off time here, so I'm off to have a drink... will have to look at that code after the weekend!

Take it easy!

d

...still another 8 hours to go here, d.

Have a good one.

BFN,

fp.

Hi again, d,

Hope you had a good weekend.

Have you found the opportunity to try out the code?

Let me know either way.

BFN,

fp.