Problem saving file to My Documents

[John Keith]

After doing some processing on a file I want to save it back to My
Documents with a new file name. I currrently have the following code:

strFilename = Title & " " & Format(Now(), "yyyymmdd") & " " &
Format(Now(), "hhmm") & ".xls"
MsgBox "Filename is " & strFilename
ActiveWorkbook.SaveAs strFilename

It appears that without specifiying a path name the file gets saved in
My Documents with the value of the string "Title" and the date/time
stamp added but I want to be more explicit on the path.

When I change the first line of code above to:

strFilename = "C:\My Documents\" & Title & " " & Format(Now(),
"yyyymmdd") & " " & Format(Now(), "hhmm") & ".xls"

the save as fails with the error of not finding the path. If I change
the line to:

strFilename = "C:\" & Title & " " & Format(Now(), "yyyymmdd") & "
" & Format(Now(), "hhmm") & ".xls"

the file is saved to the root on C: correctly.

Any ideas on why I can't explicitly save to My Documents? And, am I
correct that if I don't specify a path it will default to My
Documents?


John Keith
(e-mail address removed)

 

[NoodNutt]

G'day Keith

Try this and see how you go, it is a modified version of a code I use to
Mail/SaveAs "less the (Mail) part of the code"

Sub SaveMy_ActiveWB()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim SourceWB As Workbook
Dim MyFilePath As String
Dim MyFileName As String

Set SourceWB = ActiveWorkbook

With SourceWB
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else
Select Case SourceWB.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

MyFilePath = Environ$("C:\My Documents") & "\"
MyFileName = Sourcewb.Name & " " & Format(Now, "yyyymmdd h-mm")

With SourceWB
.SaveAs MyFilePath & MyFileName & FileExtStr,
FileFormat:=FileFormatNum
End With
End Sub

HTH
Mark

 

[John Keith]

Mark,

Thank you for responding to my inquiry.

Your code suggestion has taught me some things I'll need to know when
I get to Excel 2007 but let me respond to the issue at hand.

MyFilePath = Environ$("C:\My Documents") & "\"

After executing this statement the value of MyFilePath is "\" so I
must be missing something. Also, I cant find 'Environ$" in the Help
file but I can find Environ. What is the prpose of adding the "$"???

I'll play some more when I have some time.

G'day to you.



John Keith
(e-mail address removed)

 

[NoodNutt]

G'day John

Try this code instead, it Compiled OK, so it should be alright.

Sub SaveMyFile()

Dim MyCurWB As Workbook
Dim MyCopyWB As Workbook
Dim FilePath As String
Dim NewFileName As String
Dim FileExtStr As String

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set MyCurWB = ActiveWorkbook

With MyCurWB
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else
If MyCurWB.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

FilePath = "C:\My Documents" 'Use this for local drive
'OR
FilePath = "\\YourNetworkFolder1\My Documents" 'Use if over network -
disregard the underlining

NewFileName = MyCurWB.Name & " " & Format(Now, "dd-mmm-yy h-mm AM/PM")
FileExtStr = "." & LCase(Right(MyCurWB.Name, Len(MyCurWB.Name) -
InStrRev(MyCurWB.Name, ".", , 1)))

MyCurWB.SaveCopyAs FilePath & NewFileName & FileExtStr
Set MyCopyWB = Workbooks.Open(FilePath & NewFileName & FileExtStr)


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

HTH
Mark.

 

[John Keith]

G'day John

Try this code instead, it Compiled OK, so it should be alright.

I will give your second suggestion a try later, but in the meantime I
found that the following seems to work:

strFilename = "C:\Documents and Settings\" & Environ("username") &
"\My Documents\" & Title & ".xls"
If MsgBox("Do you want to save this file to your computer (My
Documents) with the following filename?" & Chr(10) & strFilename,
vbYesNo) = vbYes Then
ActiveWorkbook.SaveAs strFilename
End If

It appears the specifying the full path is required, don't know why
using "C:\My Documents" wouldn't work????

Thanks for the ideas.


John Keith
(e-mail address removed)