Access code, search replace in Excel

Z

Zurn

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
 
Z

Zurn

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
 
Z

Zurn

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
 
A

Alex Dybenko

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
 
D

Douglas J Steele

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
 
Z

Zurn

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...
 
Z

Zurn

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
find and replace multiple values in one statement 3
WORD VBA Search and switch to advanced ... 1
Limits of Substitute and Replace functions 4
Find and Replace is going wrong in some cases 10
Macro Not Changing Date Values 7
Find text, replace it but now I need to change the height of allpreceding/following text 10
Search and Replace 2

Top