Run Time Error 9 Sub-script out of range

[scuddersm]

Good Morning All,

I'm getting a "runtime error 9" Sub-script out of range. This hasn't been a problem before when I have used this same formatting code in other applications. The problem occurs in the

objXLApp.Selection.Replace What:=".", Replacement:="..", LookAt:=xlPart,
             _ SearchOrder:=xlByRows, MatchCase:=False, 
             SearchFormat:=False, _
             ReplaceFormat:=False

This is a find and replace function for items in my excel sheet.

Here is the code in its entirety. Everything else works when the above if commented out.

Private Sub btnImportMOL_Click()
Dim MOL As Object
Dim MOLFilePath As String
Dim objXLApp As Object
Dim objXLBook As Object
Set MOL = Nothing
Dim MOLTable As String

 'Open file dialog and wait for user to select MOL detailed .xls or .xlsx
     Set MOL = Application.FileDialog(msoFileDialogOpen)
     MOL.Title = "Select MOL Detailed List" 
     MOL.AllowMultiSelect = False
     MOL.Show
     With MOL
    
 'If user does not select file open msgbox and exit sub
     If MOL.SelectedItems.Count > 0 Then
     MOLFilePath = .SelectedItems(1)
     Else
          MsgBox "You didn't select a file"
 
    Exit Sub

   End If

       MOLFilePath = .SelectedItems(1)

 End With

'Open excel from selected file
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open(MOLFilePath)
    objXLApp.Application.Visible = True
    objXLApp.Application.DisplayAlerts = False

 ' Detailed MOL List formating code. Will format fields to DB table layout.
    objXLApp.Rows("1:3").Select
    objXLApp.Selection.Delete Shift:=xlUp
    objXLApp.Range("J1").Select
    objXLApp.ActiveCell.FormulaR1C1 = "days"
    objXLApp.Selection.Replace What:=".", Replacement:="..",  
                   LookAt:=xlPart, _
                  SearchOrder:=xlByRows, MatchCase:=False, 
                   SearchFormat:=False, _
                  ReplaceFormat:=False
    objXLApp.Range("A1").Select

' Reset Excel settings, save file, and quit excel
    objXLApp.Application.DisplayAlerts = True
    objXLApp.ActiveWorkbook.Save
    objXLBook.Close
    objXLApp.Quit

' Delete current records in tblMOLList
    MOLTable = "Delete * from tblMOLImport;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL MOLTable

' Import New MOL detailed list into tblMOLList
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12,     
                "tblMOLImport", MOLFilePath, True
    Set objXLApp = Nothing
    Set objXLBook = Nothing

    DoCmd.OpenQuery ("qryDaysCalc"), acViewNormal
    DoCmd.OpenQuery ("qryDaysCalcUpdate"), acViewNormal
    DoCmd.OpenQuery ("qryAppntotesting"), acViewNormal
    DoCmd.SetWarnings True

Exit Sub

End Sub

Thanks for any assistance.

Scott

 

[sneuberg]

In trying to debug this I first found the underscores missing after

objXLApp.Selection.Replace What:=".", Replacement:="..",

and

SearchOrder:=xlByRows, MatchCase:=False,

and

 DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel12,

After fixing that I tried it on a blank excel spreadsheet. I'm not getting an error and it's hard to see why you would be getting one. Could you post the spreadsheet that's giving you the error?

 

[Cronk]

Or at least, advise the line giving the run time error.

 

[scuddersm]

 objXLApp.Selection.Replace What:=".", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Dies right here. Not sure why. I have taken out the VBA and placed it directly in the .xlsx sheet and it runs fine, but as soon as it is in access it hangs up. I attached a sample sheet of what I am working with.

Thanks for the help.

Scott

 

[sneuberg]

Sorry but that Spreadsheet didn't cause the error on my system. I'm at a lost as to what to tell you.