• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

How to save a file in excel using vba

I have a file. after inporting the data from another source i need to save as
Exising name of the file & "All" & yesterday date
Please look at my code and help me find out what is wrong with that, please
Private Sub GetSaveAsName()
Dim file_name As Variant

    ' Get the file name.
    file_name = Application.GetSaveAsFilename( _
        FileFilter:="Excel Files,*.xls,All Files,*.*", _
        Title:="Save As File Name")

    ' See if the user canceled.
    If file_name = False Then Exit Sub

    ' Save the file with the new name.
    If LCase$(Right$(file_name, 4)) <> ".xls" Then
        file_name = file_name & ".xls"
    End If
    ActiveWorkbook.SaveAs Filename:=file_name & " All" & Format(Now, "mm/dd/yy") - 1
End Sub

Open in new window

0
rfedorov
Asked:
rfedorov
1 Solution
 
joarvCommented:
Hi,
The reson that your code is not working is:
When the code tries to save the file. Varible "file_name" ends with ".xls" then you can't add more text to the "file_name"

0
 
Patrick MatthewsCommented:
If you want to just take the current file name, and append the All and date info...



With ActiveWorkbook
    .SaveAs .Path & Left(.Name, InStrRev(.Name, ".") - 1) & "All" & Format(Now - 1, "yyyy-mm-dd") & ".xls"
End With
0
 
Saurabh Singh TeotiaCommented:
There you go use the following code...
Saurabh...

Private Sub GetSaveAsName()
Dim file_name As Variant
 
    ' Get the file name.
    file_name = Application.GetSaveAsFilename( _
        FileFilter:="Excel Files,*.xls,All Files,*.*", _
        Title:="Save As File Name")
 
    ' See if the user canceled.
    If file_name = False Then Exit Sub
 
    ' Save the file with the new name.
    If LCase$(Right$(file_name, 4)) <> ".xls" Then
        file_name = file_name & ".xls"
    End If
    ActiveWorkbook.SaveAs Filename:=Replace(file_name, ".xls", "") & " All " & Format(Now - 1, "mm.dd.yy") & ".xls", FileFormat:=xlNormal
End Sub

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rfedorovAuthor Commented:
thank you for your help, it seems to work with one problem
it saves as ...-------------> All 04.01.2010
how to get rid of dots in the output

Should be------------------>All 04022010
Any help? Please
0
 
Saurabh Singh TeotiaCommented:
replace line-16 which is this..
 ActiveWorkbook.SaveAs Filename:=Replace(file_name, ".xls", "") & " All " & Format(Now - 1, "mm.dd.yy") & ".xls", FileFormat:=xlNormal
to this..
 ActiveWorkbook.SaveAs Filename:=Replace(file_name, ".xls", "") & " All " & Format(Now - 1, "mmddyy") & ".xls", FileFormat:=xlNormal  
Saurabh...
0
 
rfedorovAuthor Commented:
Thank you, working great
it saves automatically in My documents (i understand because of the default folder)
is there any chance to force to save the file to differenct place i want, let say desktpop???
0
 
Saurabh Singh TeotiaCommented:
You need to change the path in the filename to save it in your desktop..You can before you save it do something like this..that is add this line before line 16th..Rather use this code...
Saurabh...
 

Private Sub GetSaveAsName() 
Dim file_name As Variant 
  
    ' Get the file name. 
    file_name = Application.GetSaveAsFilename( _ 
        FileFilter:="Excel Files,*.xls,All Files,*.*", _ 
        Title:="Save As File Name") 
  
    ' See if the user canceled. 
    If file_name = False Then Exit Sub 
  
    ' Save the file with the new name. 
    file_name=replace(file_name,"Your old Path","Your New Path Here")
    ActiveWorkbook.SaveAs Filename:=Replace(file_name, ".xls", "") & " All " & Format(Now - 1, "mm.dd.yy") & ".xls", FileFormat:=xlNormal 
End Sub

Open in new window

0
 
rfedorovAuthor Commented:
file_name=replace(file_name,"C:\Documents and Settings\myid\My Documents","C:\Documents and Settings\myid\Desktop") ? is there any \\ dashes ?

0
 
Saurabh Singh TeotiaCommented:
I think this is good to go and you should not have any problem, You just need to ensure that there is extra "\" after desktop and your file name.
Saurabh...
0
 
rfedorovAuthor Commented:
Great help, thank you
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now