Storing information as a text file.

robfo0 · Apr 15th, 2002, 03:45 PM

Hi,
What I am currently using is a sheet in each of my workbooks with about 500 rows, each a different field which has certain information about my customers. The problem is these files are becoming rather large. I have seen the suggestion of storing the data in a text file, then retrieving it... My question is, does anyone have some sample code i could start with? I have no idea how i would write this information to the file, then retrieve it everytime i need it. Would i be able to retreive only certain fields or do i need to import the WHOLE text file everytime i need a piece of information (ie: is there a way to define a "field" using a text file?)
Thanks

Tom Schreiner · Apr 16th, 2002, 01:02 AM

Hi
The easiest way I have figured out to store large ranges is like this:
The following macro will backup the range A1:A500.
If you will be backing up multiple ranges, you will need to keep track of the byte position of each range for restoring your data.
If you need more help, re-post.

Sub ExampleBackUp
Dim BackUpRange
Dim CurrentBytePosition
Dim FilePathAndName
Dim FileNum

FileNum = FreeFile
CurrentBytePosition = 1
FilePathAndName = YourPathHere

Open FilePathAndName For Binary as #FileNum
BackUpRange = sheet1.Range(sheet1.Cells(1, 1), sheet1.Cells(500, 1))
Put #1, CurrentBytePosition, BackUpRange
Close FileNum
End Sub

Tom

[ This Message was edited by: TsTom on 2002-04-16 02:05 ]

robfo0 · Apr 16th, 2002, 03:35 AM

can you give me an example of how i would retreive a certain value...say A250, and put it into a cell when i press a macro button?

btw tom, thanks for the responses to my posts

Tom Schreiner · Apr 16th, 2002, 08:27 AM

Hi
I misunderstood you.
I was assuming that Column A was the "field"
If you are wanting to do a search for a specific record(a row) then this is a clumsy way to do it.
You cannot search this text file as is, but would need to restore it temporarily to, let's say, a sheet called RestoreTemp, and then perform your search off of that sheet. I thought you were just wanting to store info out of your workbook to keep the size down.
I would not store your info as such in this type of file if you need to retrieve specific rows based on a query. Before we move on from here:

1. Will you be editing any of these values which are stored in the text file?
2. What is your search criteria?

If you do not wish to learn a bit about Access and want to stick with a poor man's database, there is a type of file which will do adequately to store, edit, retrieve, ect...

If you'd like, email me a sample of your wb with a more detailed description of what you need.

Probably better off posting more details as well as someone will likely offer you a better solution than I can.

Thanks,
Tom

[ This Message was edited by: TsTom on 2002-04-16 07:28 ]

robfo0 · Apr 16th, 2002, 12:22 PM

1: yes i would be importing the information, possible changing/editing it, then needing to restore it when the file is closed.

2:there wouldnt be a certain "search". This was my plan. I was going to make a workbook with multiple buttons. each button would open a sheet template stored somewhere else. then when the sheet opens, all the necessary values fill in (which are stored in a text file). Then the user can change them, or print the form that just came up.
Then when they are done, another macro reads all the new values back into the text file, and closes the opened sheet to prevent the original file from becoming huge. I did this already on a small scale, but all of my information is stored not in a text file, but another sheet in the workbook, with column B having all the values, and each cell being named for easy reference. But like i said, the file is still quite large. This maybe because the tremendous amount of macro i have do you think? its about 20+ pages when printed (lol)

Please feel free to email me anything, i'd like to take a look at it in any event. my email is: robfo0@hotmail.com

Im really begining to think access will be the ultimate solution, but ive just started to become familiar with excel and vba

and also, ive done a lot of work in excel already

[ This Message was edited by: robfo0 on 2002-04-16 11:25 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 15th, 2002, 03:45 PM	#1
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	Hi, What I am currently using is a sheet in each of my workbooks with about 500 rows, each a different field which has certain information about my customers. The problem is these files are becoming rather large. I have seen the suggestion of storing the data in a text file, then retrieving it... My question is, does anyone have some sample code i could start with? I have no idea how i would write this information to the file, then retrieve it everytime i need it. Would i be able to retreive only certain fields or do i need to import the WHOLE text file everytime i need a piece of information (ie: is there a way to define a "field" using a text file?) Thanks

Apr 16th, 2002, 01:02 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi The easiest way I have figured out to store large ranges is like this: The following macro will backup the range A1:A500. If you will be backing up multiple ranges, you will need to keep track of the byte position of each range for restoring your data. If you need more help, re-post. Sub ExampleBackUp Dim BackUpRange Dim CurrentBytePosition Dim FilePathAndName Dim FileNum FileNum = FreeFile CurrentBytePosition = 1 FilePathAndName = YourPathHere Open FilePathAndName For Binary as #FileNum BackUpRange = sheet1.Range(sheet1.Cells(1, 1), sheet1.Cells(500, 1)) Put #1, CurrentBytePosition, BackUpRange Close FileNum End Sub Tom [ This Message was edited by: TsTom on 2002-04-16 02:05 ]

Apr 16th, 2002, 03:35 AM	#3
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	can you give me an example of how i would retreive a certain value...say A250, and put it into a cell when i press a macro button? btw tom, thanks for the responses to my posts

Apr 16th, 2002, 08:27 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi I misunderstood you. I was assuming that Column A was the "field" If you are wanting to do a search for a specific record(a row) then this is a clumsy way to do it. You cannot search this text file as is, but would need to restore it temporarily to, let's say, a sheet called RestoreTemp, and then perform your search off of that sheet. I thought you were just wanting to store info out of your workbook to keep the size down. I would not store your info as such in this type of file if you need to retrieve specific rows based on a query. Before we move on from here: 1. Will you be editing any of these values which are stored in the text file? 2. What is your search criteria? If you do not wish to learn a bit about Access and want to stick with a poor man's database, there is a type of file which will do adequately to store, edit, retrieve, ect... If you'd like, email me a sample of your wb with a more detailed description of what you need. Probably better off posting more details as well as someone will likely offer you a better solution than I can. Thanks, Tom [ This Message was edited by: TsTom on 2002-04-16 07:28 ]

Apr 16th, 2002, 12:22 PM	#5
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	1: yes i would be importing the information, possible changing/editing it, then needing to restore it when the file is closed. 2:there wouldnt be a certain "search". This was my plan. I was going to make a workbook with multiple buttons. each button would open a sheet template stored somewhere else. then when the sheet opens, all the necessary values fill in (which are stored in a text file). Then the user can change them, or print the form that just came up. Then when they are done, another macro reads all the new values back into the text file, and closes the opened sheet to prevent the original file from becoming huge. I did this already on a small scale, but all of my information is stored not in a text file, but another sheet in the workbook, with column B having all the values, and each cell being named for easy reference. But like i said, the file is still quite large. This maybe because the tremendous amount of macro i have do you think? its about 20+ pages when printed (lol) Please feel free to email me anything, i'd like to take a look at it in any event. my email is: robfo0@hotmail.com Im really begining to think access will be the ultimate solution, but ive just started to become familiar with excel and vba and also, ive done a lot of work in excel already [ This Message was edited by: robfo0 on 2002-04-16 11:25 ]