Run Time Error 9 Sub-script out of range (1 Viewer)

scuddersm

Registered User.
Local time
Yesterday, 21:24
Joined
Mar 9, 2011
Messages
31
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
Code:
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.

Code:
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

AWF VIP
Local time
Yesterday, 18:24
Joined
Oct 17, 2014
Messages
3,506
In trying to debug this I first found the underscores missing after

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

and

Code:
SearchOrder:=xlByRows, MatchCase:=False,

and

Code:
 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?
 
Last edited:

Cronk

Registered User.
Local time
Today, 12:24
Joined
Jul 4, 2013
Messages
2,770
Or at least, advise the line giving the run time error.
 

scuddersm

Registered User.
Local time
Yesterday, 21:24
Joined
Mar 9, 2011
Messages
31
Code:
 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
 

Attachments

  • Test Sheet.zip
    11 KB · Views: 250

sneuberg

AWF VIP
Local time
Yesterday, 18:24
Joined
Oct 17, 2014
Messages
3,506
Sorry but that Spreadsheet didn't cause the error on my system. I'm at a lost as to what to tell you.
 

Users who are viewing this thread

Top Bottom