asked on

Import Excel cell value into Access field.

Been looking everywhere and can't find a solution - any ideas please!?

I need to be able to extract the value from an Excel cell reference, and import that value into a specific field within an Access database.

I can link the db record to the file, thats not an issue.

Eg
MyXLFile - MySheet - Cell A1 = £654,965

....import into Access.....

MyTable
Id - MyTextField - MyXLValue

Any ideas??

Nestorio

Are you trying to assing a single value to all the column (all the records in your table)?

CraigBFG

ASKER

No, a single value to a single record.

The idea being, that I have many records, each with their own excel file. Each XL will contain different values and the record must show accordingly.

Essentially, showing the cell value instead of having to type it in manually again.

Thanks

ASKER CERTIFIED SOLUTION

bonjour-aut

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CraigBFG

ASKER

ok....so how do I implement this??

bonjour-aut

you will need to wrap the XL-part in a recordset-loop

like this (there is shorter sytax to this also)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim SQL As String
Dim i
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object
Dim xlvorlage As String

SQL = "SELECT * FROM myTable;" 'I assume the Excelfilename is the third tablefield >> rs.fields(2) or rs.fields("myExcelFieldName") - let the taget field be the fifth

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

rs.MoveLast
rs.MoveFirst

' here comes the Excelthing - opening Excel
If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

For i = 1 To rs.RecordCount-1
objXL.Application.Workbooks.Open (rs.Fields(2))
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb
rs.Fileds(4) = .Worksheets(0).Cells(8, 1) ' lets assume your value is on the first Worksheet - you can call it bei name either - .Worksheets("myWorksheetname").Cells(8, 1)
End With
objActiveWkb.Close savechanges:=False
rs.MoveNext ' or rs.Move
Next i
' the Last one
With objActiveWkb
rs.Fileds(4) = .Worksheets(0).Cells(8, 1)
End With
objActiveWkb.Close savechanges:=False
' CloseExcel
If boolXL Then objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing
' close recordset
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

so i hope, there are no typos in it - check the code and give it a try

Regards, Franz