Excel automation 'replace' not supported?

[Kate]

Hi all. I posted this question on March 24th in the office
development automation newsgroup, but have received no
responses so I'll try here, where there is much more activity.

I have a nifty little module created in Access that imports
named ranges from all spreadsheets within a certain
directory, one at a time, saving each one to a subdirectory
and deleting it from the former directory.
All worked very well, UNTIL

I discovered that there was an error in a formula within a
range on a hidden 'data-collection' sheet in each workbook,
where all the imported named ranges reside.

I found that I was able to correct this using DDE as the
connectivity agent. However, it was quite slow so I played
around with OLE, setting a reference to Excel 11.0 object
library from within Access 2003. This seemed to work fine,
as I received no error messages, so I deleted all the
hard-found DDE code.

However, I discovered that none of the changes were being
made (it worked fine in that I was able to unprotect the
workbook, unhide the sheet, and select the range, and save
the workbook. But the 'replace' method wasn't working).

Is this a result of the changes Microsoft made to Excel
linked spreadsheets in Acess, as the result of some lawsuit,
referred to here?

http://support.microsoft.com/kb/904953/en-us

If so, is there any other way to make these changes on each
workbook as it is opened and the ranges are imported? The
Excel error is that the wrong cell is being reference in the
range being imported. I need to change the cell being
reference in each cell of the range, before importing.

I tried moving the code out of Access and into Excel,
running a macro from the personal.xls workbook, but still
got the error that the object didn't support this method.

Here is the code I'm using to attempt to make the changes
from within Access 2003:

Dim appXL As New Excel.Application
With appXL.ActiveWorkbook
'correct the formula in 'data' sheet for
production area
.Unprotect
.Sheets("data").Visible = True
.Sheets("data").Activate
.ActiveSheet.Range("$D$12:$D$31").Select

NOTE: I've tried both forms of the replace statement below,
and neither works

'.ActiveSheet.Selection.Replace What:="Production!C",
Replacement:="Production!F", Lookat:=xlPart,
SearchOrder:=xlByColumns, MatchCase:=False
.ActiveSheet.Selection.Replace
"Production!C", "Production!F", xlPart, xlByColumns, False
'correct mis-spelling on lookups sheet
.Sheets("lookups").Visible = True
.Sheets("lookups").Activate
.ActiveSheet.Range("$Y$5:$Y$7").Select

NOTE: again, I've tried both forms of the replace statement
below

'.ActiveSheet.Selection.Replace
What:="Tonness", Replacement:="Tonnes", Lookat:=xlPart,
SearchOrder:=xlByColumns, MatchCase:=True
.ActiveSheet.Selection.Replace "Tonness",
"Tonnes", xlPart, xlByColumns, True
.Save
End With


Please help, I'm going bonkers. Thanks to all,
Kate

 

[Kate]

Sorry, I left out two lines of code that are run after appxl
is defined:

Dim appXL As New Excel.Application
Set appXL = CreateObject("Excel.Application")
appXL.Visible = True