Excel 97 replace vs newer versions...

[jodleren]

Hi!

I found that the Cells.Replace function has been updated from 97 to
XP. I do not have the versions in between and not totally aware of the
versions available. Anyway, I order to detect it I came up with this:

Val(ExcelApplication.Version, vers, i); // get the version no as
a number
FOldExcel := (i<>0) or (vers<=8); // true for errors or max
version 8

Question: for 97 it is 8.0 - works well, but can it be with more that
one period?

Otherwise, the new "replace" would cause me problems, so my replace
looks like this:
Action wanted: replace strings such as ¤date¤ with e.g. 01.03.2010

// actual replace with respect to the Excel version!
if FOldExcel then
ExcelApplication.Cells.Replace(What := FOfficeReplaces[j],
Replacement := sTemp, LookAt := xlPart,
SearchOrder := xlByColumns, MatchCase := False) // ,
MatchByte := False can be added, does not change anything.
else
ExcelApplication.Cells.Replace(What := FOfficeReplaces[j],
Replacement := sTemp, LookAt := xlPart,
SearchOrder := xlByColumns, MatchCase := False,
SearchFormat := False, ReplaceFormat := False);

The "new" replace would cause an error in Excel 97: c:\whatever
\file.XLS\ could not be found.
The filename is correct but it adds a backslash.
The error message catched in my log is: OLE error 800A03EC

To sum up:

1) Now, my real problem is that it works in Excel XP, but the code for
Excel 97 does not cause any errors, but it does not carry out the
replace. Why?
2) For which versions of Excel is which version of Replace?

WBR
Sonnich

 

[Don Guillett]

I just recorded both of these. So, if you just leave out the last 2
parameters I think it will work in both versions.
Sub Macro1()'excel97
Columns("B:B").Select
ActiveCell.Replace What:="d", Replacement:="x", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Sub Macro5()'excel 2003
Columns("D").Select
ActiveCell.Replace What:="d", Replacement:="x", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False,ReplaceFormat:=False
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi!

I found that the Cells.Replace function has been updated from 97 to
XP. I do not have the versions in between and not totally aware of the
versions available. Anyway, I order to detect it I came up with this:

Val(ExcelApplication.Version, vers, i); // get the version no as
a number
FOldExcel := (i<>0) or (vers<=8); // true for errors or max
version 8

Question: for 97 it is 8.0 - works well, but can it be with more that
one period?

Otherwise, the new "replace" would cause me problems, so my replace
looks like this:
Action wanted: replace strings such as �date� with e.g. 01.03.2010

// actual replace with respect to the Excel version!
if FOldExcel then
ExcelApplication.Cells.Replace(What := FOfficeReplaces[j],
Replacement := sTemp, LookAt := xlPart,
SearchOrder := xlByColumns, MatchCase := False) // ,
MatchByte := False can be added, does not change anything.
else
ExcelApplication.Cells.Replace(What := FOfficeReplaces[j],
Replacement := sTemp, LookAt := xlPart,
SearchOrder := xlByColumns, MatchCase := False,
SearchFormat := False, ReplaceFormat := False);

The "new" replace would cause an error in Excel 97: c:\whatever
\file.XLS\ could not be found.
The filename is correct but it adds a backslash.
The error message catched in my log is: OLE error 800A03EC

To sum up:

1) Now, my real problem is that it works in Excel XP, but the code for
Excel 97 does not cause any errors, but it does not carry out the
replace. Why?
2) For which versions of Excel is which version of Replace?

WBR
Sonnich

 

[jodleren]

I found the reason to this problem.

The function Save does not work in 8.0 / Excel 97. Only if i save
SaveAs - AND use another filename, then it save the file...
Dont know why but the workaround it working rename file, open it,
save with right name, delete temporary file

The replace has worked all the time. I just used some software already
present, and working in Excel 11 and 12.

Still, I have no idea what version 9 abnd 10 will work for Save or
Replace.

WBR
Sonnich

I just recorded both of these. So, if you just leave out the last 2
parameters I think it will work in both versions.
Sub Macro1()'excel97
    Columns("B:B").Select
    ActiveCell.Replace What:="d", Replacement:="x", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
End Sub

Sub Macro5()'excel 2003
   Columns("D").Select
    ActiveCell.Replace What:="d", Replacement:="x", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False,  _
SearchFormat:=False,ReplaceFormat:=False
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

Hi!

I found that the Cells.Replace function has been updated from 97 to
XP. I do not have the versions in between and not totally aware of the
versions available. Anyway, I order to detect it I came up with this:

      Val(ExcelApplication.Version, vers, i); // get the version noas
a number
      FOldExcel := (i<>0) or (vers<=8); // true for errors or max
version 8

Question: for 97 it is 8.0 - works well, but can it be with more that
one period?

Otherwise, the new "replace" would cause me problems, so my replace
looks like this:
Action wanted: replace strings such as date with e.g. 01.03.2010

          // actual replace with respect to the Excel version!
          if FOldExcel then
            ExcelApplication.Cells.Replace(What := FOfficeReplaces[j],
              Replacement := sTemp, LookAt := xlPart,
              SearchOrder := xlByColumns, MatchCase := False) // ,
MatchByte := False can be added, does not change anything.
          else
            ExcelApplication.Cells.Replace(What := FOfficeReplaces[j],
              Replacement := sTemp, LookAt := xlPart,
              SearchOrder := xlByColumns, MatchCase := False,
              SearchFormat := False, ReplaceFormat := False);

The "new" replace would cause an error in Excel 97: c:\whatever
\file.XLS\ could not be found.
The filename is correct but it adds a backslash.
The error message catched in my log is: OLE error 800A03EC

To sum up:

1) Now, my real problem is that it works in Excel XP, but the code for
Excel 97 does not cause any errors, but it does not carry out the
replace. Why?
2) For which versions of Excel is which version of Replace?

WBRSonnich