Runtime error '5'

V

Vishal.Gawade

New Member
Joined
Mar 23, 2009
Messages
7
Hi Team,

I am getting run time error in below code even after getting all correct result. Marked the debug area with yellow words.

Please chck & advice.

Sub findfile()
Mydir = "Y:\13_Banking\001_Bank statements\" & Range("C4").Value & "\" & Range("C5").Value & "\" & Range("C6").Value & "\" & Range("C7").Value & "\*.*"
RowCount = 5
First = True
Do While (1)
If First = True Then
MyFilename = Dir(Mydir)
First = False
Else
MyFilename = Dir
End If
If MyFilename = "G" Then Exit Do
Cells(RowCount, "G") = MyFilename
RowCount = RowCount + 1
Loop
End Sub

Thank you,
Vishal
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Maybe this will work for you:

Code: 
Sub findfile()
    Dim MyDir As String
    Dim RowCount As Long
    Dim MyFilename As String
    MyDir = "Y:\13_Banking\001_Bank statements\" & Range("C4").Value & "\" & Range("C5").Value & "\" & Range("C6").Value & "\" & Range("C7").Value & "\*.*"
    RowCount = 5
    MyFilename = Dir(MyDir)
    Do While MyFilename <> ""
        If MyFilename = "G" Then Exit Do
        Cells(RowCount, "G") = MyFilename
        RowCount = RowCount + 1
        MyFilename = Dir
    Loop
End Sub
 
Upvote 0
Thank you so much Andrew. It's woking perfect. Now I require to modify the Y:\ (y drive & wants to have it as network drive like \\106.61.192.20\ar_folder\ etc which do not support in VB so do you have any solution on it ?

Tanks once again.

Vishal
 
Upvote 0
Hi, Could you please give me the full code with using required part which you suggested in last reply ?

Also going further I am attaching below the recent templete which I am using however I want it in the format next to it with the specific cell as per column & row (Date) value. It need to run the date from file name [file name is in the format "GU01_AUGU01GB_(2009-04-22)"]. Please try if the file name can be catched in specific cell with link to the file & if a specific date file is not there then it should show as file not avaliable.

Ready FOrmat :
[Removed]

Proposed format :
[Removed]

Thank you,
Vishal
 
Last edited by a moderator:
Upvote 0
Have you tried replacing:

Rich (BB code): 
MyDir = "Y:\13_Banking\001_Bank statements\" & Range("C4").Value & "\" & Range("C5").Value & "\" & Range("C6").Value & "\" & Range("C7").Value & "\*.*"

with?

Rich (BB code): 
MyDir = "\\106.61.192.20\ar_folder\13_Banking\001_Bank statements\" & Range("C4").Value & "\" & Range("C5").Value & "\" & Range("C6").Value & "\" & Range("C7").Value & "\*.*"
 
Upvote 0
Yes. It's working. Can I send my file to you on your mail id. I want something more to do I have the same code just want to modify it. As it is just giving the files for 1 folder but I want it with more folders & all data in different columns as per different folder.

Thank you,
Vishal
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
run-time error '9' subscript out of range
Replies
1
Views
456
NdNoviceHlp
NdNoviceHlp
D
  • Solved
VBA Filter code not working with blank variable criteria
Replies
7
Views
342
kevin9999
K
M
  • Question
Loop through each file in folder and perform an action
Replies
0
Views
472
Monsieur Roo
M
D
  • Question
Data from one sheet to overide data on another sheet
Replies
0
Views
197
Daroh
D
C
  • Question
Listing file names based on column value
Replies
0
Views
364
CBlauv
C

Forum statistics

Threads
1,215,374
Messages
6,124,574
Members
449,173
Latest member
Kon123

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top