Search for and select and database to import to excel

[chrismann85]

Hi all,

I have a code which imports data from a certain database, see below:

Please Login or Register  to view this content.

However, I want the user to be able to specify the database using an input box, or a combo-box or something similar. I know its possible but I'm a little stuck. Basically, I would like the user to only have to put in the name of the database (as they will all be in the same folder).

Any Ideas?

*Apologies for the grammatical error in the title*

[Andy Pope]

You can use the GetOpenfilename method, which will display a dialog that allows you to select a file and or change folders.

Please Login or Register  to view this content.

[chrismann85]

Thanks Andy, I'm not sure where exactly in my code to put this. I have tried to run it on its own to see what it does but I get an error on the Line

Please Login or Register  to view this content.

Does this just open the file? I want to use it with my macro to import the data to my current worksheet (the data originates from Access if that makes any difference).

Thanks again,

Chris

[Andy Pope]

It would go at the top of your code.
GetOpenfilename does not open any files it just displays the dialog and returns file(s) selected by the user.
The returned value would be incorporated in to your query.

Please Login or Register  to view this content.

The error could be because the value in strPath is a folder that does not exist. Make sure the folder name is correct.

[chrismann85]

I'm still getting an error:

Run Time Error '76': Path not found

The error is on the line

Please Login or Register  to view this content.

[Andy Pope]

So is this the folder where the data should be?

"C:\Documents and Settings\cmann29\My Documents\2007 Test Converter Tool\"

If not replace it with the correct folder name.

[chrismann85]

The directory is correct, I've removed the line and it seems to do as you said it would. I'm still having issues, I think it is because I specify the file using you code at the beginning, then when my part of the code is used, it specifies a different database and it gets confused. Do I need to modify something in my code to tell it to use the file specified at the beginning?

[Andy Pope]

Yes you need to modify the query. See post #4 in this thread where I replaced the hard coded reference with the variable vntFile.

[chrismann85]

Sorry Andy, I didn't notice that you changed that, I put it into my code and it works like a charm.

I can't thank you enough

Cheers,

Chris