Import Text File From Specific Folder

  • I have an Excel workbook with 2 worksheets in it. One of the worksheets imports a txt file and then the other worksheet displays data that formulas I have written have collected.


    I usually import the txt file by running through the many steps with the "import data" option. This is good but I want it to be faster.


    I used the macro recorder and ended up with the following code



    This code is great because it has all the settings that I always use when importing the txt file. The only thing that will need to change is the target file often and sometimes even use this excel workbook on different computers.


    Code
    "TEXT;\\Desktop\Import Folder\test.txt" _


    Would there be a way to write a macro that when run would prompt the user to select the folder where the txt file will be located? Maybe assign the folders location to a variable that could then be inserted into the previously mentioned code?


    Cheers,
    Dave[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I searched the forums a bit more to try and peice some other codes together.


    I found this tread that takes about a modual that creats a function that graps the name of the target folder
    http://www.ozgrid.com/forum/showthread.php?t=68703


    Pulling some things from there i was able to make the following code.



    So Any VB people out there that can help me use the right syntax to get that "target" string variable into that script.


    Thanks,
    Dave



    SOLUTION FOUND!!!!!


    After some more searching i found this Post,
    http://www.ozgrid.com/forum/showthread.php?t=55830


    Norie has comented on the correct syntax.

    Code
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "TEXT;" & Target _


    Thanks,
    Dave

  • Re: Import Text File From Specific Folder


    I have read both those guides many times. I read them while I was posting my first post because like many other people on this forum I have seen how much of an enforcer you can be. Its great, having a strong admin keeps the spammers off the boards and the forums functioning better.


    Would you explain in more words why your title "Import Text File From Specific Folder" is better than my original of "Targeting A Folder". If I understand why you thought mine was selfish I can take more care in future postings.


    BTW, Before I post I do search the forums heavly for my answer. It was the automatic search after I had posted that made me stuble apon the "http://www.ozgrid.com/forum/showthread.php?t=68703"" tread, through which i was able to code my own answer.


    Cheers,
    DAVE

  • Re: Import Text File From Specific Folder


    Importing a Text file is the main goal yes, but targeting a folder was the main quandary and can be used for many more uses too. I know one should never ask to what he thinks the answer is, but i thought this would work and be a pretty cool trick to learn.


    Dave

  • Re: Import Text File From Specific Folder


    But "targeting a folder" is of no use to anyone but yourself. Where-as "
    Import Text File From Specific Folder " helps you AND your fellow members.


    As you SHOULD know, your thread should help 1000's of others, not ONLY yourself.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!