Nikos,

Thanks, that bit of code solved my problem. However I'm now coming across
another problem that I didn't have before, and I'm not sure if it's related.

The next macro to run returns the filepath, worksheet name and sheet name of
the file:

Application.FindFile
AIRCell = "=cell(""filename"",a1)"
Range("q1") = AIRCell
AIRTab = "=MID(q1,FIND(""]"",q1)+1,LEN(q1)-FIND(""]"",q1))"
Range("q4") = AIRTab
AIR_Sheet = "=MID(q1,FIND(""["",q1)+1,FIND(""]"",q1)-FIND(""["",q1)-1)"
Range("q3") = AIR_Sheet
AIR_Results = "=LEFT(q1,FIND(""["",q1)-1)&q3"
Range("q2") = AIR_Results

Previously, the "cell(""filename"",a1)" command returned something along the
lines of

S:\filepath1\filepath2\[filename.xls]

But now it returns:

S:\filepath1\filepath2\filename.xls

without the square brackets, which messes up the formulas. I had this
problem previously, but it was solved by ensuring the filename had no spaces
in it, but that's not what's causing the problem this time. Any ideas?

Thanks,
Eileen.


"Nikos Yannacopoulos" wrote:

Eileen,

While you could indeed invoke the standard Windows File Save As dialog
through an API call, it is quite a piece of code (you can find it ready
on the net, but that doesn't make it any shorter...) to copy from file
to file. Also, if the just-change-the-file-type-and-extension approach
works for you, then why introduce unnecessary manual steps? Here's all
you need:

strFileName = ActiveWorkbook.FullName
strFileName = Left(strFileName, Len(strFileName) - 3) & "xls"
ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=xlNormal

HTH,
Nikos