Error - Replace command within access acting on excel file

[BlueWolverine]

Hello,
MS ACCESS/EXCEL 2003 on XP PRO.

I am writing code in ACCESS to format an excel document exported from the
access file. I have an error.

v_look_for = "3 (i.e. all week-end)"
v_replace_with = 3
.Selection.Replace What:=v_look_for, Replacement:=v_replace_with, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

This returns an error
<<<Run-time error '9':
subscript out of range>>>
The error occurs on the .selection line

It's not a TYPE issue because I have tried to make v_replace_with="3" as
well and it doesn't work. The range in excel that I have selected DEFINITELY
has exactly v_look_for in it. The rest of my code for embedding excel vba in
access is working. and here's my setup.

Set obj_excel = CreateObject("Excel.Application")
obj_excel.Visible = True
obj_excel.DisplayAlerts = False

Dim str_target As String, row_count As Long, str_target_col As String,
lcv As Long

With obj_excel

'do all sorts of excel stuff

end with


Any idea what's dying? Excel help says it's an array problem, but there's
no dimmed array to kill.

Thanks!

 

[Jacob Skaria]

Try the below.

With ActiveSheet

v_look_for = "3 (i.e. all week-end)"
v_replace_with = "3"
Selection.Replace What:=v_look_for, Replacement:=v_replace_with, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

End With

If this post helps click Yes

 

[Jacob Skaria]

Oops..Basically you need to refer a range object as (Range.Replace)

Dim rngTemp As Range
Set rngTemp = Selection ' or may be wBook.ws.Range("A1:J20")
With rngTemp
v_look_for = "3 (i.e. all week-end)"
v_replace_with = "3"
..Replace What:=v_look_for, Replacement:=v_replace_with, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With

OR

v_look_for = "3 (i.e. all week-end)"
v_replace_with = "3"
Selection.Replace What:=v_look_for, Replacement:=v_replace_with, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

If this post helps click Yes

 

[BlueWolverine]

At this point though, the appropriate range is selected so SELECTION should
work. I'll try it the other way though.

 

[BlueWolverine]

This did NOT solve my problem. Request more ideas!!

 

[Patrick Molloy]

Jacob's first code worked fine for me (as expected) replacing several cell's
contents as it says on the tin.

when you stepped through the code , were there ant issues? put OPTION
EXPLICIT at the start of the code module

 

[BlueWolverine]

I found the answer to my problem!

For common knowledge.

When you write code in an ACCESS vba editor for code for an access
application, the compiler has no clue what "xlCenter" or "xlNone" means.

so in my code:

the ACCESS VBA Compiler has no clue what xlPart means. so it craps out.


2 ways to deal with this.

1) Find out the integer values of the xl constants. put the integer values
into the code in place of xlCenter etc.

or more elegantly,

2) Past code like the following into the top of a module manipulating excel,
so that you can leave the readability of the xl constants in place.
I am including a lot of them to make it simpler, and so you don't have to
look them up.

Public Const xlNone As Long = -4142
Public Const xlCenter As Long = -4108
Public Const xlContinuous As Long = 1
Public Const xlAutomatic As Long = -4105
Public Const xlEdgeLeft As Long = 7
Public Const xlEdgeTop As Long = 8
Public Const xlEdgeBottom As Long = 9
Public Const xlEdgeRight As Long = 10
Public Const xlInsideVertical As Long = 11
Public Const xlInsideHorizontal As Long = 12
Public Const xlThin As Long = 2
Public Const xlThick As Long = 4
Public Const xlLeft As Long = -4131
Public Const xlRight As Long = -4152
Public Const xlTop As Long = -4160
Public Const xlBottom As Long = -4107
Public Const xlLandscape As Long = 2
Public Const xlPaper11x17 As Long = 17
Public Const xlPaperLetter As Long = 1
Public Const xlPaperLegal As Long = 5
Public Const xlDownThenOver As Long = 1
Public Const xlPortrait As Long = 1
Public Const xlToLeft As Long = -4159
Public Const xlToRight As Long = -4161
Public Const xlDown As Long = -4121
Public Const xlUp As Long = -4162
Public Const xlPart As Long = 2
Public Const xlbyRows As Long = 1

 

[Patrick Molloy]

did you have a reference set to the Excel object model ?

I found the answer to my problem!

For common knowledge.

When you write code in an ACCESS vba editor for code for an access
application, the compiler has no clue what "xlCenter" or "xlNone" means.

so in my code:

the ACCESS VBA Compiler has no clue what xlPart means. so it craps out.


2 ways to deal with this.

1) Find out the integer values of the xl constants. put the integer
values
into the code in place of xlCenter etc.

or more elegantly,

2) Past code like the following into the top of a module manipulating
excel,
so that you can leave the readability of the xl constants in place.
I am including a lot of them to make it simpler, and so you don't have to
look them up.

Public Const xlNone As Long = -4142
Public Const xlCenter As Long = -4108
Public Const xlContinuous As Long = 1
Public Const xlAutomatic As Long = -4105
Public Const xlEdgeLeft As Long = 7
Public Const xlEdgeTop As Long = 8
Public Const xlEdgeBottom As Long = 9
Public Const xlEdgeRight As Long = 10
Public Const xlInsideVertical As Long = 11
Public Const xlInsideHorizontal As Long = 12
Public Const xlThin As Long = 2
Public Const xlThick As Long = 4
Public Const xlLeft As Long = -4131
Public Const xlRight As Long = -4152
Public Const xlTop As Long = -4160
Public Const xlBottom As Long = -4107
Public Const xlLandscape As Long = 2
Public Const xlPaper11x17 As Long = 17
Public Const xlPaperLetter As Long = 1
Public Const xlPaperLegal As Long = 5
Public Const xlDownThenOver As Long = 1
Public Const xlPortrait As Long = 1
Public Const xlToLeft As Long = -4159
Public Const xlToRight As Long = -4161
Public Const xlDown As Long = -4121
Public Const xlUp As Long = -4162
Public Const xlPart As Long = 2
Public Const xlbyRows As Long = 1