Saving to USB Drive either D or E
 

Hi I would like to be able to have a workbook save to either D or E
when a button is pressed on a worksheet, we have panasonic toughbooks
I have the following code which worked and checked if a USB drive is
in D. Work has recently bought encrypted USB Drives where the drive
letter is now E, but I would still like the user to be able to save to
D on unencrpyted USB drives..

Sub AASAVETOSTICK()
' AASAVETOSTICK Macro save to d without rename
' Macro recorded 19/03/2007 by *
On Error GoTo AASAVETOSTICK_Error
Application.DisplayAlerts = False
ChDir "d:\"
ActiveWorkbook.SaveAs Filename:="d:\April.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=True
Application.DisplayAlerts = True
On Error GoTo 0
Exit Sub
AASAVETOSTICK_Error:
MsgBox ("Please put USB Key into USB slot")
End Sub


Sub saveit()
' save to d but rename workbook
response = MsgBox("Rename & Save to District Supervisors USB Key?" _
, vbYesNo, "Save As")
If response = 6 Then
Name = InputBox("Enter a Filename", "Get Filename")
If Name = "" Then End
On Error GoTo AASAVETOSTICK_Error
ActiveWorkbook.SaveAs Filename:="D:\" & Name & ".xls"
End If
On Error GoTo 0
Exit Sub
AASAVETOSTICK_Error:
MsgBox ("Please put USB Key into USB slot")
End Sub

Thanks in advance Stephen

Saving to USB Drive either D or E
 

Hi Stephen,

Could you try something like:

If response = vbYes Then
ActiveWorkbook.SaveAs Filename:="D:\" & Name & ".xls"
ElseIf response = vbNo Then
ActiveWorkbook.SaveAs Filename:="E:\" & Name & ".xls"
Else
Exit Sub
End If


Is it not possible to use the Application.GetSaveAsFilename to let them
choose the location and filename in one go, and then just use the SaveAs with
the string that is returned?

Sean.

--
(please remember to click yes if replies you receive are helpful to you)


"pano" wrote:

Hi I would like to be able to have a workbook save to either D or E
when a button is pressed on a worksheet, we have panasonic toughbooks
I have the following code which worked and checked if a USB drive is
in D. Work has recently bought encrypted USB Drives where the drive
letter is now E, but I would still like the user to be able to save to
D on unencrpyted USB drives..

Sub AASAVETOSTICK()
' AASAVETOSTICK Macro save to d without rename
' Macro recorded 19/03/2007 by *
On Error GoTo AASAVETOSTICK_Error
Application.DisplayAlerts = False
ChDir "d:\"
ActiveWorkbook.SaveAs Filename:="d:\April.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=True
Application.DisplayAlerts = True
On Error GoTo 0
Exit Sub
AASAVETOSTICK_Error:
MsgBox ("Please put USB Key into USB slot")
End Sub


Sub saveit()
' save to d but rename workbook
response = MsgBox("Rename & Save to District Supervisors USB Key?" _
, vbYesNo, "Save As")
If response = 6 Then
Name = InputBox("Enter a Filename", "Get Filename")
If Name = "" Then End
On Error GoTo AASAVETOSTICK_Error
ActiveWorkbook.SaveAs Filename:="D:\" & Name & ".xls"
End If
On Error GoTo 0
Exit Sub
AASAVETOSTICK_Error:
MsgBox ("Please put USB Key into USB slot")
End Sub

Thanks in advance Stephen