Importing data through Visual Basic

[B_Taylor]

I have a project in which I need to create a VBA user form which will import data and put it in a new sheet so that it can be manipulated and I have no idea how to do this. The data file must be user selectable from their hard drive so the way I was considering was launching the data import wizard from within the user form, but I have no idea how to do this or if this would be the easiest/neatest solution.

Any help would be appreciated

[Leith Ross]

Hello B_Taylor,

Welcome to the Forum!

The following line of code invokes the Import Text File Wizard. This has to be called from a VBA macro.

Please Login or Register  to view this content.

This first brings up the Open Text File dialog. The user can then select the folder and file from on their machine. It then launches the Import Wizard.

Sincerely,
Leith Ross

[B_Taylor]

Thanks a lot, but the code gives an "Invalid Outside Procedure" error. Any idea what I'm doing wrong?

[Leith Ross]

Hello B_Taylor,

The code needs to be placed in Sub procedure and placed in a Standard VBA module before it be run manually or by code.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

[jevni1974]

Hi there,

you need to put the code inside a VBA procedure (macro) to make it work
e.g.

Please Login or Register  to view this content.

you can then run the macro through Tools -> Macro -> Macros
Or you can create a button in the sheet on which you can click to run the macro.

More important however is what type of data you want to import.
The above macro assumes you import .txt files, but is that what you are looking for?

J.

[Leith Ross]

Hello jevni1974,

Please follow the rules about wrapping your code when you post. I did it for you this time. If you forget next time I will lock the post.

Rules

Sincerely, Leith Ross

[B_Taylor]

Leith Ross:
Ah, of course, I forgot to put it in a sub. Feel pretty stupid now Thanks a lot!

The above macro assumes you import .txt files, but is that what you are looking for?

Preferably a .xls file, but I can just export its data to a .txt file if I need to.

[jevni1974]

hi B_taylor,

maybe the link below can help you getting some inspiration on how to import data from other excel sheets:

http://www.ozgrid.com/forum/showthread.php?t=46717

Hope it helps

[B_Taylor]

Hiya guys,

The solution you posted is working brilliantly, I just want to know if there is a way to force which cell it goes in rather than have the user select it, since I want it to go into a hidden sheet called "Trend_Calculation" and make it automatically go into cell A1.

Any idea how I can make it do this?