Importing Data from Excel

[Guest]

I have spent nearly two month designing a database for the company that I
work for. The database is used to bill our clients. I have yet to figure
out how to get the data that I need, which is in an Excel spreadsheet, into
the proper location in my access data tables.

For example:
I will be importing data for the billing with the code 8M2006 (August 2006),

I need to be able to pull the data in from several fields in the excel sheet.

What is the best method to do this?

 

[John Vinson]

On Tue, 11 Jul 2006 10:03:02 -0700, N. Propes <N.

I have spent nearly two month designing a database for the company that I
work for. The database is used to bill our clients. I have yet to figure
out how to get the data that I need, which is in an Excel spreadsheet, into
the proper location in my access data tables.

For example:
I will be importing data for the billing with the code 8M2006 (August 2006),

I need to be able to pull the data in from several fields in the excel sheet.

What is the best method to do this?

Probably - not being able to see your database or your spreadsheet -
you'll want to use File... Get External Data... Link to link Access to
the spreadsheet, and then run an Append query based on the linked
spreadsheet data. This query can use a criterion to select the desired
records and fields within those records, and append that information
to your local table.

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

You can use the TransferSpreadsheet Method/Action if you need to import an
entire sheet or a named range. If you need to find the data you want to
import and then just import a few columns, you will need to use OLE
automation. The best place to get samples is from the MSDN library. You
can also get help from EXCEL help. You won't find help on automating Excel
in Access help. In Access help, you will find help on automating ACCESS.
To get help on automation, go to the help files of the application you want
to automate. Since I work primarily in Access, I am not familiar enough
with the object models of Word or Excel (the most common applications that I
automate) to allow me to just sit down and write code so frequently, I'll
turn on the macro recorder in Word/Excel and record the operation that way.
Then I take the generated VBA, clean it up, and paste it into my Access app.

PS - if you set a reference to Excel in the Tools/References dialog found in
the VBA window, you will get intellisense for Excel objects.