Excel Import

[Russ]

Is there a way to import specific data from specific cells
in a Excel workbook sheet into a table in Access?

I have a workbook sheet named "Adam", the cells of
interest are C4 (Uptime), C7 (DownTime), C9
(AvailableTime), G2 (NoLoadTime) and G6 (ClosedTime) on
this sheet. The target temp table is called:
tbl_AvailableTime. Target fields are: Uptime, DownTime,
AvailbleTime, NoLoadTime, ClosedTime. There are other
fields that contain other data. Oh, all data on this sheet
in Excel are a calculated fields.

Thanks,

 

[Jon]

Is there a way to import specific data from specific cells
in a Excel workbook sheet into a table in Access?

I have a workbook sheet named "Adam", the cells of
interest are C4 (Uptime), C7 (DownTime), C9
(AvailableTime), G2 (NoLoadTime) and G6 (ClosedTime) on
this sheet. The target temp table is called:
tbl_AvailableTime. Target fields are: Uptime, DownTime,
AvailbleTime, NoLoadTime, ClosedTime. There are other
fields that contain other data. Oh, all data on this sheet
in Excel are a calculated fields.

Thanks,

Russ

I would use some code like this (assuming that all of the values are
numeric):

Public Sub sGetExcelData()
On Error GoTo E_Handle
Dim objXL As New Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim db As Database
Dim strSQL As String
Set objXLBook = objXL.Workbooks.Open("C:\Access Dev\Test.xls")
Set objXLSheet = objXLBook.Worksheets("Adam")
Set db = DBEngine(0)(0)
strSQL = "INSERT INTO [tbl_AvailableTime] (Uptime,
Downtime,AvailableTime,NoLoadTime,ClosedTime) " _
& " VALUES (" & objXLSheet.Cells(4, 3) & "," &
objXLSheet.Cells(7, 3) & "," & objXLSheet.Cells(9, 3) _
& "," & objXLSheet.Cells(2, 8) & "," & objXLSheet.Cells(6, 8)
& ");"
db.Execute strSQL
sExit:
On Error Resume Next
Set db = Nothing
Set objXLSheet = Nothing
objXLBook.Close
Set objXLBook = Nothing
objXL.Quit
Set objXL = Nothing
Exit Sub
E_Handle:
MsgBox Err.Description & vbCrLf & "sGetExcelData", vbOKOnly +
vbCritical, "Error: " & Err.Number
Resume sExit
End Sub

In the code above, I have a reference set to the Microsoft Excel
Objetc Library.

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring -
http://a.webring.com/hub?ring=microsoftaccess

 

[Jen]

Hi Russ,

The easiest way to do this is to name the range in excel.
Name cells c4:g6 something like "avail_time", then in
Access you would go to file>get external data>link
tables...get the "Adam" spreadsheet, then click on "show
named ranges".

NOTE: I know that excel provides a way to name non-
contiguous cells, but unfortunately I've found that Access
doesn't recognize the named range if they're based on non-
contiguous cells....go figure!

Regards,
Jen