Modify code to input .txt files

[daz18983]

Hi

I have an excel worksheet which has the following the code/macro assigned to a button:

Please Login or Register  to view this content.

At the moment the code allows you to select multiple .txt files and then creates a new workbook and makes multiple worksheets from the text files in the new workbook. What I'm trying to do is alter the code so that the text files are imported into the same workbook as the worksheet with the button rather than a completely new workbook. I do understand VB to a certain degree but the code above has been created by someone else and i dont quite understand the code fully to alter it. Any help would be greatly appreciated.

Thanks in advance

[foxguy]

Hi daz18983;
Change the part between the '*************************

Please Login or Register  to view this content.

[daz18983]

thanks foxguy. 99% there.....

There is one slight issue. For some reason no matter which text files I input, the first text file seems to head into its own workbook where the remaining text files actually do what I require and imports into the same workbook as the sheet with the button.

If I only select 1 text file to input this heads into its own workbook. If I select 2 text files to import, the first one heads into its own workbook and the second goes where I want it?

Strange? Any suggestions?

thanks

[snb]

Please Login or Register  to view this content.

[foxguy]

Hi daz18983;

It sounds like you didn't remove the old lines. I put an ['] in front of the old lines that you did not want to execute. I left the old code there so you could study them to see what they did compared with what I did.

I didn't study snb's macro, but it looks like it will work just as well.

[daz18983]

thanks for the replies guys.

Foxguy, I assume that was what you mean is to replace all the code marked by ['] with the Set wkbAll = ThisWorkbook. Well I have now just done this and I get the error "no data was selected to parse". However if I keep all that code in and just insert the Set wkbAll = ThisWorkbook at the end that is where i get the new workbook for only one of the files.

As for macro provided by snb, this does not seem to work either. I can only select 1 text file at a time and If I run the code I get the error "run-time error '13': Type Mismatch?

Thanks for all your help on this guys, I am trying to figure this out myself Its just some of this is well above my VB expertise

[foxguy]

Hi daz18983;

The "No Data was selected to parse" error occurs on the line:

Please Login or Register  to view this content.

Replace it with:

Please Login or Register  to view this content.

At least it should copy the data into the worksheet, and you can see why it didn't "TextToColumns"

[daz18983]

Foxguy, once again. Nearly There.... We now get all the tabs to be imported into the same workbook but it doesnt actually import any of the data into the worksheet?

[foxguy]

Hi daz18983;

I didn't even read the old macro. I just assumed I knew what it was doing.
It's actually moving the text file into the workbook, then it's performing TextToColumns
When it creates a new workbook, the new sheet # is x. But when it's moving the text file into ThisWorkbook the next sheet is not x, it's .Sheets.Count so:

Please Login or Register  to view this content.

I would still on "On Error Resume Next" before it and "On Error GoTo ErrHandler" after it. At least then you should get the file into ThisWorkbook even if it's not broken into columns.

[snb]

It needs only a slight modification:

Please Login or Register  to view this content.