Import a text file by connecting. Select a file by manual.

**Hakktu** · 10-29-2012, 08:23 AM

Hi,

I try to Import a text file by connecting it. Everything working fine. When I open a Excel file it automatically update the excel sheet's from Txt file. I have 5 different txt file and data goes different excel sheets.
But now the problem TXT file name is not always be the same. So I need to select manualy the correct file.
Problem is that I cannot know which txt file I should select, because Excel does not tell it to me. There is some small changes in txt file name, but data have to goes to right sheet.

Any ideas?

T

**Hakktu** · 10-29-2012, 08:28 AM

Excel version 2010.

**abousetta** · 10-29-2012, 08:37 AM

I am guessing you have a macro that does this for you. So step 1 is for you to show us the code. Step 2 would be to tell us how the name changes. You can use things like the LIKE function or a series of Select Case... Case... End Select if you have a list of know possible variants, etc. Without more info we can't really help you.

abousetta

**Hakktu** · 10-29-2012, 08:51 AM

Hi,
On the Data tab, in the Get External Data group, click From Text.
I don't have macro in use at the moment.

.TXT files can be like this:
1234567_1212_R.txt
And next time the same file can be:
7654321_1547_R.txt
But this same txt file should be at same excel sheet for every time.

There is always something similar like "R" in my example.

**MrShorty** · 10-29-2012, 09:37 AM

If I understand your question, I have two thoughts on changing the file imported by the Get External data command (both assume that you have remember query definition checked in the data range options)

1) If you have "prompt for file name on refresh checked," then Excel should prompt you for the filename each time you tell it to update the data range.
2) If you don't have "prompt for file name on refresh" checked, then you can select the "edit data range" command where you should be given the option to select a new file for the import.

If I've misunderstood and you are trying to automate this process, you will probably need a macro, and probably the easiest place to start is recording a macro while you use one of the above methods to change the filename, then use the recorded macro as a starting point.

**Hakktu** · 10-30-2012, 02:53 AM

Hi MrShorty,
Yes you understand it right.
I have "prompt for file name on refresh" checked, but when excel popup the selection window, there is no info which txt file I need to select.
Mayby I have to create a macro, it could be easyer than this default process.

In point 2) you mention "Edit data renge command" where can I select that ? I can't find a correct place to set it up.

T

**Hakktu** · 10-30-2012, 06:30 AM

Macro created and now my problem is solved.
Thanks.

**abousetta** · 10-30-2012, 07:14 AM

Glad you got it all working. If you satisfied with the results then please mark the thread as solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Thanks.

abousetta