Search data in variable workbook

[Mihael]

I want to retrieve some cell values from another workbook which each time has a different name. The variable name of the workbook is placed in cell c3 on sheet1. It needs to search for the variable workbook in two different folders and get the data from cell D5.

I tried to do with Vlookup, but with two different folders it's impossible I think. The second option would be to do this in VBA, but I'm not so familiar with this..

Could someone help me with this?

 

[pmich]

Some example of available data and expected data will be of help for experts in this forum to give a solution.

 

[Mihael]

Ok, so this is the case:

I have to search a workbook which is called "10777498_ep_ac.xls" this workbook is to be found in one of the two folders (never at both):

folder 1: "J:Kwaliteit Helmond/Final Inspection Reports/10099/"
folder 2: "J:Kwaliteit Helmond/Final Inspection Reports/10233/"

From the given workbook that is to be found in one of the two folders, I have to retrieve the cell value in sheet "calypso" from the cell C4. This value needs to be placed in the current workbook (where the macro is started) in sheet "retrievedValues" in cell D5.

 

[pmich]

Place the following Code in a MODULE in VB Editor.

Function FileExists(FileName As String) As Boolean
 FileExists = (Dir(FileName) > "")
End Function

Place the following code in VB Editor in UserForm under a command button 'cmdRetrieveDataAndPaste':

Private Sub cmdRetrieveDataAndPaste_Click()
 Dim DataWkBk As Workbook
 Dim DataWkBkName As String
 Dim FolderOne As String
 Dim FolderTwo As String
 Dim SrcShtName As String
 Dim SrchVal As String
 Dim TargetShtName As String
 Dim DataWBkPathAndName As String


DataWkBkName = "SourceWorkBook.xlsx"
FolderOne = "D:\MyFolder1\"
FolderTwo = "D:\MyFolder2\"
SrcShtName = "SourceSheet"
TargetShtName = "TargetSheet"
DataWBkPathAndName = FolderOne & DataWkBkName


If FileExists(DataWBkPathAndName) Then
 Workbooks.Open DataWBkPathAndName
 Sheets(SrcShtName).Activate
 SrchVal = Range("C4").Value
 ThisWorkbook.Sheets(TargetShtName).Range("D5").Value = SrchVal
 MsgBox "File is in FolderOne!"
Else
 DataWBkPathAndName = FolderTwo & DataWkBkName
 If FileExists(FolderTwo & DataWkBkName) Then
  Workbooks.Open DataWBkPathAndName
  Sheets(SrcShtName).Activate
  SrchVal = Range("C4").Value
  ThisWorkbook.Sheets(TargetShtName).Range("D5").Value = SrchVal
  MsgBox "File is in FolderTwo!"
 Else
  MsgBox "File Not in both Folders!"
 End If 'FileExists(FolderTwo & DataWkBk)
End If 'FileExists(FolderOne & DataWkBk)
End Sub

 

[Mihael]

Thank you, what is the purpose of the module? And how do I activate the UserForm? Do I need to start the macro, or the UserForm?

 

[pmich]

Open an excel workbook which has the sheet 'TargetShtName'.
Press Alt+F11 to open Visual Basic Editor.
Click Insert, module from Insert menu.
Copy the above code entitled 'Function FileExists' and Paste it in the code window of the module.

Click Insert, Userform.
Rename for your UserForm as frmMyForm.

In the ToolBox, click Command Button.
If the ToolBox does not appear automatically, click View, ToolBox.

Click Command Button and rename it as 'cmdRetrieveDataAndPaste'
Double Click this Command Button.
In the Editor, 'cmdRetrieveDataAndPaste_Click' will be opened.
Paste here the code 'cmdRetrieveDataAndPaste_Click' given above.

Double Click on the name frmMyForm.
This will show the the form and the command button.
Now Click RUN Command or click F5.

The Programme will run.
You wanted to pull one data and paste it in a cell.
That will be done.

 

[Mihael]

Thank you for the explanation!

 

[pmich]

You are welcome! But your need was just to copy and paste one cell data. Anyway, hope you will be able to edit the code according to your other needs. I am still a learner. The Experts in this forum have given me valuable guidance. I am happy that my code was of some help to you.

 

[Mihael]

Your code helped my company to satisfy one of our customer needs! Good work

 

[pmich]

Happy to be of some help.