Needed help trying to SAVE AS current filename

[jojo52479]

Hello, im trying to figure out a way to SAVE AS in my current program but when I do, I always get filename as my SAVE AS but I want to SAVE AS my current file name. Any help would be appreciated.

If WorksheetFunction.CountA(Range("J10:J33")) > 0 Then
ChDir "C:\Users\Joseph\Desktop\Food Maxx Test\401"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Joseph\Desktop\Food Maxx Test\401\FileName.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If

 

[Coding4Fun]

I ran your code and it worked fine with the exception that I was asked if i wanted to replace the existing file. I adjusted this like so to avoid this.

Sub Testing()


Application.DisplayAlerts = False
If WorksheetFunction.CountA(Range("J10:J33")) > 0 Then
ChDir "C:\Users\DeathDealer187\Desktop\"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\DeathDealer187\Desktop\Test.xlsx", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
Application.DisplayAlerts = True
End Sub

 

[yky]

If you just want to save the ActiveWorkbook with all the changes you have made, do this:

ActiveWorkbook.Save

If you want to close the ActiveWorkbook and save all the changes you have made, do this:

ActiveWrkbook.Close SaveChanges:=True

If you want to save the ActiveWorkbook to a different directory, do this:

Dim wb_name as String

wb_name = ActiveWorkbook.Name

[COLOR=#333333]If WorksheetFunction.CountA(Range("J10:J33")) > 0 Then[/COLOR]
[COLOR=#333333]'ChDir "C:\Users\Joseph\Desktop\Food Maxx Test\401"[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:= _[/COLOR]
[COLOR=#333333]"C:\Users\Joseph\Desktop\Food Maxx Test\401\" & wb_name & ".xls", FileFormat:=xlExcel8, _[/COLOR]
[COLOR=#333333]Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _[/COLOR]
[COLOR=#333333]CreateBackup:=False[/COLOR]

You can comment out the ChDir line because you don't need it. The file path already specifies where to save the file.

 

[Coding4Fun]

Well done yky much better answer

 

[yky]

Well done yky much better answer

Thank you.

 

[jojo52479]

Thank you both. Great information. YKY, I have a question for your code. On the save the activworksheet program, it works perfect. The only thing im seeing is when I save it, it save perfect. But when I save again, it doesnt prompt me to replace. It just add another .XLS to the end of the original .XLS. So I get "test.xls.xls and if I save it again I get,"test.xls.xls.xls. You get the picture. How would you fix that?

 

[Coding4Fun]

Try editing this line

[COLOR=#333333]"C:\Users\Joseph\Desktop\Food Maxx Test\401\" & wb_name & ".xls", FileFormat:=xlExcel8, _[/COLOR]

Remove the & ".xls"

Dim wb_name As String

wb_name = ActiveWorkbook.Name


If WorksheetFunction.CountA(Range("J10:J33")) > 0 Then
'ChDir "C:\Users\Joseph\Desktop\Food Maxx Test\401"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Joseph\Desktop\Food Maxx Test\401\" & wb_name, FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False


End If