Results 1 to 9 of 9
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Copy file to Array for Searching (VBA/Excel/XP)

    As files are changed in our application, we append the current values to a text log on close so it can be loaded to the mainframe overnight.

    I have discovered that the mainframe app (SAP) only reads the first occurrence of a key and discards later changes.

    What I want to do is write the current file to an Array, search and replace if needed in the array, and then write out the new text file. It has been a long time since I worked with large arrays (14 columns wide by rows) and need help.

    Sound plausible?? Thanx
    Alan

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Copy file to Array for Searching (VBA/Excel/XP)

    Instead of putting all 14 columns in your array, how about just storing the row number and then using the array as an index back into your worksheet?

    You also could consider using the Scripting.Dictionary object. I'm trying to find the sample code I posted for this.... aha! see <post#=286092>post 286092</post#>.

  3. #3
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Copy file to Array for Searching (VBA/Excel/XP)

    It is not in a worksheet. It has previously been written to a space-delimited text file. I need a way to search the text file for a particular set of keys (the first 2 fields), delete that row, and replace or append with the new data.
    Alan

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copy file to Array for Searching (VBA/Excel/XP)

    Rory and jan karel have some ideas that may be helpful in <!post=this,272948>this<!/post> thread. You just need to write the loops.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Copy file to Array for Searching (VBA/Excel/XP)

    Sounds like a project for ADO, but I don't have any experience working with fixed-length fields in ADO.

  6. #6
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Copy file to Array for Searching (VBA/Excel/XP)

    Apparently no one else has done ADO for fixed length either.

    This project has pushed my skills to the limit. I wish they had let me do it all in Access instead of creating 1300 individual spreadsheets. Alas, the customer is ALWAYS right!
    Alan

  7. #7
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy file to Array for Searching (VBA/Excel/XP)

    I'm a little confused at what you are trying to do. Let me see if what I think you're doing is correct.

    You are writing out a log, which is space delimited. You want to 'search' that log, so that you can replace certain values, and then write the log back out?

    Okay, what I recommend is building a Class to hold each record. Then read the log file and split it by vbCrLf, and then split the resulting array by Space. Then fill a collection with the various 'records', using the key's as the Key in the collection. You now have an easily retrievable Class object, to change values of. You could add a 'value changed' property to the class, so that you can also determine what was changed or not.

    If you could send a copy of your 'log', with a little description, I could whip up an example of what I am talking about.

  8. #8
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Copy file to Array for Searching (VBA/Excel/XP)

    We did something totally different in the end. The customer decided we should write to a CSV file. Instead of a push systm, we poll all 1200 spreadsheets and write new CSVs and TXT files each night.
    Alan

  9. #9
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy file to Array for Searching (VBA/Excel/XP)

    Guess I was a day late, and a dollar short! <grin>

Posting Permissions

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