Saving Workbook in a shared drive

[al_ba]

Hello,

I am looking for a code to save a workbook or worksheet into a shared drive
or share point?

your help is much appreciated. Thanks in advance.

 

[corey]

Application.DefaultFilePath = ("\\Name of Your Server Drive\")
ChDir ("\\Name of Your Drive\")

 

[Dave Peterson]

I don't use sharepoint, but you could record a macro when you saved a copy to a
shared folder (on a network drive???) to see the code.

 

[Dave Peterson]

chdir won't work with UNC paths.

Application.DefaultFilePath = ("\\Name of Your Server Drive\")
ChDir ("\\Name of Your Drive\")

 

[al_ba]

Hello, here is a code that i was able to come up with...this will save the
excel workbook on the path I specified that changes as the date changes, my
problem...

1. It saves the workbook with name.. CHR &MyName_080708
CHR is constant it should be part of the name; MyName should equal to
whatever is there in SHEET1 A2...say SHEET1 A2 is "Internet", and
SHEET1 A1 is "080808"...then I want the filename to be...CHR Internet_080808

2. This code works well if the user choose "yes" all throughout, but after
the "Save As" question, if user choose, "No" or "Cancel" or click anything
except "yes" it is giving an error.
I want the user to be able to choose either "yes", "no" or "cancel" and
not get error.

Please help with these two issues. Thanks!

Sub Save_LineAd()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim ans

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
MyName = Format(Worksheets("SHEET1").Range("A2").Value)
sPath = "C:\Documents and Settings\acb\Desktop\My Team\CHR & MyName_"
sFilename = Format(Worksheets("SHEET1").Range("A1").Value, "mmddyy")
ans = MsgBox("Save File As " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs sPath & sFilename
ActiveWorkbook.Close savechanges:=True
Application.Quit
Application.StatusBar = "Application Closing."
End If
Else
ActiveWorkbook.Activate
End If

End Sub

 

[Dave Peterson]

This line:
sPath = "C:\Documents and Settings\acb\Desktop\My Team\CHR & MyName_"

wants to be:
sPath = "C:\Documents and Settings\acb\Desktop\My Team\CHR " & MyName & "_"

Maybe this will get you closer:

Option Explicit
Sub Save_LineAd()

Dim Response As Long
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim myName As String
Dim ans As Long
Dim myPath As String
Dim TestStr As String

'so you don't have to hardcode the path to desktop
myPath = CreateObject("WScript.Shell").SpecialFolders("DeskTop")

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
myName = Worksheets("SHEET1").Range("A2").Value
sPath = myPath & "\My Team\CHR " & myName & "_"
sFilename _
= Format(Worksheets("SHEET1").Range("A1").Value, "mmddyy") & ".xls"
ans = MsgBox("Save File As " & sPath & sFilename, Buttons:=vbYesNo)
If ans = vbYes Then
TestStr = ""
On Error Resume Next
TestStr = Dir(sPath & sFilename)
On Error GoTo 0
If TestStr <> "" Then
'file already exists
Response = MsgBox(Prompt:="Overwrite existing file?", _
Buttons:=vbYesNo)
If Response = vbNo Then
MsgBox "Try later"
Else
'hide any prompt
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs sPath & sFilename, _
FileFormat:=xlworkbooknormal
Application.DisplayAlerts = True
ActiveWorkbook.Close savechanges:=False 'it was just saved!
Application.StatusBar = "Application Closing."
Application.Quit
'if you've quit the application, then
'the macro has stopped!
End If
End If
End If
End If

End Sub

Personally, I wouldn't include the application.quit stuff. I wouldn't want this
workbook to interrupt any of my work on other workbooks.

 

[Dave Peterson]

PS. That "My Team" folder on the desktop has to exist!

If you're not sure, you may want to add a couple of lines:

'so you don't have to hardcode the path to desktop
myPath = CreateObject("WScript.Shell").SpecialFolders("DeskTop")
On Error Resume Next
MkDir myPath & "\my Team"

 

[al_ba]

Dave,
the code you mentioned didn't work, after the "save as" question, it just
stop and does nothing, but..I was able to use some of your codes and I also
removed the Application.Quit...I was able to use this too...myPath =
CreateObject("WScript.Shell").SpecialFolders("DeskTop")...although saving the
file to the path, is now solved, I still get error when choosing No, cancel,
or "X" or when I am prompt that the file already exist and want to override,
yes or no...Below is the code I come up with...I also recorded a macro to
save in the shared drive.

Corey, can you tell me how to use your code?
Application.DefaultFilePath = ("\\Name of Your Server Drive\")
ChDir ("\\Name of Your Drive\")

Here is the new code...

Sub Save_LineAd()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim myName As String
Dim myPath As String
Dim ans

myPath = CreateObject("WScript.Shell").SpecialFolders("DeskTop")
msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2
Response = MsgBox(msg, Style)
If Response = vbYes Then
myName = Worksheets("SHEET1").Range("A2").Value
sPath = myPath & "\My Team\CHR " & myName & "_"
sFilename = Format(Worksheets("SHEET1").Range("A1").Value, "mmddyy")
ans = MsgBox("Save File As " & myName & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs sPath & sFilename
ActiveWorkbook.Close savechanges:=True
Application.StatusBar = "Application Closing."
End If
Else
ActiveWorkbook.Activate
End If
End Sub

Here is the recorded code to save in a shared drive...

Sub
ChDir "Z:\CHR\LineAd\ChrLOB\CSB"
ActiveWorkbook.SaveAs Filename:= _
"Z:\CHR\LineAd\ChrLOB\CSB\CHTR CS Bill_101208.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

...thanks for your help

PS. That "My Team" folder on the desktop has to exist!

If you're not sure, you may want to add a couple of lines:

'so you don't have to hardcode the path to desktop
myPath = CreateObject("WScript.Shell").SpecialFolders("DeskTop")
On Error Resume Next
MkDir myPath & "\my Team"

 

[Dave Peterson]

The code I posted worked fine for me. It looks like you removed a lot of the
error handling and you broke my suggestion.

Dave,
the code you mentioned didn't work, after the "save as" question, it just
stop and does nothing, but..I was able to use some of your codes and I also
removed the Application.Quit...I was able to use this too...myPath =
CreateObject("WScript.Shell").SpecialFolders("DeskTop")...although saving the
file to the path, is now solved, I still get error when choosing No, cancel,
or "X" or when I am prompt that the file already exist and want to override,
yes or no...Below is the code I come up with...I also recorded a macro to
save in the shared drive.

Corey, can you tell me how to use your code?
Application.DefaultFilePath = ("\\Name of Your Server Drive\")
ChDir ("\\Name of Your Drive\")

Here is the new code...

Sub Save_LineAd()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim myName As String
Dim myPath As String
Dim ans

myPath = CreateObject("WScript.Shell").SpecialFolders("DeskTop")
msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2
Response = MsgBox(msg, Style)
If Response = vbYes Then
myName = Worksheets("SHEET1").Range("A2").Value
sPath = myPath & "\My Team\CHR " & myName & "_"
sFilename = Format(Worksheets("SHEET1").Range("A1").Value, "mmddyy")
ans = MsgBox("Save File As " & myName & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs sPath & sFilename
ActiveWorkbook.Close savechanges:=True
Application.StatusBar = "Application Closing."
End If
Else
ActiveWorkbook.Activate
End If
End Sub

Here is the recorded code to save in a shared drive...

Sub
ChDir "Z:\CHR\LineAd\ChrLOB\CSB"
ActiveWorkbook.SaveAs Filename:= _
"Z:\CHR\LineAd\ChrLOB\CSB\CHTR CS Bill_101208.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

..thanks for your help