Error - Replace command within access acting on excel file
At this point though, the appropriate range is selected so SELECTION should
work. I'll try it the other way though.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!
"Jacob Skaria" wrote:
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
---------------
Jacob Skaria
"Jacob Skaria" wrote:
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
"BlueWolverine" wrote:
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!
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!
|