How to find something in another file, witch is not open HELP ME!!

  • Thread starter Thread starter Andrzej
  • Start date Start date
A

Andrzej

For example.
I have two files: p1.xls and p2.xls
In p1.xls in unknown Cells value = "AAA" or differend string.. and this file
isn't open.

and I would like to write in to the file p2.xls in to ActiveCell something,
but only if i find in file p1.xls lalue "AAA" .

You know what I want isn't it?
I hope so.
 
Hi Andrzej

Are these your actual file names, or is one a name, and the other could be
any file?
Are you satisfied that the active cell in P2.xls will always be correct?
You need VB code to open and search p1.xls for the phrase AAA, and code to
thenwrite in the activecell of p2.xls, provided that AAA is found
 
Ok

Do you know that VB code??


U¿ytkownik "Kassie said:
Hi Andrzej

Are these your actual file names, or is one a name, and the other could be
any file?
Are you satisfied that the active cell in P2.xls will always be correct?
You need VB code to open and search p1.xls for the phrase AAA, and code to
thenwrite in the activecell of p2.xls, provided that AAA is found
Click to expand...
 
Hi Andrzej

I take it your answer to my questions was "yes". If so, open p2.xls,
press<Alt><F11>, click on Insert|Module, and paste the following code there:
Obviously, you must replace my C: path with your own. Also, you must insert
the code for what you want to insert in the active cell (replace "asdf")
Sub FindIt()
Workbooks.Open Filename:= _
"C:\Documents and Settings\J Kasselman\My Documents\p1.xls"
Dim notthere As Range
Set notthere = Cells.Find(What:="AAA", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If notthere Is Nothing Then
MsgBox "AAA was not found"
ActiveWorkbook.Close
Exit Sub
Else
Cells.Find(What:="AAA", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveWorkbook.Close
ActiveCell.Formula = "asdf"
End If
 
Hi Kassie,
thank for answer

I wanted something like this, but not to end.
Maybe I write a bit more about my project.
In my second file (p2.xls) I have button. This his code:
Private Sub CommandButton2_Click()
Dim Cecha As String

Cecha = InputBox("Podaj nazwe cechy (Enter the name)", "Wprowad¼ warto¶æ
(Enter value)")
If Cecha = "" Then Exit Sub

'--- and in this place I need VB code , which search p1.xls

IF will found in any cell value with variable Cecha THEN write in ActiveCell
(in p2.xls) value Cecha

' Do you know Polish language? :)

ELSE MsgBox ("Przykro mi, ale (Sorry, but) " & Cecha & " nie znaleziono (was
not found)")

End If
End Sub

I hope so, we understood each other now. Please help me if you can.
 
)Hi Andrzej

My Polish is non existent. I do not have the code to recreate some of your
characters. Please correct the ommissions (1/4 after Wprowad and special
characters after warto! For the rest, I am sure this is what you were
looking for!

Sub Macro3()
Cecha = InputBox("Podaj nazwe cechy", "Wprowad warto")
If Cecha = "" Then Exit Sub
Workbooks.Open Filename:= _
"C:\Documents and Settings\J Kasselman\My Documents\p1.xls"
Dim notthere As Range
Set notthere = Cells.Find(What:=Cecha, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If notthere Is Nothing Then
MsgBox "Przykro mi, ale" & Cecha & "nie znaleziono"
ActiveWorkbook.Close
Exit Sub
Else
Cells.Find(What:=Cecha, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveWorkbook.Close
ActiveCell.FormulaR1C1 = Cecha
End If

End Sub
 
Back
Top