External Files

[nicole0904]

I have several workbooks that several users each have a personalized
copy of. Each workbook opens the file txt.dat(used a simple name for
testing) and reads in the number and populates cell B3 with that
number. It then increments the number by one deletes the file and
re-creates the file writing the new number.

I need all of the workbooks to reference this file to get a unique
number from 1-99999. Is there a way to do this with out getting
errors?

With this code if 2 users run the macro at the same time it locks the
file.




Code:
--------------------
Sub GetGNumb()
Dim gnum As Long

On Error GoTo ErrHandler1
Open "x:\txt.dat" For Input As #1
Input #1, gnum
Range("B3").Activate
ActiveCell.FormulaR1C1 = gnum
Close #1
gnum = gnum + 1
If gnum > 99999 Then
gnum = 1
End If

On Error GoTo ErrHandler2
Kill "x:\txt.dat"

On Error GoTo ErrHandler3
Open "x:\txt.dat" For Append As #1
Write #1, gnum
Close #1
Exit Sub

ErrHandler1:
MsgBox ("1")
Close #1
Exit Sub
ErrHandler2:
MsgBox ("2")
Close #1
Exit Sub
ErrHandler3:
MsgBox ("3")
Close #1
End Sub

 

[schoujar]

Try the following:


Code:
--------------------
Dim wb As Workbook
Dim number As Integer

If Range("open").Value = False Then
Set wb = Workbooks.Open("temp.xls")
wb.Activate
Sheets(1).Range("isOpen").Value = True
number = Sheets(1).Range("num").Value
Sheets(1).Range("num").Value = number + 1
Sheets(1).Range("isOpen").Value = False
wb.Close saveChanges:=True
MsgBox number
Else
MsgBox "Workbook busy. Try again in a few seconds."
End If
--------------------


where:
* open is the name of a cell in the sheet where this macro will be
fun from.
so if this macro is run from a file abc.xls then this cell should be on
the ACTIVE sheet of abc.xls
* temp.xls is the file where you store your number. It is
recommended you use the full file path here.
* isOpen is the name given to a cell in temp.xls on sheet 1. the
default value of this cell should be 'false'.
* num is the name given to a cell in temp.xls on sheet 1. its where
the number you need is stored.


hope this helps.

 

[nicole0904]

Sheets(1).Range("isOpen").Value = True

Sheets(1).Range("isOpen").Value = False


What is the purpose of these two lines?

I commented them out and I don't see any difference.

There are two small problems though. If I open two workbooks and click
the button one works fine the other one opened the temp and just sat
there. Is there any way to prevent this? This would cause a lot of user
confusion. Also one time it used the same number in both of them.