Get Excel Value

T

Tim

Hi folks,

I have a excel spreadsheet called "test.xls" which has a "sheet1" in it. On
the sheet1, there has the following data.

A1 Peter
B1 student
B12 50
C5 15

I want to get those cells' value and append to a table from VBA. For example:

A1 go to Field1
B12 go to Field2
B1 go to Fie1d3
C5 go to Fie1d4

Could anyone can show me how to do it?

Thanks in advance.

Tim.
 
G

Graham Mandeno

Hi Tim

You need to create an instance of Excel, open the required workbook file,
open your table, add a new record, copy the required cells from the
worksheet to the table fields, save the new record, close everything, and
clean up.

This should get you going:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlWbk = xlApp.Workbooks.Open("C:\My Documents\test.xls",
ReadOnly:=True)
Set xlSht = xlWbk.Worksheets("Sheet1")
Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")
With rs
.AddNew
!Field1 = xlSht.Range("A1")
!Field2 = xlSht.Range("B12")
!Field3 = xlSht.Range("B1")
!Field4 = xlSht.Range("C10")
.Update
.Close
End With
xlWbk.Close
xlApp.Quit
Set xlSht = Nothing
Set xlWbk = Nothing
Set xlApp = Nothing
Set rs = Nothing
Set db = Nothing

You will need to add your own error handling. You will also need a
reference to the Excel object library, or you can use "late binding" and
declare all the xl... variables "As Object".
 
T

Tim

Graham,

The code works great. Thanks a lot.

Tim.

Graham Mandeno said:
Hi Tim

You need to create an instance of Excel, open the required workbook file,
open your table, add a new record, copy the required cells from the
worksheet to the table fields, save the new record, close everything, and
clean up.

This should get you going:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlWbk = xlApp.Workbooks.Open("C:\My Documents\test.xls",
ReadOnly:=True)
Set xlSht = xlWbk.Worksheets("Sheet1")
Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")
With rs
.AddNew
!Field1 = xlSht.Range("A1")
!Field2 = xlSht.Range("B12")
!Field3 = xlSht.Range("B1")
!Field4 = xlSht.Range("C10")
.Update
.Close
End With
xlWbk.Close
xlApp.Quit
Set xlSht = Nothing
Set xlWbk = Nothing
Set xlApp = Nothing
Set rs = Nothing
Set db = Nothing

You will need to add your own error handling. You will also need a
reference to the Excel object library, or you can use "late binding" and
declare all the xl... variables "As Object".
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Tim said:
Hi folks,

I have a excel spreadsheet called "test.xls" which has a "sheet1" in it.
On
the sheet1, there has the following data.

A1 Peter
B1 student
B12 50
C5 15

I want to get those cells' value and append to a table from VBA. For
example:

A1 go to Field1
B12 go to Field2
B1 go to Fie1d3
C5 go to Fie1d4

Could anyone can show me how to do it?

Thanks in advance.

Tim.
Click to expand...
Click to expand...
 
Top