Saving files with visual basic

[ab123456]

I have a macro in excel that is used to save the excel file to my a: drive when a certain sequence of events occur. My problem is that if there is no floppy in the drive i get an error message that allows me to retry or cancel. If i cancel it returns me to the debug screen.

Is there any way i can either check for the prescence of a floppy or capture the error so i exit the macro without entering the debugger?

Thanks for any help.

 

[wri]

yes-

you should study the 'ON ERROR' help. One can trap any
error and process it as you like with error trapping provided in VB and VBA.

 

[JerryYoakum]

Below is a VBA code example that works in Excel to check if a disk is in drive 'A' before saving the workbook. If there is not, it checks to see if the user would like to try to save to disk again; meaning that the user has put a disk in the drive. However, the user can choose not to try again and the VBA debugger is completely avoided.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
Dir "a:\."
ThisWorkbook.SaveAs "A:\book1.xls"
Exit Sub
errHandler:
If Err.Number = 52 Then
Dim tmp As Integer, message As String
message = "There is no disk in drive 'A'. " _
& "Do you want to try again?"
tmp = MsgBox(message, vbYesNo, "Drive Error&quot
If tmp = vbYes Then Resume
End If
End Sub

 

[ab123456]

Thanks for that jerryyoakum.

Just one thing though when i ran that code I had to change the error number from 52 to 71 to get it to run properly.

 

[JerryYoakum]

It could be a number of things: different version of Windows, Excel, or who knows. Something to keep in mind is that if you changed my code above to be more like the code below you will need the err.number to be 71.


Private Sub Worksheet_Activate()
On Error GoTo errHandler
Open "A:\excel.txt" For Output As #1
Print #1, "Hello from Excel"
Close #1
Exit Sub
errHandler:
If Err.Number = 71 Then
Dim tmp As Integer, message As String
message = "There is no disk in drive 'A'. " _
& "Do you want to try again?"
tmp = MsgBox(message, vbYesNo, "Drive Error&quot
If tmp = vbYes Then Resume
End If
End Sub