VBA Can't find C: Drive

[wrudan]

Hello, I am trying to create a macro to pull a .txt file from my computer into excel, line for line. I generated the code below yesterday and it was running great without any problems. Today I opened the code to keep working on other steps to the program and it could no longer find the file on my drive. Every time I run the program, it gives me an error message "run-time error '75': Path/File access error" and stops at the "Open MyFile For Input As #1" command line. Below is my Code. Any suggestions would be great, thank you.

Sub Part_selection()

Dim MyFolder As String
Dim MyFile As String


MyFolder = "C:\Users\William.Rudan\Documents\1 Projects\Form Bending\5130 Program Files\Test PressBrake NC codes" & "\"
MyFile = Dir(MyFolder & Range("B2") & ".txt")


Worksheets("Temp").Select

Open MyFile For Input As #1

X = 0

Do While Not EOF(1)
Line Input #1, TXT
Cells(1, 1).Offset(X, 0) = TXT
X = X + 1
Loop

Close #1

Worksheets("Operation_Sheet").Select
Cells(2, 6).Interior.Color = RGB(100, 250, 100)


End Sub

 

[ranman256]

use file system objects for file checking. Not DIR(myfile) :

Public Function FileExists(ByVal pvFile) As Boolean
Dim fso
On Error Resume Next
Set fso = CreateObject("Scripting.FileSystemObject")
FileExists = fso.FileExists(pvFile)
Set fso = Nothing
End Function

Public Sub MakeDir(ByVal pvDir)
Dim fso
On Error GoTo errMake
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(pvDir) Then fso.CreateFolder pvDir     'MkDir pvDir
Set fso = Nothing
Exit Sub
errMake:
'MsgBox Err.Description & vbCrLf & pvDir, , "MakeDir(): " & Err
Set fso = Nothing
End Sub

Public Sub KillFile(ByVal pvFile)
Dim fso
On Error Resume Next
Set fso = CreateObject("Scripting.FileSystemObject")
'FileReadOnly pvFile, False
fso.DeleteFile pvFile
Set fso = Nothing
End Sub

Public Function DirExists(ByVal pvDir) As Boolean
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
DirExists = fso.FolderExists(pvDir)
Set fso = Nothing
End Function

 

[RoryA]

Dir only returns the file name, not including the path, so you should include the path in the name of the file when you try to open it.

 

[wrudan]

Awesome! I'm pretty sure this will work now. This is the Code I made:

Sub Part_selection()

Dim Myfile As String
Dim BendFile As String
Dim MyFSO As FileSystemObject
Set MyFSO = New FileSystemObject

BendFile = Cells(2, 2).Value & ".txt"
Myfile = "C:\Users\William.Rudan\Documents\1 Projects\Form Bending\5130 Program Files\Test PressBrake NC codes\" & BendFile

If MyFSO.FileExists(Myfile) Then
MsgBox "The File Exists"
Worksheets("Operation_Sheet").Select
Cells(2, 6).Interior.Color = RGB(100, 250, 100)
Worksheets("Temp").Select
'Directory Address
Open Myfile For Input As #1
'Starting Point
x = 0
'Run the Procedure while it is not at end of file
Do While Not EOF(1)
Line Input #1, TXT
Cells(1, 1).Offset(x, 0) = TXT
x = x + 1
Loop
Close #1

Else
MsgBox "The File Does Not Exist"
Worksheets("Operation_Sheet").Select
Cells(2, 6).Interior.Color = RGB(250, 100, 100)
End If

Worksheets("Operation_Sheet").Select

End Sub