+ Reply to Thread
Page 5 of 10 FirstFirst ... 3 4 5 6 7 ... LastLast
Results 61 to 75 of 146

What concerns should i have reading in a text file using VBA

  1. #61
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    22,908

    Re: What concerns should i have reading in a text file using VBA

    Hello welchs101,

    4GB is the minimum needed to run Windows 7. You really need 8GB to run other programs and Office without problems.

    The only alternative is "chunk" the file. That is bring it in smaller pieces.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  2. #62
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    in case anyone was interested: The data i collect in the dictionary is shown below in the code. Of course the program never gets to this procedure but i wanted to share what i was putting into the dictionary incase someone had some ideas on how to implement this with the 1.3million rows. There are some other procedures i woudl need to modify but this is the big one.

    Please Login or Register  to view this content.

  3. #63
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    ok, read it in smaller chunks.....ok

    but i had a thought.

    What i really need from the 19 cols of data are 7 cols of data that i actually use which are

    Cntry
    SiteNum
    SubjNum
    VisCol
    Formcol
    FmStrt
    S_StatucCol

    Is there a way to create 7 arrays from the data for each of the cols above?

  4. #64
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    any ideas?

  5. #65
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,324

    Re: What concerns should i have reading in a text file using VBA

    Hi welchs101,
    Are you getting an 'out of memory' message with the code from post #55 (+#57)? It works for me after restarting Excel only (win7, 4Gb ram)

    And also try this
    Please Login or Register  to view this content.

  6. #66
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    hi nilem,

    the code in post #55 and #57. No, it does not work for me when i try and run the 1.3million row file. Note: The file i posted on line here was only ~94000 rows.......i could not post the big file as the file manager said the file was too big. To get the 1.3million row file take the 94k file i posted and copy/paste it 13 times in notepad and then save it.

    Regarding your most recent post code: I am trying to get this to run but am having issues. I think my problem has something to do with the "thisworkbook.path" variable but i am not sure.

  7. #67
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    nilem,

    initially i was not able to get your latest code to work. then i realized that i was using an input file which did not have the correct "column header values". So i created a test input file with 1.3million rows with the correct header values and IT WORKED........well it ran to completion at least. So this is a good step in the right direction. So i thought perhaps the same input file issue was what was causing your code posted in #55 and #57 to not work so i re-ran this code using the same input file i just created............i still get an out of memory error at this step
    Please Login or Register  to view this content.
    good news is i can get your latest code to work.........just now need to figure out how to implement this..........

    why do you think your latest code worked but not the one in #55 and #57

  8. #68
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    nilem,

    is there a way using your latest code to "verify" the column header values are in the file and then do the importing of the data?

  9. #69
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,324

    Re: What concerns should i have reading in a text file using VBA

    Perhaps you know the column numbers (starting with 0), for example 0, 1, 2, 6, 9, 11, 17
    then try
    Please Login or Register  to view this content.

  10. #70
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    nilem,

    thanks......i will take a look at this later today. i have family duty now.

  11. #71
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    i did have one question concerning the setting of the reference.

    Is there a way to set this reference in the vba code itself? For example, when using dictionaries i use the following

    Please Login or Register  to view this content.
    in the vba code instead of setting a preference.

  12. #72
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,324

    Re: What concerns should i have reading in a text file using VBA

    you can use reference Tools -> References -> MS Scripting Runtime
    and use variable like this
    Please Login or Register  to view this content.
    it is called 'early binding' and the macro will run a bit faster in this case
    Last edited by nilem; 01-10-2015 at 12:47 PM.

  13. #73
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    nilem, thanks. i wasnt actually referring to the dictionary i was asking the question about

    'with using reference to the ActiveX Data Objects library:
    'Tools -> References -> MicroSoft ActiveX Data Objects 6.1 Library


    how to do this in the vba code instead of having someone have to set their references in vba..........ie......if i give this macro to someone wont they have to set their references inorder for it to run correctly? or is the reference set in the file itself?

    thanks again for your help.

  14. #74
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,324

    Re: What concerns should i have reading in a text file using VBA

    Hi welchs101,
    try this
    Please Login or Register  to view this content.

  15. #75
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,264

    Re: What concerns should i have reading in a text file using VBA

    hi nilem,

    thanks. that one worked as well and you dont need to go into references and check " MicroSoft ActiveX Data Objects 6.1 Library". Thanks.

+ Reply to Thread
Page 5 of 10 FirstFirst ... 3 4 5 6 7 ... LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1