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

Roman FAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Roman FAuthor 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
Roman FAuthor 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
Roman FAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roman FAuthor Commented:
Great help, thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.