Simple use of Len?

[Stuart]

vFileName holds the full path and filename of a Word
document opened via Excel VBA.

A new workbook is created, and data is imported from
the Word document.

Before I save the new workbook, how can I use LEN
to simply change vFileName to end as *.xls, rather
than its' current *.doc, please?

Regards.

 

[Ray at]

filename = Replace(filename, ".doc", ".xls")

Ray at home

 

[Stuart]

So, goodbye to Len.

Thanks for that.

Regards.

filename = Replace(filename, ".doc", ".xls")

Ray at home

 

[Stuart]

That is very handy to know !
Many thanks.

Would a 'Len' solution be backwards-compatible
with my users? Their earliest version is Excel'97.

Regards.

 

[Tom Ogilvy]

sFileName = application.substitute(sfilename, "doc", "xls")


or

sfilename = Left(sfilename,len(sFilename)-4) & ".xls"

should work in all versions of Excel that support VBA.

 

[Stuart]

Thanks for 'substitute' and also for the 'Len' example.

Regards.