ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   How to find something in another file, witch is not open HELP ME!! (https://www.excelbanter.com/new-users-excel/26685-how-find-something-another-file-witch-not-open-help-me.html)

Andrzej
How to find something in another file, witch is not open HELP ME!!
 
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.

Kassie
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

"Andrzej" wrote:

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.

Andrzej
Ok

Do you know that VB code??


U¿ytkownik "Kassie" napisa³ w wiadomo¶ci
...
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

"Andrzej" wrote:

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.

Kassie
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 the
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


"Andrzej" wrote:

Ok

Do you know that VB code??


U¿ytkownik "Kassie" napisa³ w wiadomo¶ci
...
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

"Andrzej" wrote:

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.

Andrzej
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.



U¿ytkownik "Kassie" napisa³ w wiadomo¶ci
...
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
the
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


"Andrzej" wrote:

Ok

Do you know that VB code??


U?ytkownik "Kassie" napisa3 w
wiadomo?ci
...
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

"Andrzej" wrote:

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.

Kassie
)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

"Andrzej" wrote:

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.



U¿ytkownik "Kassie" napisa³ w wiadomo¶ci
...
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
the
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


"Andrzej" wrote:

Ok

Do you know that VB code??


U?ytkownik "Kassie" napisa3 w
wiadomo?ci
...
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

"Andrzej" wrote:

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.

Kassie
And define Cecha for best practice, as in your own code. I forgot to define
it.

"Andrzej" wrote:

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.



U¿ytkownik "Kassie" napisa³ w wiadomo¶ci
...
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
the
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


"Andrzej" wrote:

Ok

Do you know that VB code??


U?ytkownik "Kassie" napisa3 w
wiadomo?ci
...
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

"Andrzej" wrote:

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.

Andrzej
No because your code search in p2.
and I wants in p1.. in opened !!!

U¿ytkownik "Kassie" napisa³ w wiadomo¶ci
...
)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

"Andrzej" wrote:

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?a
(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.



U?ytkownik "Kassie" napisa3 w
wiadomo?ci
...
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
the
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


"Andrzej" wrote:

Ok

Do you know that VB code??


U?ytkownik "Kassie" napisa3 w
wiadomo?ci
...
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

"Andrzej" wrote:

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.

Kassie
On my side it searches P1 every time! You may add code to force it to work
with the open file though

"Andrzej" wrote:

No because your code search in p2.
and I wants in p1.. in opened !!!

U¿ytkownik "Kassie" napisa³ w wiadomo¶ci
...
)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

"Andrzej" wrote:

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?a
(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.



U?ytkownik "Kassie" napisa3 w
wiadomo?ci
...
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
the
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


"Andrzej" wrote:

Ok

Do you know that VB code??


U?ytkownik "Kassie" napisa3 w
wiadomo?ci
...
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

"Andrzej" wrote:

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.


All times are GMT +1. The time now is 08:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com