Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Add message box if VBA cannot find correct file path

0

Hello, I`m trying to add a text box that tells the user that if the file path is not found, they should change the path. My whole code runs but I just need to fix the lines after ChDir "C:\........." (between the comment lines) since after running my code, I was getting error 76. Im doing error handling, but I`m not sure if there is a simpler way to just say after running ChDir "File not found - change file path in the developer"

Appreciate the help.x

Sub addimport()
    On Error GoTo Errormessage 'added
    Dim Thiscommand As Variant
    Dim fileFilterPattern As String
    Dim n As Long
    fileFilterPattern = "Text Files (*.txt; *.csv),*.txt;*.csv"
    Thiscommand = Application.GetOpenFilename(fileFilterPattern)
    ChDir "C:\Users\joe\Documents\Bronx\Raw\05.23"
    If Thiscommand = False Then
        MsgBox "Did not select files from jc`s folder."
        Exit Sub
 '=======error message displays if path is not found by another user==========
Errormessage:
    Select Case Err.Number
    Case 76
    MsgBox "You need to change directory path"
    End Select
  '==========================================================================
    End If
    Application.ScreenUpdating = False
    Open Thiscommand For Input As #1
    Do Until EOF(1)
        Line Input #1, Thiscommand
        If Len(Thiscommand) > 0 Then
            Thiscommand = Replace(Thiscommand, ",", Chr(9))
            Thiscommand = Split(Thiscommand, Chr(9))
            ActiveCell.Offset(n, 0).Resize(1, UBound(Thiscommand) + 1).Value = Thiscommand
        End If
        n = n + 1
    Loop
    Close #1
    Application.ScreenUpdating = True
    For Each WS In Sheets
    On Error Resume Next
    For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = (r.Value) * 1
    Next r
Next WS     
End Sub
Answer
Discuss

Answers

0
Selected Answer

Hello Dagamerjc and welcome to the forum,

There are only a couple of things I can see which need tweaking.

First, there are two variables used in your code which have not been declared -WS and r

I also moved the On Error Goto Errormessage line to just before the line to which it applies - "ChDir "C:\.........."

I then simplified your Errormessage code. Your code after the errormessage remains unchanged.

Here is my suggested revision:

Sub addimport_2()

    Dim Thiscommand As Variant
    Dim fileFilterPattern As String
    Dim n As Long

    Dim WS As Worksheet     ' this variable was not declared
    Dim r As Range     ' this variable was not declared

    fileFilterPattern = "Text Files (*.txt; *.csv),*.txt;*.csv"
On Error GoTo Errormessage ' - moved
    ChDir "C:\Users\joe\Documents\Bronx\Raw\05.23"
    Thiscommand = Application.GetOpenFilename(fileFilterPattern)
    If Thiscommand = False Then
        MsgBox "Did not select files from jc`s folder."
        Exit Sub
 '=======   updated error message code if path is not found  ================
Errormessage:

    MsgBox "You need to change directory path"

    Exit Sub     ' ADDED
'==============================================================
    End If
    Application.ScreenUpdating = False
    Open Thiscommand For Input As #1
    Do Until EOF(1)
        Line Input #1, Thiscommand
        If Len(Thiscommand) > 0 Then
            Thiscommand = Replace(Thiscommand, ",", Chr(9))
            Thiscommand = Split(Thiscommand, Chr(9))
            ActiveCell.Offset(n, 0).Resize(1, UBound(Thiscommand) + 1).Value = Thiscommand
        End If
        n = n + 1
    Loop
    Close #1
    Application.ScreenUpdating = True
    For Each WS In Sheets
    On Error Resume Next
    For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = (r.Value) * 1
    Next r
Next WS
End Sub

Hope this helps. If this solves your problem please mark my answer as selected.

Cheers   :-)

Discuss

Discussion

Appreciate the help. Thanks a lot!
dagamerjc (rep: 2) May 30, '23 at 3:22 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login