Issues with saving to USB drive

S

simon.q.rice

Excel 2003

I am trying to write an error trap for a routine that saves a data
workbook to a USB drive, which is configured on drive E. The error
trap I'm after in this instance is when the user fails to put a USB
dive into the port. My code so far:
***************************************************************************************************************
option explicit
Sub LocalCopy()
Dim sFolder As Variant
'BC is a declared public variable

'Save data to USB drive
sFolder = "E:\Bradford\" & Format(Date, "dd-mm-yy")
MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"

On Error GoTo ErrChk
MkDir sFolder

'Trap here if no USB drive inserted into PC

ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False
Exit Sub

ErrChk:
MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"
ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False

End Sub
***************************************************************************************************************
Unfortunately even when there is no USB drive present, the code doesn't
error. Can somebody advise me on how I might check for the presence of
a USB drive. I have tried a number of options from searches of this
usergroup but so far to no avail.

Thank you

Simon
 
S

smw226 via OfficeKB.com

Hi Simon,

Instead of the error trap maybe a simple if would suffice:

IF {directory on USB drive exists} then
save
else
{insert pen drive Msg}
end if?

I don't have a USB handy so can't test it, but its worth a try. Search on
here for "Check if folder exists" if your not sure of the code.

HTH

Simon

Excel 2003

I am trying to write an error trap for a routine that saves a data
workbook to a USB drive, which is configured on drive E. The error
trap I'm after in this instance is when the user fails to put a USB
dive into the port. My code so far:
***************************************************************************************************************
option explicit
Sub LocalCopy()
Dim sFolder As Variant
'BC is a declared public variable

'Save data to USB drive
sFolder = "E:\Bradford\" & Format(Date, "dd-mm-yy")
MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"

On Error GoTo ErrChk
MkDir sFolder

'Trap here if no USB drive inserted into PC

ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False
Exit Sub

ErrChk:
MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"
ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False

End Sub
***************************************************************************************************************
Unfortunately even when there is no USB drive present, the code doesn't
error. Can somebody advise me on how I might check for the presence of
a USB drive. I have tried a number of options from searches of this
usergroup but so far to no avail.

Thank you

Simon
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200610/1
 
S

simon.q.rice

Hi Simon,
Thank you for that tip. It worked well. I managed to find some code
snippets that helped me put it together. My final code is shown below.

I'm still curious to know though, is it possible to detect a USB drive?

option explicit
sub LocalCopy

Dim fs As Object
'sFolder is a defined public variable
On Error Resume Next

'Save data to USB drive
sFolder = "E:\Bradford\" & Format(Date, "dd-mm-yy")
On Error Resume Next
MkDir sFolder
On Error GoTo 0

MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"

'check that folder exists
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(sFolder) Then
MsgBox "Please insert your pen drive into the PC's USB port",
vbCritical, _
"No USB drive detected"
End If

'Save workbook
ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False
End Sub
Hi Simon,

Instead of the error trap maybe a simple if would suffice:

IF {directory on USB drive exists} then
save
else
{insert pen drive Msg}
end if?

I don't have a USB handy so can't test it, but its worth a try. Search on
here for "Check if folder exists" if your not sure of the code.

HTH

Simon

Excel 2003

I am trying to write an error trap for a routine that saves a data
workbook to a USB drive, which is configured on drive E. The error
trap I'm after in this instance is when the user fails to put a USB
dive into the port. My code so far:
***************************************************************************************************************
option explicit
Sub LocalCopy()
Dim sFolder As Variant
'BC is a declared public variable

'Save data to USB drive
sFolder = "E:\Bradford\" & Format(Date, "dd-mm-yy")
MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"

On Error GoTo ErrChk
MkDir sFolder

'Trap here if no USB drive inserted into PC

ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False
Exit Sub

ErrChk:
MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"
ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False

End Sub
***************************************************************************************************************
Unfortunately even when there is no USB drive present, the code doesn't
error. Can somebody advise me on how I might check for the presence of
a USB drive. I have tried a number of options from searches of this
usergroup but so far to no avail.

Thank you

Simon
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200610/1
Click to expand...
 
S

simon.q.rice

Hi Simon,
Thank you for that tip. It worked well. I managed to find some code
snippets that helped me put it together. My final code is shown below.

I'm still curious to know though, is it possible to detect a USB drive?

option explicit
sub LocalCopy

Dim fs As Object
'sFolder is a defined public variable
On Error Resume Next

'Save data to USB drive
sFolder = "E:\Bradford\" & Format(Date, "dd-mm-yy")
On Error Resume Next
MkDir sFolder
On Error GoTo 0

MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"

'check that folder exists
Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(sFolder) Then
MsgBox "Please insert your pen drive into the PC's USB port",
vbCritical, _
"No USB drive detected"
End If

'Save workbook
ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False
End Sub
Hi Simon,

Instead of the error trap maybe a simple if would suffice:

IF {directory on USB drive exists} then
save
else
{insert pen drive Msg}
end if?

I don't have a USB handy so can't test it, but its worth a try. Search on
here for "Check if folder exists" if your not sure of the code.

HTH

Simon

Excel 2003

I am trying to write an error trap for a routine that saves a data
workbook to a USB drive, which is configured on drive E. The error
trap I'm after in this instance is when the user fails to put a USB
dive into the port. My code so far:
***************************************************************************************************************
option explicit
Sub LocalCopy()
Dim sFolder As Variant
'BC is a declared public variable

'Save data to USB drive
sFolder = "E:\Bradford\" & Format(Date, "dd-mm-yy")
MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"

On Error GoTo ErrChk
MkDir sFolder

'Trap here if no USB drive inserted into PC

ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False
Exit Sub

ErrChk:
MsgBox "Please insert your pen drive into the PC USB port",
vbInformation, "Saving local copy"
ActiveWorkbook.SaveCopyAs sFolder & "\" & BC & ".xls"
Application.DisplayStatusBar = True
Application.StatusBar = "Saving local copy and closing workbook ..."
ActiveWorkbook.Close (False)
Application.DisplayStatusBar = False

End Sub
***************************************************************************************************************
Unfortunately even when there is no USB drive present, the code doesn't
error. Can somebody advise me on how I might check for the presence of
a USB drive. I have tried a number of options from searches of this
usergroup but so far to no avail.

Thank you

Simon
Click to expand...

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200610/1
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Saving to USB Drive either D or E 1
ChDrive and ChPath to a network drive 4
Saving workbook with a trusted extenstion VBA issue 3
Error Trapping saving file to network drive 2
Problem: Cells show 0 despite formulas 2
Saving with toolbar macro 0
Saving any MS ACCESS .mdb file to a USB Flash Drive corrupts it 6
Onenote with AceCad Digimemo tablet-Convert handwiriting to Text 9

Top