Save to Network Drive Problems. Expanding Current code

I have some existing code which takes two cell values and combines them together to make a file name, saves the document and exits Excel.

Please Login or Register  to view this content.

Currently the file saves to my local PC, but i would like to save to a newtwork drive with the following path - "N:\COA Data\"

I have tried to search, but but i'm having trouble expanding my current code with the dynamic file name to include the network path.
Whenever i edit the "ActiveWorkbook.SaveAs Filename:=Sheets" part of the code to specify a path i get errors.

Is this at all possible, can anyone help?

Hi

Unless you have mapped the network resource as N, you have to write the file
path in form \\ServerName\ShareName\COA Data\


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"mcgrad" <[email protected]> wrote in
message news:[email protected]...
>
> I have some existing code which takes two cell values and combines them
> together to make a file name, saves the document and exits Excel.
>
> Code:
> --------------------
>
> ActiveWorkbook.SaveAs Filename:=Sheets("Input").Range("E9").Value & " " &
> Sheets("Input").Range("E11").Value & ".xls"
> If Application.Workbooks.Count = 1 Then
> Application.Quit
> End If
> End Sub
> --------------------
>
>
> Currently the file saves to my local PC, but i would like to save to a
> newtwork drive with the following path - "N:\COA Data\"
>
> I have tried to search, but but i'm having trouble expanding my current
> code with the dynamic file name to include the network path.
> Whenever i edit the "ActiveWorkbook.SaveAs Filename:=Sheets" part of
> the code to specify a path i get errors.
>
> Is this at all possible, can anyone help?
>
>
> --
> mcgrad
> ------------------------------------------------------------------------
> mcgrad's Profile:
> http://www.excelforum.com/member.php...o&userid=30821
> View this thread: http://www.excelforum.com/showthread...hreadid=504861
>

McGrad,

I encountered this problem a few weeks ago, and got around it by saving a
copy of the file to a local drive first, then saving it to the network drive
of your choice, then deleting the local copy. I since modified this to save
the file to the desktop, instead of a local drive, with the following:
(FolderName is a cell on the worksheet containing the location of the folder
to where the workbook is to be saved):


ProjectName = Sheets("SR").Range("ProjectName").Value
FolderName = Sheets("SR").Range("FolderName").Value
SaveString = "Project Workbook" & " - " & ProjectName & ".xls"

'Create the workbook on the Desktop first to avoid network drive file
creation errors.
ActiveWorkbook.SaveAs Filename:="D:\Documents and settings\" & _
Environ("username") & "\Desktop\" & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'Now create the workbook in the folder specified in "FolderName"
ActiveWorkbook.SaveAs Filename:=FolderName & SaveString, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'Delete the workbook that was created on the Desktop
If Dir("D:\Documents and Settings\" & Environ("username") & "\Desktop\"
& SaveString) <> "" Then
Kill "D:\Documents and Settings\" & Environ("username") &
"\Desktop\" & SaveString
End If

Hope this helps

Pete



"mcgrad" wrote:

>
> I have some existing code which takes two cell values and combines them
> together to make a file name, saves the document and exits Excel.
>
> Code:
> --------------------
>
> ActiveWorkbook.SaveAs Filename:=Sheets("Input").Range("E9").Value & " " & Sheets("Input").Range("E11").Value & ".xls"
> If Application.Workbooks.Count = 1 Then
> Application.Quit
> End If
> End Sub
> --------------------
>
>
> Currently the file saves to my local PC, but i would like to save to a
> newtwork drive with the following path - "N:\COA Data\"
>
> I have tried to search, but but i'm having trouble expanding my current
> code with the dynamic file name to include the network path.
> Whenever i edit the "ActiveWorkbook.SaveAs Filename:=Sheets" part of
> the code to specify a path i get errors.
>
> Is this at all possible, can anyone help?
>
>
> --
> mcgrad
> ------------------------------------------------------------------------
> mcgrad's Profile: http://www.excelforum.com/member.php...o&userid=30821
> View this thread: http://www.excelforum.com/showthread...hreadid=504861
>
>

Thanks guys
It's working fine now. Your help was much appreciated!