Solved

Import Excel cell value into Access field.

Posted on 2004-10-21
401 Views
Last Modified: 2008-02-01
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??
0
Question by:CraigBFG
    5 Comments
     
    LVL 16

    Expert Comment

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

    Author Comment

    by:CraigBFG
    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
     
    LVL 18

    Accepted Solution

    by:
    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
     

    Author Comment

    by:CraigBFG
    ok....so how do I implement this??
    0
     
    LVL 18

    Expert Comment

    by: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
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now