Excel template access Text file
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Excel template access Text file
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
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
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel template access Text file
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Excel template access Text file
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.
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.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 12629
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Excel template access Text file
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.
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.
StuartR
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Excel template access Text file
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?
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?
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel template access Text file
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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Excel template access Text file
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.
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.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 12629
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Excel template access Text file
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.
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.
StuartR
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Excel template access Text file
Stuart,
Thanks! It seems there is always a way -I just am not able to think of them all...
Thanks! It seems there is always a way -I just am not able to think of them all...
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 7218
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Excel template access Text file
I'm intrigued - could you expand on this?StuartR wrote: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.
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...)
Leif
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Excel template access Text file
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.
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.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Excel template access Text file
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 wrote: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
Regards
Don
Don
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel template access Text file
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
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Excel template access Text file
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:
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
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 12629
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Excel template access Text file
Some things are predictableReimer wrote:...Of course now I find out they want it to be accessed via the Company Intranet...
StuartR
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Excel template access Text file
You mean like: When you give them what they ASK for, they will realize it is NOT exactly what they want.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel template access Text file
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...
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Excel template access Text file
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?
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?
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel template access Text file
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 233
- Joined: 10 Feb 2010, 19:17
Re: Excel template access Text file
Many Thanks Hans
When the current fires die out here - I will stick this code in and give it a whirl.
Chuck Reimer
(I'm from the Government and I'm here to help) ;-)
(I'm from the Government and I'm here to help) ;-)