Importing multiple text files into multiple worksheets

I am setting up a spreadsheet to analyze data from a program I use. The files are with extension .npe, but to upload it, I added the .txt.

I have the following worksheets: Input, Run Selection, Summary, and R1 through R20.

On the input tab are the path file locations, starting in C6 and down to C25 (up to 20 files). The paths are from another workbook, and brought in automatically to the Input spreadsheet.

I want to write a VBA code that reads the info in C6-C25, and writes the text from those files into sheets R1-R20. However, there may be 2 files, 13 files, or 20 files.

I have most of the code written, but I am having the hardest time figuring out how to read in each of the files and put them into the individual sheets. I have found code that comes close, but the issue is that the code is really extensive, and I'm not sure I need most of it.

I have attached a sample text file, and the coding I have so far. Any assistance would be appreciated.

Thanks

Please Login or Register  to view this content.

hi,

Welcome to the Forum

Ron De Bruin has a variety of links which should help, including the below which refer to csv or txt files. The principles should be easily adapted for other file types (eg .npe files).
http://www.rondebruin.nl/txtcsv.htm
http://www.rondebruin.nl/csv.htm (check links at base of page too)

I have found code that comes close, but the issue is that the code is really extensive, and I'm not sure I need most of it.

Don't worry about how extensive it is - others may disagree, but I think that if it is from a reputable source, you should be able to learn a lot from stepping through it with the [F8] key &/or selecting a keyword and pressing [F1].
However, keep the words of wisdom in Snb's signature in mind
Once you understand what the code does you can trim it back, so that it meets your requirements & no more, plus you will have learnt something along the way.

hth
Rob

but I don't want to hard code the text file,

Did I ? How flexible it must be ?

Did you consider:

open the first npe file.
open the second npe file / copy the content of the second file into the first one / close the second file
open the third npe file / copy the content of the third file into the first one / close the third file... etc.

hi,

For the first part, I suggest:
1) creating a dynamic named range (DNR) that expands to include the number of rows that have file names in them
2) Creating an Array in VBA that is populated with the contents of the DNR. I'd use a variant array, but that may not be necessary...?
3) Looping through each item (ie file string) in the array.
Here's a rough example:

Please Login or Register  to view this content.

re the multiple delimiters,
I haven't tested this, but can you modify code from a recorded macro?
Ooopps, after actually reading your code, I realise that may not help!

I also want to bring the file in with two seperators (semicolon and comma, but can't figure out where to put the second seperator in the code.

-do you mean a two character separator eg ";,"?
I think you have to modify this

Please Login or Register  to view this content.

- or either of the separators, scattered willynilly through the file?
In which case, you'll need to explain the willynilly logic or, alternatively check for either Separator...

Please Login or Register  to view this content.

btw,
1) I have read (somewhere? ) that in recent versions of VBA, all Integers are internally compiled as Long & therefore I suggest changing some of your variable Typing to Longs.
2) It would be easier (for me, & I'm sure for others too) if you could upload a sample excel file (rather than a text file).


hth
Rob

From another angle, have you tried (recording a macro when) Excel's inbuilt functionality of the Text Import Wizard &/or Text to Columns?

Rob

Originally Posted by broro183

hi,

btw,
1) I have read (somewhere? ) that in recent versions of VBA, all Integers are internally compiled as Long & therefore I suggest changing some of your variable Typing to Longs.
2) It would be easier (for me, & I'm sure for others too) if you could upload a sample excel file (rather than a text file).

It's a proprietary program, so I don't think I can upload it, just try my best to explain.

Originally Posted by broro183

hi,
- or either of the separators, scattered willynilly through the file?
In which case, you'll need to explain the willynilly logic or, alternatively check for either Separator...

Please Login or Register  to view this content.

If you look at the txt file I previously uploaded, each line has a comma and a semicolon that I want to separate into individual columns.

Originally Posted by broro183

From another angle, have you tried (recording a macro when) Excel's inbuilt functionality of the Text Import Wizard &/or Text to Columns?

hi,

Here is some code recorded in Excel 2007 which I think is very close to what you want, by going to Data - "From Text". Perhaps you could modify the recorded code* and add it in a loop structure like my proposal in post # 7.
* I have made no changes to the recorded code therefore there may be some "extra" code. Also, unfortunately, I think this is one of those cases where the actual code recorded differs between Excel 2003 & Excel 2007.

Please Login or Register  to view this content.

If you can't modify this to do what you want, can you please let us know what you get stuck on?

We all work in various proprietary programs, but to get the most effective & contextualised solution, it's your challenge as a Poster* to create a sample file that is structurally the same as your real file but contains no proprietary information (eg it has nonsense data & any other sensitive/indentifiable information has been removed).

*if helpers can't understand, or want to test in an actual document.


hth
Rob

Okay, I'm sorry we weren't of more help but I am pleased you have a solution.

Feel free to post your final solution code, because it may help others who come across this thread when searching for answers. Plus, it may give someone a "better late than never" chance to help you, by improving upon your solution.

If you are happy with the outcome, can you please mark the thread as Solved?


Rob

Here is what I came up with:

Please Login or Register  to view this content.

And the Call TextToColums is:

Please Login or Register  to view this content.

Hope that helps

Ooopps, sorry, I had meant to include a link in my last post.

To mark your thread solved do the following: - Go to the first post - Click edit - Click Advance - Just below the word "Title:" you will see a dropdown with the word No prefix. - Change to Solve - Click Save

source

Rob

Edit: Hey, the code looks good - it won't be long before you don't need any help
End Edit

Originally Posted by broro183

...Plus, it may give someone a "better late than never" chance to help you, by improving upon your solution...

I'm not sure if you guessed that I would try to be the "someone", but I'm giving it a go now...

Edit: I can't improve it much other than to save you the hassle of duplicating the code for each file, by adding in some looping.
End Edit


Here's a teaser, while I finish the looping:

Please Login or Register  to view this content.

I should have the rest up shortly - perhaps after a snack

hth
Rob

try this one out...

Please Login or Register  to view this content.

hth
Rob

Quick comment. I changed the Sep to comma, and then changed the text to columns to column B. What was happening before was that it parses the semi-colon first, putting the value into column 2, and then when it does text to columns (comma separated), it overwrites that column.

That code was great, fabulous, awesome, only two small issues.

1. It keeps asking if you want to replace contents of the destination cells, and you have to click OK for each file. Is there a way to automatically say OK. (Update: after closing and re-opening, it no longer has that message).
2. For some reason it duplicates the last line in the file.

Yah!

Now I'm feeling more helpful

re 1)
I'm pleased that message has gone away & I'm guessing it was tied in with the order of which separator you processed first. I wouldn't have recommended it in this case, but just so you know for other coding, I suggest reading about "application.displayalerts = false" in the VBE Help files.

2) I'm not sure why this is happening?
Did it happen in your original code?
Is the last line in the text file a duplicate of the second last line?
Perhaps, you could try changing your

Please Login or Register  to view this content.

, but I don't think that will help & I think it will error if you have any empty text files.


Now that you have the general looping code (through each file) you could try some of Ron DeBruin's code. I haven't tested this properly, but I think it will be close & you should be able to fix anything I've missed...

Please Login or Register  to view this content.

hth
Rob

Originally Posted by broro183

2) I'm not sure why this is happening?
Did it happen in your original code?

Yes, but when run a second time, it disappeared.
I figured out the issue. The first line does not have a comma, so when I run it first with comma separated, it moves all the results up a line, and then makes a copy of the last line.

Is the last line in the text file a duplicate of the second last line?

No.

Please Login or Register  to view this content.

I noticed there is an apostrophe on the above line, which then doesn't clear info in the R sheets, and then each time the code was run it created another set of files in the sheet (i.e. if you were to run the ProcessNPE 5 times, there would be 5 copies in each sheet). I deleted the apostrophe and it worked great.

Please Login or Register  to view this content.

What is this stop for? When I run the ProcessNPE_v3, it stops there. When I take it out, it runs great.

This new code seems to do the trick, without the first stop.

Thanks a bunch

Joel

hi Joel,

I'm pleased I could help :-)

Sorry about those wee glitches, when I was testing I didn't have twenty "R#" sheets so I commented out that line & forgot to uncomment it. The Stop was also for when I was testing & I forgot to remove it. When testing the macro, the Stop allowed me to press [F5] & the code would run right up to the line & then I could press [F8] to go through the remaining code line by line.

Does the latest code look anything like your original attempts using Ron's examples?
Do you understand what each part is doing?

If you're happy with the solution, can you please mark the thread as Solved?

To mark your thread solved do the following: - Go to the first post - Click edit - Click Advance - Just below the word "Title:" you will see a dropdown with the word No prefix. - Change to Solve - Click Save

Source: http://www.excelforum.com/faq.php

Rob

Originally Posted by new.vbacoder

...Do you know of a thread that discusses transposing data in a vba code without first having to paste it into a worksheet? I have a file that could have more than the 265 columns worth of semi-colon delimited information, so I'd like to be able to transpose it before ever pasting it into excel. If there isn't something you could point me to, then I'll start another thread and ask for some assistance...

Hi Joel,
Can you please start a new thread?
- it keeps things tidier
If you want, you can send me a private message with a link to the new thread & I'll post some links in it.

Rob

Hi Joel,


Are you still using the code you put in post # 23?
If so, I'm not sure why the error is occurring because I think the following line should cause the entire Refresh query table section to be skipped/bypassed if there is nothing in cell C14:

Please Login or Register  to view this content.

hmmm...
I think I may be overlooking something else in the code, but I'm not sure what it is...?

Perhaps one of the suggestions (eg using "Wait") in the below thread will help: http://www.vbforums.com/archive/index.php/t-333048.html

If not, let me know & I'll look again but in more detail. To help investigate, is there any way you could upload the file that is causing the error (after you run the code but w/o including any confidential info)?

Rob

hi,

Yes, I agree it is better to inform the user of any irregularities. Is your message box within the loop?
If so, I'd suggest building/concatenating a string of the file names within the loop & only presenting the message box results once the loop has finished (ie near the end of the code). This approach minimises user interaction, allowing the code to complete faster & still giving the user all the information about the missing files in a single message box.


Rob