Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate identifying if 200 plus files exist in a folder
Hi,
XL 2003 I found the following code from the newsgroup to check if a file exists in a particular folder. Sub MyTestFileExists() Dim FName As String FName = ThisWorkbook.Sheets("Sheet1").Range("A1").Text If Dir(FName) = "" Then Range("B1").Value = "Does not exist" Else Range("B1").Value = "Exists" End If End Sub Code works great for one file. Is it possible to amend this code to loop through a dynamic range, (currently A1:A200, but will grow) to identify each filename and place the results in the adjacent cell in column B (B1:B200)? Any help appreciated. Thanks Bob East Maitland Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate identifying if 200 plus files exist in a folder
You can give this a subroutine a try...
Sub MyTestFileExists() Dim C As Range With ThisWorkbook.Sheets("Sheet3") For Each C In .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row) If Dir(C.Text) = "" Then C.Offset(0, 1).Value = "Does not exist" Else C.Offset(0, 1).Value = "Exists" End If Next End With End Sub There is no need to specify the range... it will automatically look from A1 to the last used row in Column A. Rick "Bob Maloney" wrote in message ... Hi, XL 2003 I found the following code from the newsgroup to check if a file exists in a particular folder. Sub MyTestFileExists() Dim FName As String FName = ThisWorkbook.Sheets("Sheet1").Range("A1").Text If Dir(FName) = "" Then Range("B1").Value = "Does not exist" Else Range("B1").Value = "Exists" End If End Sub Code works great for one file. Is it possible to amend this code to loop through a dynamic range, (currently A1:A200, but will grow) to identify each filename and place the results in the adjacent cell in column B (B1:B200)? Any help appreciated. Thanks Bob East Maitland Australia |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate identifying if 200 plus files exist in a folder
Sub MyTestFileExists()
Dim FName As String With ThisWorkbook.Sheets("Sheet1") LastRow = .Cells(Rows.Count, "A"). _ End(xlUp).Row For RowCount = 1 To LastRow FName = .Range("A" & RowCount).Text If Dir(FName) = "" Then .Range("B" & RowCount).Value = _ "Does not exist" Else .Range("B" & RowCount).Value = _ "Exists" End If Next RowCount End With End Sub "Bob Maloney" wrote: Hi, XL 2003 I found the following code from the newsgroup to check if a file exists in a particular folder. Sub MyTestFileExists() Dim FName As String FName = ThisWorkbook.Sheets("Sheet1").Range("A1").Text If Dir(FName) = "" Then Range("B1").Value = "Does not exist" Else Range("B1").Value = "Exists" End If End Sub Code works great for one file. Is it possible to amend this code to loop through a dynamic range, (currently A1:A200, but will grow) to identify each filename and place the results in the adjacent cell in column B (B1:B200)? Any help appreciated. Thanks Bob East Maitland Australia |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate identifying if 200 plus files exist in a folder
Thanks Rick and Joel,
Appreciate the prompt assistance. Cheers Bob "Rick Rothstein (MVP - VB)" wrote in message ... You can give this a subroutine a try... Sub MyTestFileExists() Dim C As Range With ThisWorkbook.Sheets("Sheet3") For Each C In .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row) If Dir(C.Text) = "" Then C.Offset(0, 1).Value = "Does not exist" Else C.Offset(0, 1).Value = "Exists" End If Next End With End Sub There is no need to specify the range... it will automatically look from A1 to the last used row in Column A. Rick "Bob Maloney" wrote in message ... Hi, XL 2003 I found the following code from the newsgroup to check if a file exists in a particular folder. Sub MyTestFileExists() Dim FName As String FName = ThisWorkbook.Sheets("Sheet1").Range("A1").Text If Dir(FName) = "" Then Range("B1").Value = "Does not exist" Else Range("B1").Value = "Exists" End If End Sub Code works great for one file. Is it possible to amend this code to loop through a dynamic range, (currently A1:A200, but will grow) to identify each filename and place the results in the adjacent cell in column B (B1:B200)? Any help appreciated. Thanks Bob East Maitland Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if specifik folder exist. | Excel Programming | |||
Save file in a new folder, but create folder only if folder doesn't already exist? | Excel Programming | |||
Create Folder If It Doesn't Exist | Excel Programming | |||
Does folder exist problem | Excel Programming | |||
How to check if a folder/directory exist using VBA | Excel Programming |