Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving drive d | Excel Discussion (Misc queries) | |||
Problem saving to network drive | Excel Programming | |||
EXCEL saving to A: drive | Excel Discussion (Misc queries) | |||
Problems Saving on a shared drive | Excel Discussion (Misc queries) |