Using VBA to search filenames for namematch then take data from file 3

[LizF]

Please excuse my newbie naivite, but I'm struggling here!
I need to write some VBA code in a client Excel file to search a folder ("server&quot for filenames matching my criteria (e.g filename contains z31245). Then I need to extract data from certain cells in that file and copy the data to my client Excel file. The whole code will be in a loop to repeat this process for each line in the client file (criteria to match filename comes from 1st cell in each line).

How can I tell it to search through the folder until it finds a filename containing e.g. z31245?. Also, do I then have to open the file to pick up the cells I need to copy?

Many Thanks,
Liz

 

[RobBroekhuis]

Use FileSearch to find the files you want. Since you are looking for filenames which CONTAIN (rather than START WITH) a certain string, you'd do best to collect ALL the filenames using

dim FileNames() as string, i as integer
with application.filesearch
.filename="\\server\*.xls"
.execute
if .foundfiles.count>0 then
redim FileNames(.foundfiles.count-1)
for i=1 to .foundfiles.count
FileNames(i-1)=.foundfiles(i)
next i
end if
end with

You need only do this once, before your loop. Now you can look through your array of filenames every time you're looking for the next file. Yes, it's easiest to open the workbook to extract the cell values. Set
application.screenupdating=false
to prevent distracting screen flickering.
Will that help get you on your way?
Rob

 

[SBendBuckeye]

To make it easier to use if you are going to nose around in the locals window, I would suggest the following change:

Dim fs As FileSearch
Set fs = Application.FileSearch

Now you only have a FileSearch object instead of having to open the application object, scroll down to the FileSearch object, etc.

As an aside, I can't call a Public Sub out of the Immediate Window in Excel VBA. If I stick a call in one of the events it will work OK. Any ideas?

Have a great day and Good Luck!

 

[SBendBuckeye]

Hello Liz,

I took the code Rob was kind enough to post above and expanded it to check for matches by building an array of file names and then going down column A until it hit the last row of the spreadsheet. I left a couple message boxes in place so you could watch it work.

If you still have questions, please repost and we'll try to help you. Thanks for the opportunity to work on something like this. I know that I'll be needing to do something very similar soon and now it's most of the way done.

Hope this helps and have a great day!

PS. I put this in the cell change event so it was easy to trigger. You may want to trigger it another way. Either way, the code should still work.

PSS. Due to the way this displays, things wrap funny sometimes. If you just paste the whole thing into a VB editor or WordPad it should reformat itself nicely into something easier to read.

******************* Code *****************************
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Call ProcessFiles
End Sub

Private Sub ProcessFiles()
Dim avarFileNames As Variant 'Array to hold file names
Dim lngColumntToMatch As Long 'Which column should it check

'Build array of File Names using Application.FindFile
Call GetFileNames(avarFileNames)

'Search Column 1 (eg A)
lngColumntToMatch = 1

'Check specified column against array of file names
Call MatchFileNames(avarFileNames, lngColumntToMatch)

End Sub 'ProcessFiles

'If no file names or path passed, setup defaults in code
Private Sub GetFileNames(ByRef avarArray As Variant, _
Optional ostrPath As String, _
Optional ostrFiles As String)
Dim fs As FileSearch
Dim lngX As Integer
Dim strPath As String
Dim strFiles As String

'Use path if given, otherwise default to something here
If Len(ostrPath) Then
strPath = Trim$(ostrPath)
Else
strPath = "H:\Microsoft Excel Data"
End If

'Use files if given, otherwise default something here
If Len(ostrFiles) Then
strFiles = Trim$(ostrFiles)
Else
strFiles = "*.xls" 'Default to Excel files
End If

'Set reference to FileSearch object - not required but
'saves scrolling through Application object if debugging
Set fs = Application.FileSearch

With fs 'Faster and saves having to say fs.lookin, etc
.LookIn = strPath
.Filename = strFiles
'Can use path qualified .FileName if desired
' .Filename = "H:\Microsoft Excel Data\*.xls"
If .Execute > 0 Then 'If anything found process it
'Dimension as ones based to keep life simple
ReDim avarArray(1, .FoundFiles.Count)
For lngX = 1 To .FoundFiles.Count
avarArray(0, lngX) = .FoundFiles(lngX) 'With path
avarArray(1, lngX) = _
StripPath(.FoundFiles(lngX), "\&quot
Next lngX
End If
End With
End Sub 'GetFileNames

'Strip path from a File Name w/ Path and return File Name
Private Function StripPath(pstrFileNameWithPath, _
pstrDelim As String) As String
Dim lngX As Long
Dim lngPos As Long
'In case delimiter is not found set to input parm value
StripPath = pstrFileNameWithPath
'Work from end of string backwards looking for delimiter
For lngX = Len(pstrFileNameWithPath) To 1 Step -1
If Mid$(pstrFileNameWithPath, lngX, 1) = pstrDelim Then
StripPath = Mid$(pstrFileNameWithPath, lngX + 1)
Exit For
End If
Next lngX
End Function 'StripPath

'Check specified Column values against array of File Names
Private Sub MatchFileNames(pvarArray As Variant,
plngColToMatch As Long)
Dim lngRow As Long
Dim lngRows As Long
Dim strFileNameWithPath As String

'How many rows should be checked
lngRows = GetSpreadsheetRows()

For lngRow = 1 To lngRows
With Worksheets("Sheet1&quot.Cells(lngRow, plngColToMatch)
MsgBox "Checking: " & .Text & " Row: " & lngRow

'Does current Column cell text match File Names array
strFileNameWithPath = _
FileNameMatched(.Text, pvarArray)

'No match returns an empty string so check length
If Len(strFileNameWithPath) > 0 Then
MsgBox "Found: " & strFileNameWithPath
'Do something here as you need to
End If
End With
Next lngRow
'Application.ScreenUpdating = False
End Sub

'If name matches array then array element w/ path returned
Private Function FileNameMatched(pstrNameToMatch, _
pvarFilesArray As Variant) As String
Dim lngX As Long
Dim lngPos As Long

'If file name is not matched return an empty string
FileNameMatched = vbNullString

'If spreadsheet names do not include extension add it
If InStr(pstrNameToMatch, ".&quot = 0 Then
pstrNameToMatch = Trim$(pstrNameToMatch) & ".xls"
End If

'Expecting 2 dimenional array, spin it looking for match
'used ones based array because it makes things easier
For lngX = 1 To UBound(pvarFilesArray, 2)

'If file name only matches return file name with path
If UCase$(pvarFilesArray(1, lngX)) = _
UCase$(pstrNameToMatch) Then
FileNameMatched = pvarFilesArray(0, lngX)
Exit For 'Found a match so no need to continue
End If
Next lngX
End Function 'FileNameMatched

'Determine number of rows in requested worksheet - Default
'is ActiveSheet - If another sheet requested, ActiveSheet
'is saved and then reactivated before exiting
Private Function GetSpreadsheetRows(_
Optional oxlExcelWS As Worksheet) As Long
Dim strSheetName As String
'Get lower right hand corner of requested worksheet
If oxlExcelWS Is Nothing Then

'Go to the lower right hand corner of the worksheet
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate

'Return the row we are on
GetSpreadsheetRows = ActiveCell.Row
Else
'Save currently active worksheet
strSheetName = ActiveSheet.Name

'Activate requested worksheet
oxlExcelWS.Activate

'Go to the lower right hand corner of the worksheet
oxlExcelWS.Cells.SpecialCells(xlCellTypeLastCell).Activate

'Return the row we are on
GetSpreadsheetRows = ActiveCell.Row

'Reactivate original worksheet
Sheets(strSheetName).Activate

End If
End Function 'GetSpreadsheetRows

*********************** End Code **************************

 

[RobBroekhuis]

SBB,
The only reason I can think of why you can't run that public sub is that the active workbook, while working in VBE, is a different one than the one containing your sub. Normally, subs run just fine from the immediate window.
Rob

 

[LizF]

Thanks so much both of you! Where textbooks have failed for 2 weeks you guys have come up with the goods in a weekend - what a site. Have a star! (Shame they're not gold stars!!)

I now have some hope that I (zero VBA experience, in fact next to zero programming experience) might have a chance of succeeding with this job I "volunteered" for - wish me luck!

Thanks again,
Liz

 

[SBendBuckeye]

Hey Liz,

Always a pleasure to help. This side is a Godsend all the time for me. How much do you want to know? You mentioned you have almost no programming background. If you want to learn more, I'll try to expand on the examples for you so you can learn. If you're not interested except to get this project done, that's fine too. Just let us know...

Have a great day!

 

[SBendBuckeye]

Hello Rob,

I open up a new spreadsheet in Excel, hit Alt+F11 to get to the VB code editor, select ThisWorkbook out of the Project explorer, right click to view code, select general in the combobox on the upper left, type the following in the code window, go the the immediate window, type glw <enter> and get the message Compile Error, Sub or Function Not defined.

If I put the call inside ThisWorkbook event code it works just fine.

Public Sub glw()
MsgBox &quot;Hello World&quot;
End Sub

Not insurmountable, just irritating.

Have a great day and thanks for your helpfulness not only here but in many threads in this forum. I appreciate those that are willing to share as I have learned much from them.

Have a great day!

 

[LizF]

Morning SBB, and everyone (well, it's afternoon here...)

Thanks for your reply, yes it would be good to see some more code. I know enough to be able to read VBA code and get the basic structure reasonably OK (did a couple of C++ modules at University) but despite ploughing through a couple of textbooks there's a huge amount of functionality in VBA which I don't know about.

In short, I'll be very grateful for anything you share with me!!

Thanks,
Liz

 

[RobBroekhuis]

SBB,
Is there a reason why you're putting this sub in the ThisWorkbook module? General-purpose subs should normally be defined in a regular code module (insert-module), whereas the ThisWorkbook (and sheet) object code pages are meant to contain event handling subs. As you've found, variables and subs declared on the object code pages tend not to be visible from the outside.
Rob

 

[SBendBuckeye]

Hello Rob,

No there is not. Almost all of what I have done to this point has been in Access so I didn't know where to put it. If I put it in a general purpose module will it be tied to the current workbook or will it then be a generic module available to all Excel? And if it is generic to all of Excel, how do you invoke it in a given workbook file or call it from a given workbook's code.

Thanks for the great help you give!

 

[SBendBuckeye]

Sorry, as a further aside, do you recommend any particular technical references? I use Access2000 Developers Handbook on the Access side. If I could find an Office equivalent, I would be thrilled.

Have a great daY!

 

[RobBroekhuis]

Each module is part of a VBAProject. Each workbook has its own VBAProject. I usually add modules by right-clicking on my workbook in the VBE project browser, and choosing Insert-Module. This opens a squeaky-clean new module, ready for new subs. You're probably familiar with Excel's macro recorder (if you're not, you really should be!) - you'll notice that it, too, creates a new module into which to record your macros.
As for a good reference - most people like John Walkenbach's books. I'd go browse around Amazon, find Excel VBA books and read the reviews. There may be others that more closely fit your needs.
Rob

 

[LizF]

Hello again folks,

Thanks to the help received above, things are progressing well with my code. However, I'm stumped again!!

My client Excel file is a large spreadsheet. Each Row contains a number i.e. z12345 along with a load of other information. My code can now pick up the z-number and compare it with a bunch of smaller server files to find the one(s) with the same z-number in their filename. Now I need to copy data from certain cells in that matched server file into cells in the large client spreadsheet, in the row containing that z-number. The columns are named ranges, so is there any way of specifying e.g. for row containing z12345, range &quot;Summary&quot;, copy xyz?? i.e can I use the range as if it were a column to pinpoint a cell I need to copy into?

Example....

Z-number Summary Actions Attendance Cost
z12345 78 86 56 67
z23456 89 69 98 95
z45123
z76124

Thanks in advance...
Liz
%-)

 

[RobBroekhuis]

Liz,
Could you please post this in a new thread? The old thread's subject name really doesn't apply.
Thanks
Rob