File renaming problem

[bbotz]

In 2003 Excel VBA, this code saved the name correctly:

Application.Calculation = xlCalculationAutomatic
ChDir "K:"

wNFileName = "k:Safety\" & Left(wFileName, Len(wFileName) - 4) & " " &
Format(Now(), "mmddyy") & ".xls"
ActiveWorkbook.SaveAs FileName:=wNFileName
ActiveWorkbook.Close
ChDir ".."
ActiveWorkbook.SaveAs FileName:="k:" & wFileName

So a file name of "Quota File MW 201003.xls" would save with exactly that
filename.

Just upgraded to 2007 and same code now saves

"Quota%20File%20MW%20201003.xls" which I assume is URL encoding. How can I
turn that off, or have Excel 2007 revert back to 2003 behavior??

Thanks!!

 

[ker_01]

1. How are wFileName and wNFileName dimmed? If they aren't explicitly dimmed
as strings, that might be worth doing as a safeguard.

2. The code below includes two save procedures, for two different files(?)
(because the first one is closed). Is that intentional? Which one is saving
with the %20's, or is it both?

3. It may not be necessary (I haven't tested) but I'm surprised at the use
of k:Safety instead of k:\Safety, and k: instead of k:\

4. You are explicitly saving a .xls file from 2007, even though the native
format is .xlsx (or .xlsm or others). I haven't played in that area, so I
don't know if that might cause any problems, but consider testing with a
..xlsx or .xlsm extention to see if that has any impact, if the prior
suggestions aren't effective.

 

[bbotz]

Thanks. See below...

1. How are wFileName and wNFileName dimmed? If they aren't explicitly dimmed
as strings, that might be worth doing as a safeguard.

Everything dimmed and I always use OPTION EXPLICIT

2. The code below includes two save procedures, for two different files(?)
(because the first one is closed). Is that intentional? Which one is saving
with the %20's, or is it both?

The save procedure is a backup of the original file where I save the
original file to a subdirectory named 'safety' which could be five or more
levels down in the tree, hence not fully enumerating the path. I rename the
original file with the date appended to the file name. The saving of the file
in the safety folder is the one that is being named using the URL codes.

3. It may not be necessary (I haven't tested) but I'm surprised at the use
of k:Safety instead of k:\Safety, and k: instead of k:\

Again, convenience so I don't have to enumerate the entire path.

4. You are explicitly saving a .xls file from 2007, even though the native
format is .xlsx (or .xlsm or others). I haven't played in that area, so I
don't know if that might cause any problems, but consider testing with a
.xlsx or .xlsm extention to see if that has any impact, if the prior
suggestions aren't effective.

We have a really backwards IT department, I hate to say. We're still on XP
and Office 2003 for most users. I'm the power user in the bunch and got
permission to use Office 2007, so I have to write everything backwards
compatible using the 2003 format. (sigh)

I noticed this renaming behavior in the active save dialogue box when
accessing other than my hard drive (i.e., saving on the LAN).

Thanks for trying to help!