Help with importing code

[George Too]

I have been searching the forum for some code that will help me import an Excel spread sheet into a table in my database, but no luck so far.

What I want is for the user to be able to select the Excel file to be imported and then have Access format the spread sheet and then import all the records in that sheet once they have the right format.

Now, if this is not possible, can anyone suggest an alternate way where there is not much digging from the user?

Thanks to all.

 

[Travis]

What Kind of Formatting are you talking about?

Are these Excel Files identical (Except for the Number of Records)?

One method would be to use Automation

See Microsoft Knowledge Base Article:

INFO: OLE Automation Objects with GetObject and CreateObject
ID: Q114347

And

HOWTO: Automate Excel 97 and Excel 2000 from Visual Basic
ID: Q219151

 

[George Too]

Yes, all Excel files are identical one-page-only files.

By formating I mean making the Excel spread sheet compatible with my Access table (adding necessary columns, making the cells the same format as the table, etc). I got this far where, through code, I have been able to format the page but don't know how to automate the process of getting the records to my table.

By teh way, i'm using Access 97

Thank you.

 

[RichMorrison]

George,

Get a copy of the "Access Developers Handbook" for Access 97. In it you will find an explanation and sample code to open a Windows standard dialog box for selecting files. This will allow the users to find the Excel spreadsheet they want.

Next, look in Help for the TransferSpreadsheet method. You will find a good explanation of the command and an example.

That's the two main tasks you need to automate. It can all be packaged in an event sub for a command button on a form.

HTH,
RichM

 

[George Too]

Well, I was looking for a cheaper solution (book runs for $45) though I'll certainly look for an used copy. Now, if some one has the code readily available for posting will be much appreciated.

Help was very usefull in explaining the Transfer method. I'll definitely will give that a try.

Thanks for the tips guys.

 

[George Too]

Got it!

Both the TransferSpreadsheet method and the Microsoft articles worked like a charm. I just can't believe how relatively easy this things are.

Thank you for all your help and patience.