Import from Excel

[skc]

Hi,

I have an Access database setup with a table that is
output to a Form. These forms are used for printing.

What I want to do is to create an import routine - so that
the Excel sheet with it's pre-defined columns, e.g.
A1=Name, B1=Company etc... get automatically imported into
my table and the Form to load the current set of
information for printing.

How do I do this please?

I'm using Access 2000 and Win2K Pro.

Thanks,

skc

 

[John Nurick]

Hi,

If you only need to generate the printed output it's best to avoid
importing the data at all, and to link to the worksheet instead.

First get it working by using (File|Get External Data|Link) to create a
table linked to the Excel sheet with the current information. Let's call
it tblLinkData. Bind your form or report to this table (or a query on
the table).

Once that's working properly, create another form (unless you already
have a "control centre" form) and put a commandbutton on it. In the
button's Click event procedure put VBA code something like this:

'delete existing linked table
DoCmd.DeleteObject acTable, "tblLinkData"

're-link to sheet with updated information
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9,
"tblLinkData", "D:\Folder\File.xls", True, "Sheet1"

'open the form
DoCmd.OpenForm "MyForm"


If you're lucky, you won't even need to do this much: if the workbook
keeps its name and location as it is updated, the linked table will
reflect the changes in the data in the worksheet.

If you're unlucky, Access won't link to the worksheet, or will show lots
of Error values in the data because of limitations in the linking
system. In that case you will need to import the data, using
DoCmd.TransferSpreadsheet acImport ... instead of acLink.