Using macro to import excel data - can I make the file selectable? 1

[fiordhraoi]

Hey all,

I've created a macro using the "TransferSpreadsheet" action in order to import data from a spreadsheet, and it works fine. However, I would like to modify it slightly.

The "TransferSpreadsheet" action forces you to put a file name into the macro, and it will always import that file. Is there any way to do this so that it asks for user input (preferably in one of the typical windows "file selection" boxes) as to which file it should use as a data source?

Thanks!

 

[fiordhraoi]

Clarification: Even more so than the file name, the issue is being able to select a path, but I figure if it lets you select the path it would likely let you select the name as well.

 

[Remou]

How about code, rather than a macro?
IMPORT Excel Sheet into Access allowing user to select file
thread705-760625

 

[fiordhraoi]

Could work... My VB is rusty but usable, my Access is horrible. Where do I put this code? As an action for a command button, or something similar? (Example: on my intro page, put a command button called "input data," and then make that code the executed action on clicking?)

Thanks for the prompt response.

 

[Remou]

The command button sounds good. You could call the function to get the file name from the On Click event and then do the import. There is always Tek-Tips if you get stuck.

 

[fiordhraoi]

Grah...man, I am as rusty as a 30 year old anchor with VB. How do you call a public function again?

 

[fiordhraoi]

Cancel that...duh, "call." I was looking for cmdButton.call or something like that.

 

[fiordhraoi]

Okay, trying the code there as written...I'm getting this error:

Compile Error:
User-defined type not defined

in reference to this:

Public Function GetFile()
Dim fDialog As Office.FileDialog

Thoughts?

 

[Remou]

I am working off Access 2000, so I don't have the FileDialog stuff, however, don't forget to add a reference to the Microsoft Office x.x Object Library.

 

[fiordhraoi]

Sorry if I'm being an idiot here. Ask me a TCP/IP question, I swear I'll be competent!

Anyway, how would I reference the object library? I've never done that in my limited VB experience - everything was a self-contained program that I wrote then.

 

[Remou]

Don't ask me a TCP/IP question!
In a module page, choose Tools->Reference and scroll down until you find the Microsoft Office x.x Object Library and tick its little box. x.x should be 10.0 or above, I think.

 

[fiordhraoi]

Okay, file choosing is working properly, now to integrate it with the import function...but that'll have to be tomorrow.

I might bug you more then. Thanks!

 

[fiordhraoi]

Okay, I lied, I tried one more time before I left.

It's all working, thanks for the help!