Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Workbook in a shared drive
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Workbook in a shared drive
Application.DefaultFilePath = ("\\Name of Your Server Drive\")
ChDir ("\\Name of Your Drive\") "al_ba" <al_814 wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Workbook in a shared drive
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. al_ba wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Workbook in a shared drive
chdir won't work with UNC paths.
corey wrote: Application.DefaultFilePath = ("\\Name of Your Server Drive\") ChDir ("\\Name of Your Drive\") "al_ba" <al_814 wrote in message ... 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Workbook in a shared drive
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" wrote: 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. al_ba wrote: 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. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Workbook in a shared drive
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("Desk Top") 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. al_ba wrote: 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" wrote: 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. al_ba wrote: 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. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Workbook in a shared drive
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("Desk Top") On Error Resume Next MkDir myPath & "\my Team" On Error GoTo 0 Dave Peterson wrote: 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("Desk Top") 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. al_ba wrote: 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" wrote: 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. al_ba wrote: 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Workbook in a shared drive
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("Desk Top")...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("Desk Top") 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 :) "Dave Peterson" wrote: 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("Desk Top") On Error Resume Next MkDir myPath & "\my Team" On Error GoTo 0 Dave Peterson wrote: 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("Desk Top") 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. al_ba wrote: 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" wrote: 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. al_ba wrote: 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Workbook in a shared drive
The code I posted worked fine for me. It looks like you removed a lot of the
error handling and you broke my suggestion. al_ba wrote: 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("Desk Top")...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("Desk Top") 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 :) "Dave Peterson" wrote: 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("Desk Top") On Error Resume Next MkDir myPath & "\my Team" On Error GoTo 0 Dave Peterson wrote: 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("Desk Top") 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. al_ba wrote: 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" wrote: 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. al_ba wrote: 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. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving A Workbook with queries to a shared drive | Excel Discussion (Misc queries) | |||
Saving shared spreadsheets on a shared drive. | Excel Discussion (Misc queries) | |||
Printing viewing a shared workbook on a shared drive | Excel Discussion (Misc queries) | |||
Interactive Workbook on Shared Drive | Excel Programming | |||
Problems Saving on a shared drive | Excel Discussion (Misc queries) |