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??
CraigBFGAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NestorioCommented:
Are you trying to assing a single value to all the column (all the records in your table)?
0
CraigBFGAuthor Commented:
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
0
bonjour-autCommented:
do not import, just connect temporary and copy the value to the record
question ist, where you get the info, which excelfile belongs to which record.

this codepiece writes to a cetain excel file to specific fields, but shows the method you can use to read.
so you will need to do a recordset, loop through it - open the execl-files and copy the valiues to you record

Private Sub ExcelDokument_Click()

    Dim objXL As Object
    Dim strWhat As String, boolXL As Boolean
    Dim objActiveWkb As Object
    Dim xlvorlage As String
       
    xlvorlage = GetWordVorlage() & "\" & Me.Excelvorlage ' this is the variable excel file

    If fIsAppRunning("Excel") Then
        Set objXL = GetObject(, "Excel.Application")
        boolXL = False
    Else
        Set objXL = CreateObject("Excel.Application")
        boolXL = True
    End If
   
    objXL.Application.Workbooks.Open (xlvorlage)
    Set objActiveWkb = objXL.Application.ActiveWorkbook
   
    With objActiveWkb
        .Worksheets("LS").Cells(8, 1) = CleanLabel(Me.AdressLabel)
        .Worksheets("LS").Cells(23, 1) = Me.Brief
       
    End With
   
   
    objXL.Application.Visible = True
    MsgBox "Access zu Excel-Kanal wird geschlossen !"
    On Error Resume Next
    objActiveWkb.Close savechanges:=False
   
    If boolXL Then objXL.Application.Quit
   
    Set objActiveWkb = Nothing: Set objXL = Nothing
   
End Sub

Regards, Franz
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CraigBFGAuthor Commented:
ok....so how do I implement this??
0
bonjour-autCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.