LinkBack URL
About LinkBacks
Hello,
I want to be able to open a text file into an 1 dimensional array in excel. I don't want to split it, I just want each row to equal a line of text.
Thanks in advance.
Alex
Hello,
I want to be able to open a text file into an 1 dimensional array in excel. I don't want to split it, I just want each row to equal a line of text.
Thanks in advance.
Alex
Rather than re-invent the wheel, see: https://stackoverflow.com/questions/...n%20an%20array.
Or Google: excel vba to read entire text file into an array for more suggestions.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Getting an error on line
Range("A1").Resize(UBound(Arr) + 1, 1).Value = Application.Transpose(Arr)
that states: Run -time error '1004' " Application -defined or object -defined error
Also, Not sure this is what I am looking for. I want to have each line as it's own "row" in the array. So Arr(0) would equal the first line of text, Arr(1) would equal the second line of text and so on.
Last edited by Alexander40; 05-15-2024 at 03:58 PM.
didn't work
Provide a sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Please Login or Register to view this content.
Last edited by AliGW; 05-21-2024 at 09:48 AM. Reason: Code tags added - please review the forum guidelines.
Please provide a copy of the text file to be imported.
It should work though. Change the path to yours
Please Login or Register to view this content.
Or
Please Login or Register to view this content.
Thanks JEC,
But it doesn't pull in the entire file. There are over 1 million rows in the text file, but when I do a ubound on the ar array it only has 18962 rows.
The ubound should show the correct result. The application.transpose on the other hand, can’t handle so much data. Therefore you need another approach. Can you post it?
You should try to use Power Query
Subroutines to read a complete/entire text file
Change the source file and location as required.
Try this:
Sample workbook and test file to play with. Also contains a query to load the test file.Please Login or Register to view this content.
Found the source for the function.
LIVEJOURNAL
VBA_CORNER
Dated 24 August 2009
https://vba-corner.livejournal.com/5311.html
First sub is doing the same as my suggestions. But the transpose can handle around 36k records or less…
Since here are over a million records, you can’t load it in one column at all.
Last edited by JEC.; 05-23-2024 at 01:49 AM.
Try using longlong for 64 bit
Please Login or Register to view this content.
Last edited by daboho; 05-23-2024 at 10:05 AM.
"Presh Star Who has help you *For Add Reputation!! And mark case as Solve"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks