Excel template access Text file

[Reimer]

Hi again!

I have been requested to help develop an Excel Template (in Excel 2007) that would be used for creating Purchase Requisitions.
Since each requisition needs a unique number, I was thinking I could have a macro that would access a text file to get the last used number and put it in the correct place on the form, then increase the number in the text file by one. The requisitions are printed but not saved. I remember seeing a post about Excel accessing a text file but have not been able to find the post.

If anyone has time, I would appreciate it if you could point me in the right direction.

Thanks

[HansV]

Try something like this; you'll have to substitute a suitable path and filename, and mdify the cell address as needed.

Code: Select all

Sub Test()
  Dim f As Integer
  Dim varLast
  Const strFile = "\\server\share\folder\filename.txt"
  ' Open the file for reading
  f = FreeFile
  Open strFile For Input As #f
  ' Read the number
  Line Input #f, varLast
  ' Close the file (you can't read and write at the same time)
  Close #f
  ' Fill cell
  Range("A1") = varLast
  ' Open the file for writing
  Open strFile For Output As #f
  ' Increase number
  varLast = varLast + 1
  ' Write it to the file
  Print #f, varLast
  ' Close the file
  Close #f
End Sub

[Reimer]

Hans,

Thanks - I am leaving work and will not be able to test unti Monday. I will let you know.

Thanks again for the help and I hope you have a great weekend.

[StuartR]

Chuck,

One little warning. The code that Hans supplied will not synchronize access to the text file from multiple users. This will only work reliably if there is only one person that ever creates purchase requisitions.

[Reimer]

Stuart,

Thanks for the heads up.
That would be a problem. No matter who opens the template, it would need to work to the same. When they run the macro to print the form, it needs to grab the number from the text file and increment the number in the text file -then print the form.
Got any idea how to accomplish this?

[HansV]

If you store the text file in a shared network folder, it should work OK with multiple users, unless two users run the code within the fraction of a second it takes to open the text file, write the increased sequence number and close it. So the probability of a conflict is not zero, but still very small.

[Reimer]

Hans,

Thanks. That was what I was thinking. I have not had a chance yet to test the macro (they are still trying to determine where to put them). I understood the possible chance conflict but it seemed very small. Thanks again.

[StuartR]

You could eliminate the possibility of a conflict by creating a second data file, that you open BEFORE you read the one with the index number and you close AFTER you write the new index number back again.
This second data file would not contain any data, but would simply serve to prevent two different users reading the index file before the first one had written it again.

[Reimer]

Stuart,

Thanks! It seems there is always a way -I just am not able to think of them all...

[Leif]

You could eliminate the possibility of a conflict by creating a second data file, that you open BEFORE you read the one with the index number and you close AFTER you write the new index number back again.
This second data file would not contain any data, but would simply serve to prevent two different users reading the index file before the first one had written it again.

I'm intrigued - could you expand on this?

If we are talking about two users attempting to access the index file at precisely the same time causing a problem, isn't it just as feasible that they would access the second file at the same time?

(We've been using a simple (single) index file for PO numbers for about 8-10 years now without ever striking unlucky. Admittedly, there are only 3 or 4 using the system, a maximum of 20 times in total a day...)

[Reimer]

Leif,

I think Stuart is suggesting the second file as a Lock. The person that accesses first opens the Lock file which stays open until the counter file is used and incremented -then the lock file is closed. That way it would be impossible for the counter file to be accessed between the times of getting the number and incrementing the number.

Hans / Stuart,
The code works GREAT. Now I will look at making the Lock file. It should be easy as I am able to follow what the code is doing.

Thanks so much for the help.

[Don Wells]

Try something like this; you'll have to substitute a suitable path and filename, and mdify the cell address as needed.

Code: Select all

Sub Test()
  Dim f As Integer
  Dim varLast
  Const strFile = "\\server\share\folder\filename.txt"
  ' Open the file for reading
  f = FreeFile
  Open strFile For Input As #f
  ' Read the number
  Line Input #f, varLast
  ' Close the file (you can't read and write at the same time)
  Close #f
  ' Fill cell
  Range("A1") = varLast
  ' Open the file for writing
  Open strFile For Output As #f
  ' Increase number
  varLast = varLast + 1
  ' Write it to the file
  Print #f, varLast
  ' Close the file
  Close #f
End Sub

As a point of clarification; While user A has a given file open for input, is it possible for user B to also open the same file for input?

[HansV]

In the code that I posted, user B can also open the file for input. To prevent the file being opened for either input or output, the line

Open strFile For Input As #f

can be changed to

Open strFile For Input Lock Read Write As #f

Similarly, the line

Open strFile For Output As #f

could be changed to

Open strFile For Output Lock Read Write As #f

[Reimer]

Thanks for all the suggestions (AND code).
I have made a few changes to the Original code and tested it. It apprears to work great. Of course now I find out they want it to be accessed via the Company Intranet. I will look into that later.

Here is the code I have so far:

Code: Select all

Sub PreparePR()
''' Macro by HansV on Eileen's Lounge
''' Post # 30283 on Oct 14, 2010
''' Modified by Reimer

  Dim f As Integer                                              'For Counter File
  Dim L As Integer                                              'For Lock File
  Dim varLast
  Const strFile = "U:\PROJECTS\ChrisHouser\PurReqCounter.txt"
  Const strFileLck = "U:\PROJECTS\ChrisHouser\PurReqLOCK.txt"
  
  On Error GoTo Whoops
  
  ' Open the Lock file
  L = FreeFile
  Open strFileLck For Input Lock Read Write As #L
  
  ' Open the file for reading
  f = FreeFile
  Open strFile For Input Lock Read Write As #f
  ' Read the number
  Line Input #f, varLast
  ' Close the file (you can't read and write at the same time)
  Close #f
  ' Fill cell
  Range("PRno") = varLast
  ' Open the file for writing
  Open strFile For Output Lock Read Write As #f
  ' Increase number
  varLast = varLast + 1
  ' Write it to the file
  Print #f, varLast
  ' Close the file
  Close #f
  ' Close the Lock file
  Close #L
  
  ' Print the Purchase Requistion (user wants 2 copies, so change code below)
  ActiveWindow.SelectedSheets.PrintOut Copies:=1
  
  ' If it is desired to close the workbook with saving changes, remove Rem below
  'ActiveWorkbook.Close Savechanges:=False
  
  Exit Sub

Whoops:
MsgBox ("File is in use, please try again in a minute")

End Sub

[StuartR]

...Of course now I find out they want it to be accessed via the Company Intranet...

Some things are predictable

[Reimer]

You mean like: When you give them what they ASK for, they will realize it is NOT exactly what they want.

[HansV]

As a general rule, "customers" don't have an exact idea of what they want, so finding out is a major part of an IT project...

[Reimer]

Hans -You have that right!

I am finding my self making many many changes. Every now and then I forget to reset the Template so it shows the correct sheet on opening.
When it opens, and the user has macros disabled -the input sheet is hidden and the the user sees a message indicating macros are required and shows the instructions for how to enable macros.
If the user has macros enabled -they are brought right to the input sheet (the macro required sheet is hidden).

I can write code that hides the input sheet and run that every time before I save it as a template, but that requireds me to REMEMBER to run it.
I am looking for help creating code to be in Workbook_BeforeSave sub. I am trying to figure out how to test if it is being saved as a template.
If it is being saved as a template, do one thing, if it is being saved as xlsx do another.
Any ideas?

[HansV]

You could look at the file extension:

Code: Select all

Dim strName As String
Dim strExtension As String
Dim intPos As Integer
strName = ThisWorkbook.Name
intPos = InStrRev(strName, ".")
strExtension = Mid(strName, intPos + 1)
Select Case strExtension
  Case "xls", "xlsx", "xlsm", "xlsb"
    ' This is a workbook
    ...
  Case "xlt", "xltx", "xltm"
    ' This is a template
    ...
  Case Else
    ' Could be an add-in
    ...
End Select

[Reimer]

Many Thanks Hans

When the current fires die out here - I will stick this code in and give it a whirl.