save file in unknown default location with string?

**poopdish** · 06-20-2007, 08:33 AM

This one has me scratching my head...

I need my macro to save the workbook in its default location each time it runs. I'm doing this with:
Application.DisplayAlerts = False/True
at the beginning and end of my routine. The file simply saves where it already is, and my macro doesn't need to know what its name is. The problem is:

I want the macro to save two versions of the spreadsheet. The first as it already is, and the second (after a small manipulation in my routine) as the same filename & directory with a string " - string".xls tacked on the end of the filename.

e.g. directory/filename" - string".xls

How do I do this without explicitly stating the filename or file directory at some point within my macro?

**mudraker** · 06-20-2007, 08:44 AM

You cann't

You have to tell Excel where to save to or it will use your default location, which for most people is My Documents & you also need to give it the saveas filename

You can use code to find out the path of the existing workbook & workbook name & have your code modify them as required

Here is 2 versions
Version 1 leaves you the workbook open that has string in its name
Version2 leaves you with the workbook open with the same name before you saved

Sub SaveVersion1()
   Dim sPath As String
   Dim sFname As String
   
   sPath = ThisWorkbook.Path
   sFname = ThisWorkbook.Name
   
   ThisWorkbook.Save
   ThisWorkbook.SaveAs Filename:=sPath & "\" _
      & Left(sFname, Len(sFname) - 4) & " String.xls"
End Sub

Sub SaveVersion2()
   Dim sPath As String
   Dim sFname As String
   
   sPath = ThisWorkbook.Path
   sFname = ThisWorkbook.Name
   
   ThisWorkbook.SaveAs Filename:=sPath & "\" _
      & Left(sFname, Len(sFname) - 4) & " String.xls"
   ThisWorkbook.SaveAs Filename:=sPath & "\" & sFname
End Sub

**poopdish** · 06-20-2007, 09:31 AM

This is exactly what I was looking for, but when I execute my macro, it resaves my PERSONAL.XLS file as PERSONAL - string.xls
I ran it a few times to test and now my macro workbook looks like PERSONAL - string - string - string - string.xls
How do I make sure it is not taking this as my default filepath rather than the intended file?