what do I type to specify to import from the folder that I just browsed for?

I have inserted the code above to my macro. Instead of '& ThisWorkbook.Path', what do I type to specify to import from the folder that I just browsed for?

PHP Code: 

 With Worksheets("Info").QueryTables.Add(Connection:= _
        "TEXT;" & ThisWorkbook.Path & "\messag.", Destination _
        :=Worksheets("Info").Range("A1")) 


THANK YOU.

Hi

If you have just browsed for a folder in excel, and don't specify a path, then it will try to open from the current position. So you should be able to remove the workbook.path component.

Please Login or Register  to view this content.

rylo

Hi! Thank you for your reply.

Unfortunately, excel gives error message "run-time error 1004, excel cannot find the text file to refresh this external data range". Here is what I am trying to do:
- import text data files from two different folders
- the text files from the two folders have the same names

My sequence of thought is to browse for folder one, import the text from there. Then browse for folder2, and import text from there to different sheet. Here is what I have so far:

Please Login or Register  to view this content.

I am guessing the error comes from the following not correctly specified, any suggestions?

Please Login or Register  to view this content.

Hi

Try putting the suffix onto the file name

Please Login or Register  to view this content.

should perhaps be

Please Login or Register  to view this content.

rylo

Hi, thanks for the reply.

The file names do not have suffixes themselves. Excel was able to import the no suffix files when the location specified was the current workbook directory with "& ThisWorkbook.Path &". But since I am trying to import files from two different directories, that no longer works...

Thanks! Please advise!

HI! Ideally, the code should allow me to browse for the folder I want to import data files from, then repeat. However, even though the files are in the specified folder, it doesn't load the files.

Full code in the original message. The 'browsing for a folder code' I copied from post http://www.excelforum.com/showthread.php?t=650159. Please advise!

Please Login or Register  to view this content.

Hi

How about using the find file dialog

Please Login or Register  to view this content.

The first line will open the find file dialog and allow you to search for and select the file to open. The result is a boolean that, if a file is opened, returns true. So if you opened a file, you can then go on to your processing commands.

HTH

rylo

Hi! Thanks for the response but not quite what I am looking for...

I can't open 16 text files every time to analyze the data. That's why I chose to use macro code and query tables as I can specify the path and it will load it.

I need to load 16 txt files from one folder (location varies) and another 16 text files from another location (location varies). What is the best way of doing that if the code I have cannot work?

Hi

While your folder location varies, could you fill a text box with the full path? So if you were asked to provide the 2 paths, the code could then work from those directions.

rylo

Hi!

So instead of browsing for the two folders, I type in the full path for the two locations? How is this done with a textbox? How do I change the query table connection to appropriately connect to these textbox path?

THANK YOU!

Hi

A couple of ways

Please Login or Register  to view this content.

With this you could then go

Please Login or Register  to view this content.

This will change to the drive and path and run your code as it stands
or incorporate the full path in the connection string

Please Login or Register  to view this content.

HTH

rylo