Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru range and determine if filename exists in selected folder
I'm looking for some code that will do the following:- I have a range of cells that list certain bitmap file names. I want to loop thru the list in the range, open a folder and determine if a matching filename from the range of cells exists in the folder. If so place a simple comment in the adjacent cell next to the filename in the range of cells. The comment should be something like, "Present" if a match is found or "Missing" is one is not found. The range of cells could be "A1:A20" Any help would be greatly appreciated. Thanks burl_h |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru range and determine if filename exists in selected folde
Need to confirm how you want to select the folder.
Do you want to Browse and select the required folder or do you want to hard code the folder name and simply search for the file name? -- Regards, OssieMac "burl_h" wrote: I'm looking for some code that will do the following:- I have a range of cells that list certain bitmap file names. I want to loop thru the list in the range, open a folder and determine if a matching filename from the range of cells exists in the folder. If so place a simple comment in the adjacent cell next to the filename in the range of cells. The comment should be something like, "Present" if a match is found or "Missing" is one is not found. The range of cells could be "A1:A20" Any help would be greatly appreciated. Thanks burl_h |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru range and determine if filename exists in selected f
If you want to browse to select the folder to search then the following code
should do what you want. I have assumed that the .bmp extension is already on your files in the range A1:A20. If not then may need to modify the code. Sub Macro1() Dim strFolder As String Dim strFilename As String Dim rngToTest As Range Dim rngCell As Range With Application.FileDialog(msoFileDialogFolderPicker) .AllowMultiSelect = False If .Show = True Then 'User did not click Cancel strFolder = .SelectedItems(1) Else MsgBox "User clicked Cancel" Exit Sub End If End With 'Edit to your sheet name With Sheets("Sheet1") Set rngToTest = Range("A1:A20") End With For Each rngCell In rngToTest strFilename = Dir(strFolder & "\" & rngCell.Value) If strFilename < "" Then rngCell.Offset(0, 1) = "Present" Else rngCell.Offset(0, 1) = "Missing" End If Next rngCell End Sub -- Regards, OssieMac "OssieMac" wrote: Need to confirm how you want to select the folder. Do you want to Browse and select the required folder or do you want to hard code the folder name and simply search for the file name? -- Regards, OssieMac "burl_h" wrote: I'm looking for some code that will do the following:- I have a range of cells that list certain bitmap file names. I want to loop thru the list in the range, open a folder and determine if a matching filename from the range of cells exists in the folder. If so place a simple comment in the adjacent cell next to the filename in the range of cells. The comment should be something like, "Present" if a match is found or "Missing" is one is not found. The range of cells could be "A1:A20" Any help would be greatly appreciated. Thanks burl_h |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop thru range and determine if filename exists in selected f
OssieMac,
The solution is brilliant, it worked perfectly, thank you for your assistance. I really like the folder option allowing the user to select the appropriate folder. Thanks again. burl_h |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop to determine number of rows range | Excel Programming | |||
How to determine if a Folder/Directory Exists in Excel VBA | Excel Programming | |||
Determine if Named Range exists in Worksheet | Excel Programming | |||
Determine if folder exists | Excel Programming | |||
Determine exctent of selected range | Excel Programming |