TransferSpreadsheet Import access2002

[kat50]

Can someone help me create the VB to import a spreadsheet the catch is I want to be able to choose the file and not have it defined. I only know how to use the macro feature to set up the transfer spreadsheet but that requires a sepcific file name be impoorted each time.

Thanks a million!

 

[PHV]

You may consider the FileSearch object in VBA.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HoneyBee88]

I've been learning about that myself recently. I will tell you what I know (which isn't a lot).

If you just want to do it once or twice, you should convert your spreadsheet to a CSV (comma separated values) text file. You'll find that under the saving options in your save dialogue.

Then just close any open database in access, go to the open file dialogue in access, and open the csv file. Access will start it's import dialogue (which is a lot like the import dialogue in excel) and you will have your data.

Be aware, 1, you cannot save multiple sheets in the same csv file. You will have to save each sheet separately. MS Excel will warn you about that six times, though.

If you want to automate it, you have two options. You can do it the adhoc way, which is basically (from my current point of view) going to be writing two routines-- one in VBA for Excel and one in VBA for Access-- that basically automate the process described above.

There is a way (and I don't remember off the top of my head) to make VBA prompt the user for a file path with a browse dialogue-- just like when you save or open a file. You can return that file path to a variable in your code and make the code open whatever file you like, dynamically, that way. I would reccomend getting a good book about VBA. The books can be quite cheap. Make sure the book focuses on VBA, though. You will likely have to buy such a book online. The more general books that also cover regular tasks (like creating charts and working with pivot tables without using VBA code) don't really go into a lot of detail, and are best used as a reference, not a source of learning, as a book that focuses on coding will have all sorts of tips and tricks in it about common mistakes and misconceptions and misunderstandings, as well as recommendations and advice about best practices and writing good code.

If you want to do it the slick/advanced way then you need to learn about using ADO (Activecontrol Data Objects or something like that). You also need to know SQL and how to execute SQL inline (in VBA code).

ADO replaced DAO a while back.

(If you google search the two terms, you'll find a lot of comparitive articles that will tell you information ranging from historical perspectives about when and why the changes occured, and pragmatic advice speaking to those who need to know which object library to use and why when writing their code.)

You also will probably need to have some idea about what OLE DB is. The best I can figure (I'm just learning this part!) is that ADO and OLE DB do almost the same thing, only ADO is the middle man.

This is kind of complex stuff, and I can't tell from your post what you're likely interested in tackling.

To learn about these things, I've been:
Searching forums
Reading "Beginning Access 2002 VBA" by Robert Smith et al.
Reading the MSDN Developer Website
Reading articles at websites like this

Hopefully I've given you enough keywords to get you started!

 

[kat50]

Thanks for the information. I ended up adding the excel commands under references

http://support.microsoft.com/kb/198571

and using get file similar to:

http://www.ozgrid.com/VBA/ExcelsDialogBoxes.htm

Take care