[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Import Excel cell value into Access field.

Posted on 2004-10-21
5
Medium Priority
?
454 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
Comment
Question by:CraigBFG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

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

Author Comment

by:CraigBFG
ID: 12369668
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:
bonjour-aut earned 2000 total points
ID: 12369959
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
ID: 12371239
ok....so how do I implement this??
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 12372723
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

656 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