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

    Importing an Excel Worksheet (2000)

    I'm trying to import the attached XLS file using the Get External Data/Import command but it just gives me text as data type for all the fields to be created(see attached jpg). What is the procedure to import the data so that they have the right data type(date/time for the first field and number for all the others)?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing an Excel Worksheet (2000)

    The first row in the worksheet is unsuitable for import, and the field names are actually in row 2. If you import the worksheet "as is", the second row will be seen as a data row, hence all columns are interpreted as text. There are two possible solutions:

    - Delete the first row of the worksheet (in a copy of the workbook if you don't want to modify the original.)
    or
    - Assign a name to the range consisting of the field names and data: select the range B2:Z32 and type a name (for example Data) in the cell address box, or select Insert | Name | Define...
    When importing into Access, specify that you want to show named ranges.
    Attached Images Attached Images
    • File Type: png x.png (12.2 KB, 0 views)

  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 an Excel Worksheet (2000)

    Thanks Hans,
    Is there a way in VBA to automate the Get External Data/Import command so that maybe an end user could just click a button and import such Excel file automatically?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing an Excel Worksheet (2000)

    Yes, look up TransferSpreadsheet in the VBA online help. An example:

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblImport", "C:ExcelTest.xls", True, "Data"
    or
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblImport", "C:ExcelTest.xls", True, "Foglio2!B2:Z32"

    The last argument used here is the range to be imported - either a named range or a range address.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing an Excel Worksheet (2000)

    If you are going to do this with the same data regularly and want a user to do it from a button, what about linking the spreadsheet (tables>new>link table) and then using an append query to put the data into an access table?

    If you want the table empty of the old data a delete query solves that problem.

    Docmd.OpenQuery "DeleteQueryName"
    Docmd.OpenQuery "AppendQueryName"

    You might want to setwarnings = false, this will turn off the windows messaging asking if you're sure you want to do this, if so put the setwarning = true in the exit handler to turn them back on.

    Ian

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

    Re: Importing an Excel Worksheet (2000)

    Hans, Ian, thank you so much, linking the Excel file is good, but for the purpose of my next request, I imported the xls file, obtaining the "April2004" table you see in the attached mdb.
    All the numeric data in the "April2004" table belong to the "Rezzato" field in the "Previsioni" table but the problem is the layout in the two tables is different because the hours are part of the "Ora" field in the "Previsioni" table while they're field names in the "April2004" table: how can I append the data in the "April2004" table to the "Giorno" and "Ora" and "Rezzato" fields in the "Previsioni" table? I think one way to solve the problem would be to turn the 25-field "April2004" table into a 3-field(Giorno, Ora, Rezzato) table which I could then append to the "Previsioni" table but how to go about that?
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing an Excel Worksheet (2000)

    You will have to use code for this:

    Sub AppendToPrevisioni()
    Dim cnn As ADODB.Connection
    Dim rstSrc As New ADODB.Recordset
    Dim rstTrg As New ADODB.Recordset
    Dim i As Long

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rstSrc.Open "April2004", cnn, adOpenForwardOnly, adLockOptimistic, adCmdTableDirect
    rstTrg.Open "Previsioni", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    Do While Not rstSrc.EOF
    For i = 0 To 23
    rstTrg.AddNew
    rstTrg!Giorno = rstSrc!Aprile
    rstTrg!Ora = i
    rstTrg!Rezzato = rstSrc.Fields(CStr(i))
    rstTrg.Update
    Next i
    rstSrc.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstSrc.Close
    Set rstSrc = Nothing
    rstTrg.Close
    Set rstTrg = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Be aware that this will ONLY set Rezzato, all the other data fields such as Termica will have their default value 0.

Posting Permissions

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