Hello All,
I have a folder that contains multiple zip files with some numbers separated by a "-" on it eg: 10123-45903-09354.zip, 99345-12367.zip, etc..I'm trying to list the file path against a cell that contains one of these numbers. If it does not then put in "does not exist"
eg:
[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]Numbers[/TD]
[TD]Path[/TD]
[/TR]
[TR]
[TD]10123[/TD]
[TD]this should read the 10123-45903-09354.zip path and store here[/TD]
[/TR]
[TR]
[TD]56338[/TD]
[TD]does not exist[/TD]
[/TR]
[TR]
[TD]34689[/TD]
[TD]does not exist[/TD]
[/TR]
[TR]
[TD]99345[/TD]
[TD]this should read the 99345-12367.zip and store here[/TD]
[/TR]
</tbody>[/TABLE]
This is what i've done so far:
<dl class="codebox"><dt>
</dt>
can somebody please help?
I have a folder that contains multiple zip files with some numbers separated by a "-" on it eg: 10123-45903-09354.zip, 99345-12367.zip, etc..I'm trying to list the file path against a cell that contains one of these numbers. If it does not then put in "does not exist"
eg:
[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]Numbers[/TD]
[TD]Path[/TD]
[/TR]
[TR]
[TD]10123[/TD]
[TD]this should read the 10123-45903-09354.zip path and store here[/TD]
[/TR]
[TR]
[TD]56338[/TD]
[TD]does not exist[/TD]
[/TR]
[TR]
[TD]34689[/TD]
[TD]does not exist[/TD]
[/TR]
[TR]
[TD]99345[/TD]
[TD]this should read the 99345-12367.zip and store here[/TD]
[/TR]
</tbody>[/TABLE]
This is what i've done so far:
<dl class="codebox"><dt>
</dt>
Code:
<dd><code>Dim rnum, fname, pathname, fpath As String
Dim i, x As Integer
pathname = ActiveWorkbook.Path & "\"
fname = Dir(pathname & "*.zip")
i = 2
x = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
While i <= x
rnum = Trim(ThisWorkbook.Sheets(1).Range("A" & i).Value)
Do While fname <> ""
fpath = pathname & fname
If InStr(fpath, rnum) > 0 Then
ThisWorkbook.Sheets(1).Range("B" & i).Value = fpath
Exit Do
Else
ThisWorkbook.Sheets(1).Range("B" & i).Value = "not found"
End If
fname = Dir()
Loop
i = i + 1
Wend</code>
</dd></dl>
can somebody please help?