Importing information from an Excel Spreadsheet into Access - Programmatically

  • Thread starter Thread starter Enrique
  • Start date Start date
E

Enrique

Hello,

I am trying to retrieve a specific information (Invoice
detail) in an Access 2000 application from an Excel File.
I need it so I can automate the
process of updating the inventory
in the Access Database. I will appreciate for any coding
ideas on how to open the Excel File from Access, and start
retrieving the Invoice information starting at a specific
cell, so I can then keep adding the records/details in my
Access database, and affect the inventory accordingly.


Thank you very much,

(e-mail address removed)
 
You can one of use two approaches.

1. Import the whole spreadshet into a table using the
TransferSpreadsheet method of the DoCmd object
(DoCmd.TransferSpreadsheet) And then use queries to
isolate/extract the data you want. This works if the data
is in neat colums.

2. Open the spreadsheet and copy selected cells. See the
example below (note that all of the Dim statements setting
up the objects are omitted):

Fname= <Name of workbook file without the filepath>
SpreadsheetToOpen = <put the full path and filename here>
Sheetname=<name of workhseet within the workbook>
apEXCEL.Workbooks.Open _&
SpreadsheetToOpen
Set CurrentWorksheet = apEXCEL.Workbooks _&
(Fname).Worksheets(Sheetname)

'** Set the value of a variable to the value of a cell
LogMgrID = CurrentWorksheet.Range("A1").Value

'** Write the file information to a table
FileRecs.AddNew
FileRecs!MgrID = LogMgrID
FileRecs.Update

FileRecs is a table recordset object. The code creating
this object is not shown)
 
Back
Top