Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Storing information as a text file.

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •