×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Saving files with visual basic

Saving files with visual basic

Saving files with visual basic

(OP)
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.

RE: Saving files with visual basic

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.

RE: Saving files with visual basic

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")
        If tmp = vbYes Then Resume
    End If
End Sub

RE: Saving files with visual basic

(OP)
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.

RE: Saving files with visual basic

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")
        If tmp = vbYes Then Resume
    End If
End Sub

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources