Search multiple columns in another Workbook for value

[lkelsen]

Hi team,

I currently have a Project Register spreadsheet which auto populates all project documents from within a project folder in windows.

One type of document is an excel workbook called estimates which contains an estimate number, I require my workbook "9876 - Project Register.xlsm" worksheet "Estimates" on 'workbook open' to look into the workbook "9876-EST-001 - Rev C - Luke.xls" worksheet 'Proj Man' and search for the cell which contains the word "Estimate No" (which resides in G4) and pull the estimate number in the cell to the right (H4) and place it in a specific cell (cell B4) in the "9876 - Project Register.xlsm".

I have tried VLOOKUP but this can only search across a single column, also looked at INDEX and MATCH combined but have not been able to come up with a satisfactory result.

My system is Windows 7
Office 2010
VBA 7

VBA Experience = Beginner.
I have also attached some mock versions of the excel files im working with to give an idea of the layout etc..

Cheers,

Luke

13840
13841

[SamT]

See Post # 16 in this thread: Excel Macro - Copying data from one workbook to another (http://www.vbaexpress.com/forum/showthread.php?53035-Excel-Macro-Copying-data-from-one-workbook-to-another)

I am curious, if you know exactly where the Cell is, why do you want to search for it?

[lkelsen]

See Post # 16 in this thread: Excel Macro - Copying data from one workbook to another (http://www.vbaexpress.com/forum/showthread.php?53035-Excel-Macro-Copying-data-from-one-workbook-to-another)

I am curious, if you know exactly where the Cell is, why do you want to search for it?

Hi SamT, only because there are a few different layouts of our Estimates Template within the company.

One would logically standardize them all but there are way to many existing copies in our hundreds of projects to go through and make them all the same. Also for future proofing, if someone changes the layout again at a later date and the location of the "Estimate No" cell changes then my register will not find it.

Cheers.

[SamT]

You have to search many workbooks.
We can code to choose any book on any drive, but, if you want to datamine many books at once, we need more info. Note: Programmers only count with three numbers; Zero, one and many. LOL We use indexes for every thing else.
Are the sheet names you need to search in the same name across all books or is there at least come common theme to all the sheet names?

We can't just write code for one specific book ans Sheet, you might just as well copy it by hand.

[lkelsen]

You have to search many workbooks.
We can code to choose any book on any drive, but, if you want to datamine many books at once, we need more info. Note: Programmers only count with three numbers; Zero, one and many. LOL We use indexes for every thing else.
Are the sheet names you need to search in the same name across all books or is there at least come common theme to all the sheet names?

We can't just write code for one specific book ans Sheet, you might just as well copy it by hand.

SamT, the sheet name always remains the same.

The Project register has other code which searches the project folder and its sub folders for a files (Estimates, Reports etc.) where its name matches a certain string criteria, and then takes that file name and populates the columns in the project register accordingly, this means the project manager does not have to manually update the project register and when ever the register is opened it searches for all specified files as per the vba code. I have included the code below to give you context of what the code does:



Private Sub Workbook_Open()
Dim i As Integer

Application.AutoCorrect.AutoFillFormulasInLists = False
fPath = """" & Application.ActiveWorkbook.Path & """"


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ESTIMATES (Sheet2) '''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


' Get name of each file
sName2 = Split(Replace(Replace(CreateObject("wscript.shell").exec("cmd /c forfiles /P " & fPath & " /S /M *-EST-*.xl?? /c ""cmd /c echo @fname """).stdout.readall, Chr(32) & Chr(34), ""), Chr(34), ""), vbCrLf)
Sheet2.Cells(4, 3).Resize(UBound(sName2)) = Application.Transpose(sName2)


' Get name of each file + extension
sNameExst2 = Split(Replace(Replace(CreateObject("wscript.shell").exec("cmd /c forfiles /P " & fPath & " /S /M *-EST-*.xl?? /c ""cmd /c echo @file """).stdout.readall, Chr(32) & Chr(34), ""), Chr(34), ""), vbCrLf)
Sheet2.Cells(4, 9).Resize(UBound(sNameExst2)) = Application.Transpose(sNameExst2)




' Get path of each file
sPath2 = Split(Replace(Replace(CreateObject("wscript.shell").exec("cmd /c forfiles /P " & fPath & " /S /M *-EST-*.xl?? /c ""cmd /c echo @path """).stdout.readall, Chr(32) & Chr(34), ""), Chr(34), ""), vbCrLf)
Sheet2.Cells(4, 10).Resize(UBound(sPath2)) = Application.Transpose(sPath2)




' Doc Number - Get cell value from estimate files
Worksheets(2).Activate
Range("K5").Select


Do Until IsEmpty(ActiveCell.Offset(, -8))
ActiveCell.Value = "='" & Left(ActiveCell.Offset(, -1).Value, Len(ActiveCell.Offset(, -1).Value) - Len(ActiveCell.Offset(, -2).Value)) & "[" & ActiveCell.Offset(, -2).Value & "]Summary'!$I$4"
ActiveCell.Offset(1, 0).Select
Loop
Worksheets(2).Activate
Range("A5").Select

Do Until IsEmpty(ActiveCell.Offset(, 2))
ActiveCell.Value = "=TEXT(" & ActiveCell.Offset(, 10) & "," & Chr(34) & "000" & Chr(34) & ")"



ActiveCell.Offset(1, 0).Select
Loop




' Revision - Get cell value from estimate files
Worksheets(2).Activate
Range("B5").Select


Do Until IsEmpty(ActiveCell.Offset(, 1))
ActiveCell.Value = "='" & Left(ActiveCell.Offset(, 8).Value, Len(ActiveCell.Offset(, 8).Value) - Len(ActiveCell.Offset(, 7).Value)) & "[" & ActiveCell.Offset(, 7).Value & "]Summary'!$I$5"
ActiveCell.Offset(1, 0).Select
Loop


' Date - Get cell value from estimate files
Worksheets(2).Activate
Range("D5").Select


Do Until IsEmpty(ActiveCell.Offset(, -1))
ActiveCell.Value = "='" & Left(ActiveCell.Offset(, 6).Value, Len(ActiveCell.Offset(, 6).Value) - Len(ActiveCell.Offset(, 5).Value)) & "[" & ActiveCell.Offset(, 5).Value & "]Summary'!$I$6"
ActiveCell.Offset(1, 0).Select
Loop


' Author - Get cell value from estimate files
Worksheets(2).Activate
Range("E5").Select


Do Until IsEmpty(ActiveCell.Offset(, -2))
ActiveCell.Value = "='" & Left(ActiveCell.Offset(, 5).Value, Len(ActiveCell.Offset(, 5).Value) - Len(ActiveCell.Offset(, 4).Value)) & "[" & ActiveCell.Offset(, 4).Value & "]Summary'!$G$7"
ActiveCell.Offset(1, 0).Select
Loop

End Sub


So to sum it up, the sheet is always called "Summary" in a project estimates excel workbook and the cell is always named "Estimate No" within this sheet. Please ignore the other worksheets if I left any in either workbook.

Cheers.

[SamT]

This is the Document information table from your 9876-EST-001 - Rev C - Luke.xlsx.



PROJECT COST ESTIMATE - DETAIL SHEET


Project No.



Project :




Estimate No.
1


Cat:
Project Management



Revision.
C





Sheet No.
1 of 1
Date.
24-Jun-15





Originator:

???






Checked By:

???



IF I understand, you are saying that the actual location on the sheet may vary a bit, but it always looks like this.

Just to give some context, this is the top few rows from the Report Sheet of your 9876 - Project Register.xlsm


Report Register














Doc Number
Revision
Description
Date
Author
Recipient


8675-R-001
0
Pigging Report Log - Guage Run
4/13/2015
KO
Mx


8675-R-002
0
Pigging Report Log - Metal Loss Intelligent Pig Run
4/14/2015
KO
Mx


8675-R-003
0
Pigging Report Log - Caliper & Mapping Intelligent Pig Run
4/14/2015
KO
Mx



Finally, here are the top few rows of the Estimates Sheet you want the Estimate Number placed in, specifically, you say that you want it put in the Revision Column


Estimates Register














Doc Number
Revision
Description
Date
Author
Recipient



























Can you see where I am having a little cognitive dissonance here? It seems to me that there is a distinct relationship between the File name and the Doc. number; i.e. Blah-blah-001. Not to mention that you say you want the Estimate Number in the Revision Column. BTW, the sheet in the *EST* book is named Proj Man, not Summary, which doesn't matter as there is only one sheet.

I can accept that you just want the Estimate number somewhere in your purview, but we can easily insert and label an Estimate Number column into the Estimates sheet, if you want.

Now I have to speculate.
Since the project Register and the Other book have the same number in their name, the Doc Number will only vary by the last three numbers, which is also how the *EST* file name differs from day to day.

That there is a high possibility that you will receive *EST* files with the same Estimate number and different Revision letters.

Now, I am past speculation and well into fantasy.
You want every Registry book, when opened, to search a certain folder for any file(s) with the same numerical prefix as the Registry, that are not listed by Doc Number, and extract the indicated data from them into the Estimates sheet. That you would really prefer the estimates sheet to look like this.


Estimates Register
















Doc Number
Estimate No.
Revision
Description
Date
Author
Recipient


9876-EST-001
1
A

1 January 2015
Originator
Checked By


9876-EST-002
1
B

17 March 2015
KO
Mx


9876-EST-003
2
A

4-Jul-15
KO
Mx




Yeah, we can do that. With a little cooperation from you.

On the other hand, you may want the Description to say "Estimate number is " & (EstimateNumber)

[lkelsen]

SamT, Yup you have understood correctly and yes the actual location may vary slightly but the information always remains the same.



Can you see where I am having a little cognitive dissonance here? It seems to me that there is a distinct relationship between the File name and the Doc. number; i.e. Blah-blah-001. Not to mention that you say you want the Estimate Number in the Revision Column. BTW, the sheet in the *EST* book is named Proj Man, not Summary, which doesn't matter as there is only one sheet.

My apologies, don't know what I was thinking? The Revision number is what I want to retrieve from the *EST* book and put into the Revision column of the *Project Register*.
Yes, ignore the fact that the sheet in the *EST* book is named Proj Man, i grabbed the wrong sheet when whipping up that example but the top section with the information I have shown remains consistent through all sheets in the *EST* book.


I can accept that you just want the Estimate number somewhere in your purview, but we can easily insert and label an Estimate Number column into the Estimates sheet, if you want.

Sorry not quite sure what you mean here Sam?



Now I have to speculate.
Since the project Register and the Other book have the same number in their name, the Doc Number will only vary by the last three numbers, which is also how the *EST* file name differs from day to day.

That there is a high possibility that you will receive *EST* files with the same Estimate number and different Revision letters.

Now, I am past speculation and well into fantasy.
You want every Registry book, when opened, to search a certain folder for any file(s) with the same numerical prefix as the Registry, that are not listed by Doc Number, and extract the indicated data from them into the Estimates sheet. That you would really prefer the estimates sheet to look like this.


Estimates Register
















Doc Number
Estimate No.
Revision
Description
Date
Author
Recipient


9876-EST-001
1
A

1 January 2015
Originator
Checked By


9876-EST-002
1
B

17 March 2015
KO
Mx


9876-EST-003
2
A

4-Jul-15
KO
Mx




Yeah, we can do that. With a little cooperation from you.

On the other hand, you may want the Description to say "Estimate number is " & (EstimateNumber)

Yes your speculation is accurate except that the EST document number will remain the same and the revision will change as the document is changed or revised, there will only be a new EST document number when a new EST (Estimate) is required for a totally different costing for the project.

Here is an example,

For Project 0001 - Pipeline Remedial Works we may need to price an Excavator to reveal a pipeline, we would generate say 0001-EST-001 this document may get reviewed and changed various times, then we may require a further two weeks along, some extra pipe repairs which were unknown and require more supplied so need to generate another EST so this would become 0001-EST-002 and so on and as you can imagine with a large project there would be many EST files.

I hope this clarifies my intentions, I apologize as I am not the most methodically minded type.

Cheers,

Luke

[SamT]

For Project 0001 -Pipeline Remedial Works, do you want 0001-EST-001 and 0001-EST-002 information recorded in the same "0001-Project Register.xlsm" worksheet "Estimates?"

[lkelsen]

For Project 0001 -Pipeline Remedial Works, do you want 0001-EST-001 and 0001-EST-002 information recorded in the same "0001-Project Register.xlsm" worksheet "Estimates?"

Yes so the project will be a folder in windows where the parent is for argument sake "0001-EST-002 - Pipeline Remedial Works". The code currently searches this folder and all sub folders within this parent folder for any file containing "-EST-". And to answer your question, Yes it places them all in the same "0001-Project Register.xlsm" worksheet "Estimates". I have successfully been able to do this, its just extracting information from within each document found into the particular columns in the register that I am struggling with.

[SamT]

With FoundDocument
DataDesired = .Cells.Find("Revision").Offset(0, 1).Value
End With

[SamT]

I kinda rewrote most of your code

ThisWorkbook Code

Option Explicit

Private Sub WorkbookOpen()

Application.AutoCorrect.AutoFillFormulasInLists = False
modGetProjectDetails.GetDetails

End Sub

BTW, On the VBA editor Menu >>Tools >>Options >>EditorTtab >> Code Settings Frame, check everything. That is why I prefix sub and function calls with the Module name and a dot. When VBA sees that, it shows a drop down list of what is available in the module. Select one with the mouse or Arrow Keys and press Tab or Enter to have it automatically typed in..

Module "modGetProjectDetails" Code

Option Explicit

Sub GetDetails()
Dim FileNameArray As Variant
Dim FPath As String
Dim fn As Long
Dim AllDetails As Variant
Dim valProjectNo As String
Dim valEstimateNo As String
Dim valRevision As String
Dim valDate As String
Dim valOriginator As String
Dim valCheckedBy As String

Dim EXTBook As Workbook

FPath = ThisWorkbook.Path & "\"

FileNameArray = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
FPath & "*.xl* /b /s").stdout.ReadAll, vbCrLf), ".")

For fn = 0 To UBound(FileNameArray)
Set EXTBook = Workbooks.Open(FileNameArray(fn))
AllDetails = modProjectDetails.GetProjectDetails(EXTBook.Sheets(1))
If AllDetails = False Then
MsgBox "Error Happened"
End If

valProjectNo = AllDetails(ProjectNoindex)
valEstimateNo = AllDetails(EstimateNoindex)
valRevision = AllDetails(Revisionindex)
valDate = AllDetails(Dateindex)
valOriginator = AllDetails(Originatorindex)
valCheckedBy = AllDetails(CheckedByindex)


'Code to apply the above values to your Project registry as desired
'
'
EXTBook.Close
Next fn
End Sub

Module "modProjectDetails" Code

Option Explicit

Public Enum IndicesEstimateDetails
ProjectNoindex
EstimateNoindex
Revisionindex
Dateindex
Originatorindex
CheckedByindex
End Enum


Const DetailsCount = 6
Const strProjectNo As String = "Project No."

Public Function GetProjectDetails(ByRef Sht As Worksheet) As Variant
Dim Cel As Range
Dim Temp(DetailsCount) As String

Set Cel = Sht.Cells.Find("Revision")
If Cel Is Nothing Then
GetProjectDetails = False
Exit Function
End If

With Cel
Temp(ProjectNoindex) = .Offset(0, 1)
Temp(EstimateNoindex) = .Offset(1, 1)
Temp(Revisionindex) = .Offset(2, 1)
Temp(Dateindex) = .Offset(4, 1)
Temp(Originatorindex) = .Offset(-1, 5)
Temp(CheckedByindex) = .Offset(-1, 6)
End With

GetProjectDetails = Temp
End Function

ps: I corrected one typo. After you set the VBA Options, use Debug >>Compile until you find them all.

[lkelsen]

Haha whoa! Thanks SamT, I shall replace my code and see how it goes :thumb

Thank you!

[lkelsen]

SamT, I have set the VBA Options as instructed but the "Compile VBA Project" option is greyed out?

[SamT]

Maybe it is already compiled. Make an immaterial change, say.. Insert a space in a word, then remove the space, and see what happens.

[lkelsen]

Half my problem is not understanding a majority of terms and code which I come across in VBA and on the internet and what it does/how it works, which must be frustrating for people like yourself.

So to confirm what 'Compiled' actually does, if I understand correctly according to a google search, compiling converts VBE into code in which Windows can read? I did indeed make an immaterial change which was unsuccessful in making the "Compile VBA Code" available.

I can execute the code but nothing actually happens within the workbook, are there parts of the code in which i need to re-name to suite my workbook etc?

I have added code into the Module 'modGetProjectDetails' to write the data from all the val** strings into the desired cells in my workbook with no success.

Sorry, when I say i'm a VBA newbie i guess that means even understanding the fundamentals of coding and the logic behind it, so please forgive me if I am asking silly questions or missing an obvious point..

[SamT]

I did indeed make an immaterial change which was unsuccessful in making the "Compile VBA Code" available.

Hmmm... Go back into the Options menu and on the General tab, check the boxes in the "Compile" Frame. While you're there, in the "Error Trapping" Frame, check "Break On all Errors."

Tip. If you put the cursor in a Key Word in the code and press F1, you will get the Help page for that item.

Don't worry, I am still learning VBA for Excel.

[lkelsen]

Hmmm... Go back into the Options menu and on the General tab, check the boxes in the "Compile" Frame. While you're there, in the "Error Trapping" Frame, check "Break On all Errors."

Tip. If you put the cursor in a Key Word in the code and press F1, you will get the Help page for that item.

Don't worry, I am still learning VBA for Excel.

SamT, both boxes were already checked but it did however activate the "Compile VBA Code" option i nthe debug menu, I pressed it and there was no 'error' as such.

Thanks for the tips also :)

Yea I guess there is always something new to learn when it comes to programming.

So an update, the code is not populating my workbook at all. Am I supposed to be using any of my original code or replacing it all completely with your code? I created both Modules as you have named them and placed the corresponding code inside each module. Where specified that I need to apply code to add the val* values to my register I have entered the following code:


'Code to apply the above values to your Project registry as desired '
'
Range("A5").Select
ActiveCell.Value = valEstimateNo
ActiveCell.Offset(, 1).Value = valRevision
ActiveCell.Offset(, 3).Value = valDate
ActiveCell.Offset(, 4).Value = valOriginator

[SamT]

'Code to apply the above values to your Project registry as desired '
'
With Sheets("some Sheet name here").Range("A5")
.Value = valEstimateNo
.Offset(, 1).Value = valRevision
.Offset(, 3).Value = valDate
.Offset(, 4).Value = valOriginator
End With

Assuming that you might want to place information from several EST books on the the same sheet this code will do that


Sub Whatever()
Dim NR As Long 'Next Row

'Blah
'Blah
'Blah


'Code to apply the above values to your Project registry as desired '
With Sheets("Some sheet name here")
NR = Cells(Rows.Count, "A").End(xlUp).Row + 1

With .Cells(NR, 1)
.Value = valEstimateNo
.Offset(, 1).Value = valRevision
.Offset(, 3).Value = valDate
.Offset(, 4).Value = valOriginator
End With
End With

EXTBook.Close
Next Fn
End Sub

Note the dots in the below

With .Cells(NR, 1)
.Value = valEstimateNo
.Offset(, 1).Value = valRevision
.Offset(, 3).Value = valDate
.Offset(, 4).Value = valOriginator
End With
The dot in front of "Cells" tells VBA that those Cells specifically belong to the Obect (a Sheet) named in the previous "With"

The ones in from of "Value" and the "Offset"s, say that those are Properties of the specific Object named in the previous With (a Cell). The relevant End With stops that behavior.

[lkelsen]

Great, understand with the dot stuff thanks!

With the whatever sub or whatever I call it, do I place that in the module 'modGetProjectDetails' module after the Public Function 'End Function'? and call it from within the Public function?

[SamT]

No, that code goes in Sub GetDetails. Compare sub Whatever to GetDetails and insert the differences in GetDetails. The Dim Statement goes at the top with the other Variable Declarations. The rest goes under:

'Code to apply the above values to your Project registry as desired

Be sure to add the sheet name where you are storing the info as indicated. In quotes.

With Sheets("Some sheet name here")

[lkelsen]

Cool have done that but still no result :(

Here is what it looks like now:


Option Explicit
Sub GetDetails()
Dim FileNameArray As Variant
Dim FPath As String
Dim fn As Long
Dim AllDetails As Variant
Dim valProjectNo As String
Dim valEstimateNo As String
Dim valRevision As String
Dim valDate As String
Dim valOriginator As String
Dim valCheckedBy As String
Dim NR As Long 'Next Row

Dim EXTBook As Workbook

FPath = ThisWorkbook.Path & "\"

FileNameArray = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
FPath & "*.xl* /b /s").stdout.ReadAll, vbCrLf), ".")

For fn = 0 To UBound(FileNameArray)
Set EXTBook = Workbooks.Open(FileNameArray(fn))
AllDetails = modProjectDetails.GetProjectDetails(EXTBook.Sheets(1))
If AllDetails = False Then
MsgBox "Error Happened"
End If

valProjectNo = AllDetails(ProjectNoindex)
valEstimateNo = AllDetails(EstimateNoindex)
valRevision = AllDetails(Revisionindex)
valDate = AllDetails(Dateindex)
valOriginator = AllDetails(Originatorindex)
valCheckedBy = AllDetails(CheckedByindex)


'Code to apply the above values to your Project registry as desired
'
'
With Sheets("Estimates")
NR = Cells(Rows.Count, "A").End(xlUp).Row + 1

With .Cells(NR, 1)
.Value = valEstimateNo
.Offset(, 1).Value = valRevision
.Offset(, 3).Value = valDate
.Offset(, 4).Value = valOriginator
End With
End With


EXTBook.Close

Next fn

End Sub

BTW: Should FPath @ line 21 be searching for *-EST-*.xl* not just *.xl* so that it is finding workbooks that contain *-EST-* in the name?

Thanks again SamT, sorry for consuming so much of your time.

[lkelsen]

Hey SamT, FYI I put a 'msgbox(FPath)' at line 19 after 'FPath = ThisWorkbook.Path & "\"' in the Sub GetDetails(). The reason I did this was because I noticed in 'ThisWorkbook', Private Sub WorkbookOpen() did not have an underscore '_' between 'Workbook' & 'Open'.

I first executed the code (Closed & Re-opened workbook) without the underscore and the msgbox did not appear so this was telling me that the WorkbookOpen sub was not even initiating., I then added the underscore and the msgbox did appear so now the Sub is working, but there is still no data populating in my Project Register, have tried all sorts of things like calling 'Sheet1' instead of 'Estimates' etc but to no avail.

Back at work next week Tuesday, so thanks for your help so far and look forward to your reply.

Cheers, Luke

[SamT]

Upload the latest registry book with a sample estimate sheet in it.

[lkelsen]

SamT, here is the Parent folder with all sub-folders, the '9876 - Project Register.xlsm' is in the main parent folder.

There is an *-EST-* file in the parent folder called '9876-EST-001 - Rev C - Luke' and there should be another x3 in the '1 - Estimate and Proposal' folder.


Cheers,

Luke

[SamT]

Nice data (folder) structure. I may steal that. unfortunately , see
SamT will be gone for a while (http://www.vbaexpress.com/forum/showthread.php?53176-SamT-will-be-gone-for-a-while)

[lkelsen]

Nice data (folder) structure. I may steal that. unfortunately , see
SamT will be gone for a while (http://www.vbaexpress.com/forum/showthread.php?53176-SamT-will-be-gone-for-a-while)



Thanks SamT :) more than welcome to use the folder structure.

Oh no, at least you will be upgrading your MS Office suite, all the best of luck with your restoration and builds.

Many thanks for your support so far with my project, this forum has been very constructive for me and tolerant with my lack of experience.

See you before Christmas here's hoping.


Cheers, Luke