Save file in current directory as .xls

[keith05281967]

Hello Coders,

I have an excel workbook "Actionable.XYZ.xlsx"

line 2 of the below code successfully removes Actionable." from the file name.
line 3 successfully maps the path "C:\Documents\Test_Folder" where the file currently resides.
What I want line 4 to do is save the workbook with the updated name in the current directory as a .xls file.

This is what I want: "C:\Documents\Test_Folder\XYZ.xls"
What i'm getting: "C:\Documents\Some_Other_Folder\XYZ.xlsx"
wrong directory, wrong extension, right file name.

1. sFile = ActiveWorkbook.Name
2. sFile = Replace(sFile, "Actionable.", "")
3. sPath = ThisWorkbook.Path

'save as .xls format
4. ActiveWorkbook.SaveAs sFile, FileFormat:=56


Excel 07
thanks,
keith

 

[xenou]

This changes the file name but not the folder.
2. sFile = Replace(sFile, "Actionable.", "")


So you get the same folder. You have to either parse the folder path out in a similar fashion, or provide the right absolute path to the folder where you want the file saved.

As far as the extension goes, I believe you've chosen the right file format (56), but you also need to change the extension in your final file name:

sFile = Replace(sFile, ".xlsx", ".xls")

 

[keith05281967]

Hi Xenou,

That fixes the .xls issue. thank you.

The path will change on a daily basis so I don't want to hard code it in.
I'm using this sPath = ThisWorkbook.Path to capture the current directory but i don't know how successfully incorporate it into my save as stmt.

ActiveWorkbook.SaveAs sFile, FileFormat:=56

thanks,
Keith

 

[xenou]

The path will change on a daily basis so I don't want to hard code it in.
I'm using this sPath = ThisWorkbook.Path to capture the current directory but i don't know how successfully incorporate it into my save as stmt.

Okay, if it's not the current directory how do we know what it really is? Do you want to be able to choose a directory? Or is there a kind of "rule" that we can follow?

 

[keith05281967]

Xenou,

Okay, I see where i've confused you.

The original file "Actionable.XYZ.xlsx" is in path C:\Documents\Test_Folder

And when I save it with the changes mentioned I want to save it back in the same path, same folder. In other words:
"C:\Documents\Test_Folder\XYZ.xls"

So there will now be 2 files in the same folder:
"Actionable.XYZ.xlsx"
"XYZ.xls"

But my question is about the syntax of incorporating the path into this statement: ActiveWorkbook.SaveAs sFile, FileFormat:=56

I tried: ActiveWorkbook.SaveAs sPath, sFile, FileFormat:=56
and: ActiveWorkbook.SaveAs sPath & sFile, FileFormat:=56

but that just addes the path as part of the file name.

does that make sense?

 

[xenou]

As far as I can tell you were on the right path, but probably needed a backslash added ...

Does this work:

[COLOR="Navy"]Sub[/COLOR] Foo()
[COLOR="Navy"]Dim[/COLOR] sFile [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

[COLOR="SeaGreen"]'replace "actionable" in file name[/COLOR]
sFile = ActiveWorkbook.Name
sFile = Replace(sFile, "Actionable.", "")

[COLOR="SeaGreen"]'remove old extension and replace with .xls[/COLOR]
sFile = Left(sFile, Len(sFile) - InStr(1, StrReverse(sFile), ".")) & ".xls"

[COLOR="SeaGreen"]'add folder path[/COLOR]
sFile = ActiveWorkbook.Path & "\" & sFile

[COLOR="SeaGreen"]'Save File as XL 2003[/COLOR]
ActiveWorkbook.SaveAs sFile, FileFormat:=56
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] sFile

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

FYI if for some reason you need further info on the file formats, there's a good summary here:
http://www.rondebruin.nl/saveas.htm

 

[keith05281967]

YES!! that's it!!

thank you!