Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error - Replace command within access acting on excel file
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error - Replace command within access acting on excel file
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error - Replace command within access acting on excel file
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error - Replace command within access acting on excel file
This did NOT solve my problem. Request more ideas!!
-- 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error - Replace command within access acting on excel file
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" wrote in message ... This did NOT solve my problem. Request more ideas!! -- 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 < SNIP 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error - Replace command within access acting on excel file
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: 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 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 -- BlueWolverine MSE - Mech. Eng. Go BLUE! "Patrick Molloy" wrote: 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" wrote in message ... This did NOT solve my problem. Request more ideas!! -- 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 < SNIP 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error - Replace command within access acting on excel file
did you have a reference set to the Excel object model ?
"BlueWolverine" wrote in message ... 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: 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 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 -- BlueWolverine MSE - Mech. Eng. Go BLUE! "Patrick Molloy" wrote: 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" wrote in message ... This did NOT solve my problem. Request more ideas!! -- 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 < SNIP 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File access error in Excel | Excel Discussion (Misc queries) | |||
Excel file acting odd | Excel Discussion (Misc queries) | |||
how can I access favorites in the Excel 2007 Open file command | Excel Discussion (Misc queries) | |||
how can I access favorites in the Excel 2007 Open file command | Setting up and Configuration of Excel | |||
When opening Excel - get Access (Read-Only) file and error | Setting up and Configuration of Excel |