Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem saving file to My Documents
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem saving file to My Documents
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem saving file to My Documents
On Sun, 26 Oct 2008 13:17:29 +1100, "NoodNutt"
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem saving file to My Documents
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem saving file to My Documents
On Tue, 28 Oct 2008 16:01:29 +1100, "NoodNutt"
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I change default file for saving documents? | Excel Discussion (Misc queries) | |||
Problem with saving documents as .csv file | Excel Programming | |||
Saving Excel File in Current Directory NOT My Documents | Excel Programming | |||
Saving Excel File in Current Directory NOT My Documents | Excel Programming | |||
cannot open documents as viruscan says problem file? | Excel Discussion (Misc queries) |