Access code, search replace in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

From within Access environment, I want to search and replace in Excel. I have
a database exported to excel, and want after exporting to replace some
characters.

This gives error message...what to use?

oApp.Application.ActiveWorkbook.Cells.Sheets(1).Replace What:="_",
Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
 
Still error message 9

subscript out of range?

oApp.ActiveWorkbook.Sheets(1).Cells.Replace What:="_", Replacement:="",
LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
 
Did anyone ever manage to do a search and replace from within Access
environment to Excel sheet?

I am trying to search where my problem is situated, but I don't know if it
is possible to do it.

Zurn
 
Hi,
this code works fine at me:

Sub replaceExcel()
Dim e As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set e = CreateObject("Excel.Application")
Set wb = e.Workbooks.Open("C:\book1.xls")
Set ws = wb.Sheets(1)

ws.Cells.replace What:="y", Replacement:="z", LookAt:=xlPart,
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

wb.Save
wb.Close
e.Quit
End Sub

must admit i was wrong about Sheets(0)

Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com
 
Note that if you declare all variables as objects, like Alex suggests, you
then need to either define all of the Excel-specific variables, or else
replace them with their actual values. For example, in the statement:

ws.Cells.replace What:="y", Replacement:="z", LookAt:=xlPart, SearchOrder
_
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

you need to supply values for xlPart (2) and xlByRows (1).

That would mean either adding:

Const xlPart = 2
Const xlByRows = 1

or changing to

ws.Cells.replace What:="y", Replacement:="z", LookAt:=2, SearchOrder _
:=1, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
again, subject out of range

Sub replaceExcel(ByVal pathFile As String)
Dim e As Object
Dim wb As Object
Dim ws As Object

Set e = CreateObject("Excel.Application")
Set wb = e.Workbooks.Open(pathFile)
Set ws = wb.Sheets(1)

ws.Cells.Replace What:="_", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

wb.Save
wb.Close
e.Quit
End Sub

Don't know where the problem is situated now. The pathfile is correct...
 
Thx Alex and Douglas!
Appreciate it.


Douglas J Steele said:
Note that if you declare all variables as objects, like Alex suggests, you
then need to either define all of the Excel-specific variables, or else
replace them with their actual values. For example, in the statement:

ws.Cells.replace What:="y", Replacement:="z", LookAt:=xlPart, SearchOrder
_
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

you need to supply values for xlPart (2) and xlByRows (1).

That would mean either adding:

Const xlPart = 2
Const xlByRows = 1

or changing to

ws.Cells.replace What:="y", Replacement:="z", LookAt:=2, SearchOrder _
:=1, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Click to expand...
 
Back
Top