inserting records by importing excel spreadsheet on a form with the use of macro

[sachinvh]

I've to insert records by importing from excel spreadsheet on a form.I created a macro for it in design view of form.But in Transfer spredsheet action of that macro i've to give fixed file name.I want to add flexibility such that when a user clicks that macro button on the form he should be able to select the excel file by browsing i mean from any drive.So how to achieve this?

 

[boblarson]

You're going to be very limited in your options by using macros. If you really want to do this, you will probably need to venture into code.

 

[sachinvh]

How to achieve this using code?

You mentioned about the changes in code.So how to modify the code?

 

[boblarson]

You can either use the CommonDialog control, if you have it available to you, or you can go here:
http://www.mvps.org/access/api/api0001.htm
and use the code provided there (instructions there as well).

The site is a great resource and Dev's been around for a LONG time.

 

[sachinvh]

Where to use the code?

I tried to use the code at the place in the on-click option of properties of a button on a form which on clicking asks for excel spreadsheet to be imported.But i doesn't work. So where to use that code. Let's say i've import button on a form which on clicking asks for importing records from excel spreadsheet.Then how to proceed with the code provided?

 

[boblarson]

You have to put the code into a Module and then call it from your button's click code.