Importing with a Macro

  • Thread starter Thread starter Cindy PR
  • Start date Start date
C

Cindy PR

My company uses an excel file to compile a lot of
information into various fields. The excel file has
different worksheets to store information. Some of this
information needs to be stored in an Access Database.

I need to come up with a way to simply click a button
(maybe through the use of a Macro) so that only specific
fiels from specific worksheets is uploaded into specified
fields in one of my Access tables.

I figured out how to import data from an excel file with
the use of a Macro, but the file had only one worksheet.
I need a more structured method to be able to pick and
choose specific worksheets and fields, and store them into
specified fields in the table.

Any suggestions?
 
Hi Cindy,

If you need to access individual cells on specific worksheets, you'll
need to use Automation and the Excel object model (see links below).

If on the other hand you have a variety of tables in various places on
various worksheets, consider using Excel's Named Range feature: give
them names in Excel, and then import them to Access by name.

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476

ACC2000: How to Use Automation to Fill a List Box (Q210145)
http://support.microsoft.com/?id=210145
This article shows you how to use Automation to populate a list box
with values from a Microsoft Excel worksheet

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235



Also, search www.mvps.org/access for "automation excel"

My company uses an excel file to compile a lot of
information into various fields. The excel file has
different worksheets to store information. Some of this
information needs to be stored in an Access Database.

I need to come up with a way to simply click a button
(maybe through the use of a Macro) so that only specific
fiels from specific worksheets is uploaded into specified
fields in one of my Access tables.

I figured out how to import data from an excel file with
the use of a Macro, but the file had only one worksheet.
I need a more structured method to be able to pick and
choose specific worksheets and fields, and store them into
specified fields in the table.

Any suggestions?
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top