Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Import from Excel into Access (2003/SP2)

    I need to import data from several Excel spreadsheets into one Access table each month. Each user will have a copy of the same spreadsheet and input data into the spreadsheet. They will then submit all the spreadsheets to one user a monthly basis. From a menu screen in Access, that user will browse for the file they need to import then click a button to automatically import the file. The number of records in each spreadsheet will vary from month to month. I've named a range but there is no way to know how many records there will each month, so I made the range quite large. How do I avoid importing all the blank records at the end of the range? Or is there another way to do this when the number of records vary from spreadsheet to spreadsheet?

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

    Re: Import from Excel into Access (2003/SP2)

    If the data begin in cell A1, you can simply import the entire worksheet. Access will automatically import only the 'used range' of the worksheet.

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Import from Excel into Access (2003/SP2)

    There's the problem... the data range doesn't begin until A12. There is a bunch of header and instructional info in the first 11 lines. Is there anyway to get rid of the blank lines in this case?

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

    Re: Import from Excel into Access (2003/SP2)

    Excel has dynamic ranges that adapt themselves automatically as rows are added or deleted, but unfortunately, Access doesn't recognize them. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    I'd move the instructions etc. to a separate sheet, or to a text box floating above the sheet, or to comments, and let the data range begin in cell A1.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import from Excel into Access (2003/SP2)

    Here's some code for you. You can pass in row 12 as intRow2Start and change intMax to whatever maximum number of rows you think you'll need to examine. This code just looks in column A, but you could change that.

    I used the UsedRange feature of Excel, but found that it did not always return the last CURRENTLY used row, so this corrects it.

    Good luck!

    Function fnLastRow(ByVal osheet As Excel.Worksheet, Optional intRow2Start As Integer) As Integer
    'Return last continuous used row of Excel worksheet by examining column A
    'up to 1000 rows
    Dim intRows As Integer, intRow As Integer
    Const intMax As Integer = 1000
    On Error GoTo err_fnLastRow
    If intRow2Start = 0 Or IsMissing(intRow2Start) Then
    intRow2Start = 1
    End If
    With osheet ' "
    'Get correct number of used rows (continuous range only)
    intRows = .UsedRange.Rows.Count
    intRow = intRow2Start
    GetUsedRange:
    For intRow = intRow To intRows + 1
    If IsEmpty(.Cells(intRow, 1).value) = -1 Then
    fnLastRow = intRow - 1
    Exit Function
    End If
    Next
    If intRows + 100 >= intMax Then
    fnLastRow = 0
    Exit Function
    Else
    intRows = intRows + 100
    GoTo GetUsedRange
    End If
    End With

    exit_fnLastRow:
    Exit Function
    err_fnLastRow:
    MsgBox "fnLastRow error: " & Err.Description
    fnLastRow = 0
    Resume exit_fnLastRow
    End Function

Posting Permissions

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