Andybuck86
10-08-2011, 07:19 AM
Hi,
I currently have a template spreadsheet that is accessed by several pc's across my business. Some access the spreadsheet locally (via a mapped NAS drive) and some access it through a VPN.
I have two buttons on my spreadsheet that will save the spreadsheet based on filename in cell G1. One button will use the folder path in cell G2 and the other will use cell G4. The reason I have two buttons is because the folder paths are different depending on whether the spreadsheet is access locally or via the VPN.
i.e.
Cell G2 contains the VPN folder location - \\5.51.214.9\Network\Document (file://\\5.51.214.9\Network\Document)
Cell G4 contains the Local folder location - \\ftws1\Network\Document (file://\\ftws1\Network\Document)
The above is just an example. The folder paths vary with data entry
I would like to be able to use one button...that would check the connection before saving. Eg. if folder location in cell G2 does not work then use G4.
My code is as follows:
Option Explicit
Sub Save_Password()
Application.DisplayAlerts = False
Dim Open_Password As String
Dim New_File_Name As String
New_File_Name = Range("G2").Value & "\" & Range("G1").Value & ".xlsm"
Open_Password = InputBox("Please enter a password")
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"
' ActiveWorkbook.SaveAs New_File_Name, _ ' creates a new file with the name in G1
' Password:=Open_Password, _ ' this is the password the user enters
' WriteResPassword:="password" ' this is the master password
End Sub
The above is for the VPN connection. The button for the local connection is similar - this is the only change:
New_File_Name = Range("G4").Value & "\" & Range("G1").Value & ".xlsm"
Any help would be greatly appreciated
Andy
I currently have a template spreadsheet that is accessed by several pc's across my business. Some access the spreadsheet locally (via a mapped NAS drive) and some access it through a VPN.
I have two buttons on my spreadsheet that will save the spreadsheet based on filename in cell G1. One button will use the folder path in cell G2 and the other will use cell G4. The reason I have two buttons is because the folder paths are different depending on whether the spreadsheet is access locally or via the VPN.
i.e.
Cell G2 contains the VPN folder location - \\5.51.214.9\Network\Document (file://\\5.51.214.9\Network\Document)
Cell G4 contains the Local folder location - \\ftws1\Network\Document (file://\\ftws1\Network\Document)
The above is just an example. The folder paths vary with data entry
I would like to be able to use one button...that would check the connection before saving. Eg. if folder location in cell G2 does not work then use G4.
My code is as follows:
Option Explicit
Sub Save_Password()
Application.DisplayAlerts = False
Dim Open_Password As String
Dim New_File_Name As String
New_File_Name = Range("G2").Value & "\" & Range("G1").Value & ".xlsm"
Open_Password = InputBox("Please enter a password")
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"
' ActiveWorkbook.SaveAs New_File_Name, _ ' creates a new file with the name in G1
' Password:=Open_Password, _ ' this is the password the user enters
' WriteResPassword:="password" ' this is the master password
End Sub
The above is for the VPN connection. The button for the local connection is similar - this is the only change:
New_File_Name = Range("G4").Value & "\" & Range("G1").Value & ".xlsm"
Any help would be greatly appreciated
Andy