Save File

[Papi]

I found this on Mr Excel ages ago and have got a lot of mileage from it.

Sub SaveMe()
With ActiveWorkbook
    .SaveAs Filename:="N:\Drop Box\Pend\" & Format(.Sheets("Sheet1").Range("G8").Value, "") & ".xls", FileFormat:=56
End With
End Sub

What would I need to do for this to take the path from a cell number (Sheet7 cell A20) and leave the file name as is?

 

[Papi]

Thanks again JLGWhiz. It returns a compile error - Invalid or Unqualified Reference on fName = Format(.Sheets at the word .Sheets

Here is the cleaned up version:

Dim myPath as String, fName As String
myPath = Sheets(7).Range("A20").value
fName = Format(.Sheets("Sheet1").Range("G8").Value, "") & ".xls"
If Right(myPath, 1) <> "\" Then myPath = myPath & "\"
ActiveWorkbook.SaveAs myPath & fName, FileFormat:=56

I moved the Fileformat down to the SaveAs line where it belongs and changed the path reference to sheet 7..

 

[Papi]

Thank you Son. It is returning a runtime error 13. I will look that up and see if I can figure it out.

ok, so just use:

[COLOR=#333333]Activeworkbook.SaveAs Filename:=Worksheets(Sheet1).Range("A1") & "\" & [/COLOR][COLOR=#333333]Worksheets(Sheet1).Range("A1").[/COLOR][COLOR=#333333]Range("A2"), FileFormat:=56
[/COLOR]

 

[Son]

oh, I see some typos, so either try this corrected code or try to get message boxes of the values in range A1 and A2, so that you can verify that the input is correct. Finally, if this does not work, try to remove the fileformat = 56

[COLOR=#333333][COLOR=#333333]Activeworkbook.SaveAs Filename:=Worksheets("Sheet1").Range("A1") & "\" & [/COLOR][/COLOR][COLOR=#333333]Worksheets("Sheet1").[COLOR=#333333][COLOR=#333333][/COLOR][/COLOR][COLOR=#000000]Range("A2"), [/COLOR]FileFormat:=56   [/COLOR]
 
msgbox [COLOR=#333333]Worksheets("Sheet1").Range("A1") 
[/COLOR]msgbox [COLOR=#333333]Worksheets("Sheet1").Range("A2") [/COLOR]

 

[Papi]

The code shows an error 9 and highlights the entire row yellow and does not make it to the messages. I moved the data to the cells noted just to make sure and tried it with both fileformat:=56 an without but it stops each time.

oh, I see some typos, so either try this corrected code or try to get message boxes of the values in range A1 and A2, so that you can verify that the input is correct. Finally, if this does not work, try to remove the fileformat = 56

[COLOR=#333333][COLOR=#333333]Activeworkbook.SaveAs Filename:=Worksheets("Sheet1").Range("A1") & "\" & [/COLOR][/COLOR][COLOR=#333333]Worksheets("Sheet1").[COLOR=#333333][COLOR=#333333][/COLOR][/COLOR][COLOR=#000000]Range("A2"), [/COLOR]FileFormat:=56   [/COLOR]
 
msgbox [COLOR=#333333]Worksheets("Sheet1").Range("A1") 
[/COLOR]msgbox [COLOR=#333333]Worksheets("Sheet1").Range("A2") [/COLOR]

 

[Son]

ok, so then try to place the save routine after the message boxes and see what happens

 

[Papi]

The messages show what's in the cells and then it goes to Error 9 and turns the routine yellow.

 

[Papi]

It just dawned on me that the file name no longer holds the path. I am guessing that I will have to commbine the filename and the path to come together.

 

[Son]

when it says error 9, what else does it say?

for example when i tried it, i didn't have a sheet with the name sheet1, so it produced error 9.

also, try this:

create a new workbook, and save it in your documents. Then insert a module and paste:

Sub TestPath()
Worksheets("Sheet1").Range("A1") = ActiveWorkbook.PathWorksheets("Sheet1").Range("A2") = ActiveWorkbook.Name




ActiveWorkbook.SaveAs Filename:=Worksheets("Sheet1").Range("A1") & "\" & Worksheets("Sheet1").Range("A2")


End Sub

 

[Papi]

Thanks very much Son. It's up and running. All your help is appreciated.

 

[Son]

You're welcome, nice to be of assistance.