Save directory

Posted by Paul on April 12, 2001 1:36 PM

How do I get a macro to uses the text in a cell as the directory the workbook will be saved in?

PS. The workbook will always be called sdata.xls
Thanks
Paul

Posted by Dave Hawley on April 12, 2001 1:47 PM

Hi Paul

As long as you cell contains a valid file path, you can use this:

Sub SaveAsCellName()
Dim FilePathName As String
'Written by OzGrid Business Applications
'www.ozgrid.com

FileCellName = Sheets("Sheet1").Range("A1")

ThisWorkbook.SaveAs FileCellName & sdata.xls
End Sub

Dave

OzGrid Business Applications

Posted by Dave Hawley on April 12, 2001 1:47 PM

Hi Paul

As long as you cell contains a valid file path, you can use this:

Sub SaveAsCellName()
Dim FilePathName As String
'Written by OzGrid Business Applications
'www.ozgrid.com

FileCellName = Sheets("Sheet1").Range("A1")

ThisWorkbook.SaveAs FileCellName & "sdata.xls"
End Sub

Dave

OzGrid Business Applications

Posted by Paul on April 12, 2001 2:14 PM

Thanks but that didn't work please help

When I run the macro the debugger highlights the line:
ThisWorkbook.SaveAs FileCellName & CustRef.xls

and displays the message Object Required.

Thanks

Posted by Dave Hawley on April 12, 2001 3:50 PM

Re: Thanks but that didn't work please help

Hi Paul

That first one slipped through without the quotations, it should be:

Sub SaveAsCellName()
Dim FilePathName As String
'Written by OzGrid Business Applications
'www.ozgrid.com

FileCellName = Sheets("Sheet1").Range("A1")

ThisWorkbook.SaveAs FilePathName & "CustRef.xls"
End Sub

But as I said you will need to be sure the cell contains a valid path, eg:

C:\MyDocuments\

Dave

OzGrid Business Applications