Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Values from an Excel Worksheet (2000)

    I'm trying to fill the MisCon field in the CaluscoAgosto table in the attached mdb with data drawn from the attached Excel file.

    I've prepared the GetDataFromExcel1 procedure in Module1
    <font color=red>Sub GetDataFromExcel1()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim blnStart As Boolean
    Dim datDate As Date
    Dim i As Integer
    Dim j As Integer
    Dim strSQL As String
    Dim a
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
    Set xlApp = CreateObject("Excel.Application")
    If xlApp Is Nothing Then
    MsgBox "Cannot open Excel.", vbExclamation
    Exit Sub
    End If
    blnStart = True
    End If

    On Error GoTo ErrHandler

    If xlApp.Dialogs(xlDialogOpen).Show = False Then
    GoTo ExitHandler
    End If
    Set xlWbk = xlApp.ActiveWorkbook

    Set cnn = CurrentProject.Connection
    For j = 2 To 32
    For i = 1 To 96
    strSQL = "UPDATE CaluscoAgosto SET MisCon = " & _
    Replace(xlWbk.Worksheets(1).Cells(j, i + 1), ",", ",")
    CurrentDb.Execute strSQL
    Next i
    Next j


    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    xlWbk.Close SaveChanges:=False
    Set xlWbk = Nothing
    If blnStart Then
    xlApp.Quit
    End If
    Set xlApp = Nothing

    Screen.ActiveForm.Repaint

    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    MsgBox "Salvare il file di Excel con il formato corrente.", vbExclamation
    Resume ExitHandler
    End Sub
    </font color=red>
    which does the purpose but all the records for the MisCon field get filled with the same value(22040.6) which is the value contained in the bottom right cell in the Excel worksheet.
    What do I have to change in the routine so as to fill the MisCon field with all the values from the Excel worksheet?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Values from an Excel Worksheet (2000)

    Giorgo,

    In your update query you is missing a WHERE clause.
    It should be something like :
    strSQL = "UPDATE CaluscoAgosto SET MisCon = " & _
    Replace(xlWbk.Worksheets(1).Cells(j, i + 1), ",", ",") _
    " Where Data = " & Fill in the coresponding cell in column A
    " And Hour = " & Fill in the coresponding cell in row 1

    But you'll have to modify the records in table CaluscoAgosto to met Hour with the row 1 of the excel sheet
    Francois

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Values from an Excel Worksheet (2000)

    Thank you Francois.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •