Importing Different Files (1 Viewer)

[dreamz]

Hi

i currently have a macro setup with the transferspreadsheet function in it and it goes a little something like this..


Table Name - abc_def_ghi_jkl_m
File Name - =[Forms]![import_frm]![txtfile3]
Has Filed Name - Yes
Range - abc_def_ghi_jkl_m



now this works but what i need is, to be able to import a different sheet with a different range into the same table. Is there a way of doing this without re-creating the database 10 times to import 10 different files?


Thanks

 

[Hayley Baxter]

There's no reason why you can't do this. If you have a macro already set up just add another action line in the macro also set to transferspreadsheet with your different sheet name(s) and range(s) and this should work.

It probably would be tidier doing it with VBA but it should still work this way.

Hay

 

[dreamz]

Only have 1 unbound text box that i'd like to use to import 10 files.

I have setup the queries to be universal, so they'll run with all the files, but as opposed to having 10 macro's with 10 buttons to do this, i'd like to have 1 macro running in a loop.

what i need now, is a way of telling the macro to pause and ask me to select the next file so it can run the query again.

any idea's?

sure it can be done, just not sure how so for now i have 10 macro's with 10 buttons

 

[Hayley Baxter]

I am not sure I am really understanding you here, you currently have 10 macros with 10 buttons, you need 1 macro with 1 button which has 10 actions, those actions being the name(s) / range(s) for your different files. When you run this button it will run through all 10 actions giving you a completed table with all the data you need form the 10 files.

Did I misunderstand your question or is that what you are aiming for?

Hay

 

[dreamz]

I have 1 unbound TextBox, which i use to select the File i wanna import.

So i open the form and select the file. I then Click the button that runs the macro.



Now i have to do it again for a different file, to do this i have to select the file with the unbound text box again and click a different button which does the same thing.

Reason i have different buttons is,

1.) only have 1 unbound text box, so can only select 1 file at a time.
2.) files have different ranges set
3.) Each file gets exported to a different location.


Hope it's a bit clearer.

Thanks again