Subscript out of range 9 (1 Viewer)

scuddersm

Registered User.
Local time
Yesterday, 19:09
Joined
Mar 9, 2011
Messages
31
Hello all,

So it seems simple, but as part of an excel import I use some automated formatting to match my excel to my table structure. Recently I tried to modify it to remove the "periods" in my name field. However, I keep receiving a subscript out of range error. I also receive this error after multiple subsequent imports, but it appears to be random on that end. It gives me the error each time this part of the code is run.
Code:
objXLApp.Cells.Replace What:=".", Replacement:=" ", 
 LookAt:=xlPart, SearchOrder _:=xlByRows, MatchCase:=False,
 SearchFormat:=False, ReplaceFormat:=False
Thanks for any help on this one.

Scott
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:09
Joined
Feb 19, 2013
Messages
16,668
column names should not have spaces, if they have, you need to surround the name with square brackets - this may be your problem
 

scuddersm

Registered User.
Local time
Yesterday, 19:09
Joined
Mar 9, 2011
Messages
31
All spaces have been removed from the columns. Still receive the "subscript out of range" error. Here is a more complete list of my code to assist.
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
DoCmd.SetWarnings False
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.Cells.Select
objXLApp.Selection.UnMerge
objXLApp.Rows("1:3").Select
objXLApp.Selection.Delete Shift:=xlUp
objXLApp.Range("F1").Select
objXLApp.ActiveCell.FormulaR1C1 = "DutyStatus"
objXLApp.Range("H1").Select
objXLApp.ActiveCell.FormulaR1C1 = "StartDate"
objXLApp.Range("I1").Select
objXLApp.ActiveCell.FormulaR1C1 = "EndDate"
objXLApp.Range("J1").Select
objXLApp.ActiveCell.FormulaR1C1 = "Days"

 'ERROR ERROR THE NEXT LINE OF CODE PRODUCES SUBSCRIPT ERROR ERROR.
 
 objXLApp.Cells.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
Thanks
 

Minty

AWF VIP
Local time
Today, 00:09
Joined
Jul 26, 2013
Messages
10,374
Your code is replacing the . with a space, try "" instead of " ". I think that is what CJ was pointing out.
 

scuddersm

Registered User.
Local time
Yesterday, 19:09
Joined
Mar 9, 2011
Messages
31
Changing " " to "" has no effect. Error still present. :banghead:

Thanks all.
 

scuddersm

Registered User.
Local time
Yesterday, 19:09
Joined
Mar 9, 2011
Messages
31
No. not specifically. I am removing all of the "." throughout the entire sheet. A specific cell or column selection is not required. I have run this same VBA through Excel only and it has no problem running this line of formatting code, the error is only when running as formatting as part of the MS Access import.

Scott
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:09
Joined
Feb 19, 2013
Messages
16,668
not sure it would make a difference, but have you tried removing the underscore and putting the code on one line - or at least move it so

SearchOrder:=xlByRows

is not a split statement - as it is at the moment, Access may be seeing a space before the colon
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:09
Joined
Feb 19, 2013
Messages
16,668
also, try commenting out your setwarnings code until you have this resolved
 

scuddersm

Registered User.
Local time
Yesterday, 19:09
Joined
Mar 9, 2011
Messages
31
Moved to one line, same error. SetWarnings removed, doesn't provide any other indication or warning other than the "subscript out of range" error.
Modified code to search by both columns and rows, no effect. Tried using a Macro enabled file for import, no effect.

I am at a loss. Is there an update to Access that may have caused this?
 

Users who are viewing this thread

Top Bottom