Trying to import a excel spreadsheet into MS Access DB and get a Error “Subscript out of range”

Trying to import a excel  2007 spreadsheet into MS Access DB and get a Error “Subscript out of range”  and does not import the data from spreadsheet.
Need help with editing my marco.

Option Compare Database
Option Explicit

Dim bCancelImport As Boolean

Private Sub cmdBrowse_Click()
    Dim fDialog As Office.FileDialog
    Dim varFile As Variant

    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
      .AllowMultiSelect = False
      .Filters.Clear
      .InitialFileName = txtImportFile
      .Title = "Please select the import file..."

        If .Show = True Then
            txtImportFile = .SelectedItems.Item(1)
            Me.Refresh
        End If
    End With

End Sub

Private Sub cmdCancelImport_Click()
    Dim ans As VbMsgBoxResult
    ans = MsgBox("Are you sure that you want the import cancelled?", vbYesNo, "Cancel Import")
    If ans = vbNo Then
        Exit Sub
    Else
        MsgBox "All records for current file will be deleted from database!", vbCritical, "Cancel Import"
        bCancelImport = True
    End If
    
End Sub

Private Sub cmdImport_Click()
On Error GoTo Err_cmdImport_Click
    Dim strFileType As String
    
    Dim ans As VbMsgBoxResult
    ans = MsgBox("Import Data ?", vbYesNo, "Import Data")
    If ans = vbNo Then
        MsgBox "Terminating import"
        Exit Sub
    Else
        lstStatus.RowSource = ""
        lstStatus.Requery
        DoCmd.Hourglass True
        ImportOSSLogFile
        AddToList lstStatus, "Finished!"
    End If

Exit_cmdImport_Click:
    DoCmd.Hourglass False
    Exit Sub

Err_cmdImport_Click:
    MsgBox Err.Description
    MsgBox Err.Source
    Resume Exit_cmdImport_Click

End Sub

Private Sub Form_Load()
    txtDatabasePath = CurrentProject.Path
    txtImportFile = CurrentProject.Path
    lstStatus.RowSource = ""
    lstStatus.Requery

    Me.Refresh

End Sub

Public Sub ImportOSSLogFile()
On Error GoTo Err_ImportOSSLogFile
    Dim oExcel As Object
    Dim oBook As Object
    Dim conADO As ADODB.Connection
    Dim rstADO As ADODB.Recordset
    Dim strSQL As String
    Dim iRow As Long

    Dim bBookOpen As Boolean
    Dim bExcelOpen As Boolean
    bBookOpen = False
    bExcelOpen = False

    Set oExcel = CreateObject("Excel.Application")
    bExcelOpen = True
    Set oBook = oExcel.Workbooks.Open(txtImportFile)
    bBookOpen = True

    Set conADO = Application.CurrentProject.Connection
    strSQL = "SELECT * FROM RBS"

    iRow = 1
    ' validate file
    Dim WorksheetName As String
    Dim totalRows As Long
    totalRows = 0
    'WorksheetName = "Sheet1"
    If Trim(oBook.Worksheets("Sheet1").Range("A" & iRow)) <> "OSS" Then
        MsgBox "Row 1, Column A is not OSS" & vbCrLf & "Value: " & Trim(oBook.Worksheets("Sheet1").Range("A" & iRow))
        oBook.Close savechanges:=False
        bBookOpen = False
        oExcel.Quit
        bExcelOpen = False
        'MoveTheFile theFilePath & "\" & theFileName, "LogsFailed"
    Else
        Set rstADO = New ADODB.Recordset
        rstADO.Open strSQL, conADO, adOpenKeyset, adLockOptimistic
        Dim ws As Worksheet
        For Each ws In oBook.Worksheets
            WorksheetName = ws.Name
            If WorksheetName = "Sheet1" Then
                iRow = 2
            Else
                iRow = 1
            End If
            AddToList lstStatus, "Processing Worksheet: " & WorksheetName
            While Len(Trim(oBook.Worksheets(WorksheetName).Range("A" & iRow))) > 0 And Not bCancelImport
                If Trim(oBook.Worksheets(WorksheetName).Range("B" & iRow)) <> Trim(oBook.Worksheets(WorksheetName).Range("C" & iRow)) Then
                    rstADO.AddNew
                    rstADO!OSS = Trim(oBook.Worksheets(WorksheetName).Range("A" & iRow))
                    rstADO!RNC = Trim(oBook.Worksheets(WorksheetName).Range("B" & iRow))
                    rstADO!RBS = Trim(oBook.Worksheets(WorksheetName).Range("C" & iRow))
                    rstADO!SPECIFIC_PROBLEM = Trim(oBook.Worksheets(WorksheetName).Range("D" & iRow))
                    rstADO!Perceived_Severity = Trim(oBook.Worksheets(WorksheetName).Range("E" & iRow))
                    rstADO!START_TIME = Trim(oBook.Worksheets(WorksheetName).Range("F" & iRow))
                    rstADO!END_TIME = Trim(oBook.Worksheets(WorksheetName).Range("G" & iRow))
                    rstADO!DURATION_MINUTES = Trim(oBook.Worksheets(WorksheetName).Range("H" & iRow))
                    rstADO!PROBABLE_CAUSE = Trim(oBook.Worksheets(WorksheetName).Range("I" & iRow))
                    rstADO!EVENT_TYPE = Trim(oBook.Worksheets(WorksheetName).Range("J" & iRow))
                    rstADO!FDN_REMAINDER = Trim(oBook.Worksheets(WorksheetName).Range("K" & iRow))
                    rstADO!PROBLEM_TEXT = Left(Trim(oBook.Worksheets(WorksheetName).Range("L" & iRow)), 255)
                    rstADO!START_TIME_1 = Trim(oBook.Worksheets(WorksheetName).Range("M" & iRow))
                    rstADO!START_TIME_VALUE = DateDiff("n", "1/1/2010 12:00 AM", rstADO!START_TIME)
                    rstADO.Update
                End If
                txtRowNumber = iRow
                Me.Refresh
                iRow = iRow + 1
                DoEvents
            Wend
            AddToList lstStatus, "Worksheet " & WorksheetName & ": " & iRow - 1 & " rows"
            totalRows = totalRows + iRow - 1
        Next ws

        oBook.Close savechanges:=False
        bBookOpen = False
        oExcel.Quit
        bExcelOpen = False
        If bCancelImport Then
            AddToList lstStatus, "Cancelled Import for " & txtImportFile
        Else
            AddToList lstStatus, "Finished importing " & totalRows & " rows from " & txtImportFile
        End If
    End If

Exit_ImportOSSLogFile:
    DoCmd.Hourglass False
    If bBookOpen Then
        bBookOpen = False
        oBook.Close
    End If
    If bExcelOpen Then
        bExcelOpen = False
        oExcel.Quit
    End If
    Exit Sub

Err_ImportOSSLogFile:
    MsgBox Err.Description
    MsgBox Err.Source
    Resume Exit_ImportOSSLogFile

End Sub

Open in new window

Troush2009Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Troush2009,

Which line does the debugger jump to?

Patrick

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Troush2009Author Commented:
I had a format error in my import files so it s working now.
Thanks!

tim
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.