Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open files from folder by matching name
Hi all, I have name in column A like see below
A………..col Denise Buky John Well Sophy Bell and I have files in folder "C:\Record" like see below Record Sheet - Denise Buky.xls Record - Roy William.xls Record (John Well).xls Record Sheet Craig Brown.xls Record - Sophy Bell.xls Record Sheet - Dean Owen.xls I need macro which should check names in column A and only open those files from above folder in which that name match or appear. I tried doing this with below macro but this opens up all the files in folder. Please can any friend can help that how can i do it. Sub test() fldrName = "C:\Record" fName = Dir(fldrName & "\*.xls") lastcl = Workbooks("Data.xls").Sheets("Sheet1").Cells(Rows. Count, "A").End(xlUp).Row Do While fName < "" Set c = Workbooks("Data.xls").Sheets("Sheet1").Range("A2:A " & lastcl).Find(What:=fName, _ LookIn:=xlValues, LookAt:=xlWhole) If c Is Nothing Then Set bk = Workbooks.Open(Filename:=fldrName & "\" & fName) bk.Close False End If fName = Dir() Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open files from folder by matching name
Hi There
One method you could use is the File System Object which is handy in this sort of situation. It will check each file in the given folder to see if it is an Excel file if so you can use the InStr function to look for a string within a string to check the name of the file and if you get a match you can then open the file or do what ever else you need. Option Explicit Sub OpenFilesUsingFSO() Dim intRow As Integer Dim intLastRow As Integer Dim objFSO As Object Dim objFile As Object Dim objFolder As Object Dim strSearchFor As String Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFolder = objFSO.getfolder("C:\Test") With workbooks("Data.xls") intLastRow = .Sheets("Sheet1").Cells _ (Rows.Count, "A").End(xlUp).Row For intRow = 1 To intLastRow strSearchFor = .Sheets(1).Cells(intRow, 1).Value For Each objFile In objFolder.Files If Right(objFile.Name, 4) = ".xls" Then If InStr(1, objFile.Name, strSearchFor, vbTextCompare) < 0 Then Workbooks.Open (objFile) End If End If Next objFile Next intRow End With Set objFSO = Nothing Set objFolder = Nothing Set objFile = Nothing End Sub I hope this helps you out Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open files from folder by matching name
Hi Steve, Thanks for replying. sorry mate i tried your macro but its
not working. your macro is also opening all the files from folder instead of the given name ones. My macro does work but i need some thing on line Set c = Workbooks("Data.xls").Sheets("Sheet1").Range("A2:A " & _ lastcl).Find(What:=fName, _ LookIn:=xlValues, LookAt:=xlWhole) If i put some kind of critaria in above line where it say "What:=fName" then i got everything working perfect. In first question i just put top half of my macro and therefore i am looking for small macro sultion. it can be solved if lets say i got files names listed in column and i put some formula in next column which should only exract the people name form the file and then i need same formula thing in my macro to do the job. If you have any suggestions please do share. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open files from folder by matching name
Hi Again
First off sorry my first code didn't work for you, not sure why as i tested it and it runs fine for me with the info from your post? Anywho i can't think of a way around it using your proposed method however i have come up with a sort of combo of the two, this time without the FSO but still using the InStr function as i think this is the easiest way to check for a name in a string... I have added a few escape routes to try and get out of the loops to prevent unnecessary cycles, let me know how you get on with the code and of course if you find a way around the problem :) Dim fldrName As String Dim fName Dim lastcl As Integer Dim i As Integer Dim intFound As Integer Sub test() fldrName = "C:\Record" fName = Dir(fldrName & "\*.xls") With Workbooks("Data.xls") lastcl = .Sheets("Sheet1").Cells _ (Rows.Count, "A").End(xlUp).Row Do While fName < "" For i = 1 To lastcl If InStr(1, fName, .ActiveSheet.Cells(i, 1).Value, _ vbTextCompare) < 0 Then Workbooks.Open (fldrName & "\" & fName) intFound = intFound + 1 i = lastcl End If Next i fName = Dir() If intFound = lastcl Then Exit Do Loop End With End Sub Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User selection of folder and open all .xls files within folder | Excel Programming | |||
Open all files in folder automatically using VBA | Excel Programming | |||
Open files in folder - skip if already open | Excel Programming | |||
Open all files in a folder | Excel Programming | |||
open all files in a folder and ... | Excel Programming |