File Browser Prompt
 

Hello All,

I am trying to have a file do some lookups off of another file. My problem
is, I need the Macro to prompt me with a file browser and look at the file
that I choose and then do the lookups off that file. The format in the files
will always be the same. Any ideas?

Thanks in advance!
Aaron

File Browser Prompt
 

Dim fName as String, bk as Workbook
Dim rng as Range
fName = Application.GetOpenFileName()
if fName < "False" then
set bk = Workbooks.Open(fName)
End if


set rng = bk.worksheets(1).Cells.Find("ABCD")
if not rng is nothing then
msgbox "ABCD found at " & rng.Address(external:=True)
End if

See the Excel VBA help example for FindNext if you need to find multiple
instances of "ABCD" (as an example).

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hello All,

I am trying to have a file do some lookups off of another file. My
problem
is, I need the Macro to prompt me with a file browser and look at the file
that I choose and then do the lookups off that file. The format in the
files
will always be the same. Any ideas?

Thanks in advance!
Aaron

File Browser Prompt
 

Hi Tom,
I can almost get this to work. Any Ideas? See below.

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

'Here I need it to select the file I started the Macro in.

Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup off of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10],'H:\MAT_MGMT\HP HDD and Tape Folder\Backlog
Report\[HP Backlog Report 01-06-05.xls]Sheet1'!C1:C11,11,FALSE)"

End sub

Thanks again!
Aaron
"Tom Ogilvy" wrote:

Dim fName as String, bk as Workbook
Dim rng as Range
fName = Application.GetOpenFileName()
if fName < "False" then
set bk = Workbooks.Open(fName)
End if


set rng = bk.worksheets(1).Cells.Find("ABCD")
if not rng is nothing then
msgbox "ABCD found at " & rng.Address(external:=True)
End if

See the Excel VBA help example for FindNext if you need to find multiple
instances of "ABCD" (as an example).

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hello All,

I am trying to have a file do some lookups off of another file. My
problem
is, I need the Macro to prompt me with a file browser and look at the file
that I choose and then do the lookups off that file. The format in the
files
will always be the same. Any ideas?

Thanks in advance!
Aaron

File Browser Prompt
 

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

set rng = bk.Worksheest("Sheet1").Range("C1:I11")
'Here I need it to select the file I started the Macro in.
ThisWorkbook.Activate
Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup off of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(0,0,xlR1C1,True) & ",11,FALSE)"

End sub

May work. You might have to put rng.address(0,0,xlR1C1,True) into a string
and put in the single quotes.

--
Regards,
Tom Ogilvy

"Aaron" wrote in message
...
Hi Tom,
I can almost get this to work. Any Ideas? See below.

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

'Here I need it to select the file I started the Macro in.

Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup off
of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10],'H:\MAT_MGMT\HP HDD and Tape Folder\Backlog
Report\[HP Backlog Report 01-06-05.xls]Sheet1'!C1:C11,11,FALSE)"

End sub

Thanks again!
Aaron
"Tom Ogilvy" wrote:

Dim fName as String, bk as Workbook
Dim rng as Range
fName = Application.GetOpenFileName()
if fName < "False" then
set bk = Workbooks.Open(fName)
End if


set rng = bk.worksheets(1).Cells.Find("ABCD")
if not rng is nothing then
msgbox "ABCD found at " & rng.Address(external:=True)
End if

See the Excel VBA help example for FindNext if you need to find multiple
instances of "ABCD" (as an example).

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hello All,

I am trying to have a file do some lookups off of another file. My
problem
is, I need the Macro to prompt me with a file browser and look at the
file
that I choose and then do the lookups off that file. The format in
the
files
will always be the same. Any ideas?

Thanks in advance!
Aaron

File Browser Prompt
 

Hi Tom,

I am getting a error on the line "set rng =
bk.Worksheest("Sheet1").Range("C1:I11")" Error is "Object does not support
this property or method".
Do I need to Dim this as something? Also, How do I put a code in that says
if the file is already open then do not reopen it.

Thanks,
Aaron

"Tom Ogilvy" wrote:

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

set rng = bk.Worksheest("Sheet1").Range("C1:I11")
'Here I need it to select the file I started the Macro in.
ThisWorkbook.Activate
Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup off of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(0,0,xlR1C1,True) & ",11,FALSE)"

End sub

May work. You might have to put rng.address(0,0,xlR1C1,True) into a string
and put in the single quotes.

--
Regards,
Tom Ogilvy

"Aaron" wrote in message
...
Hi Tom,
I can almost get this to work. Any Ideas? See below.

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

'Here I need it to select the file I started the Macro in.

Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup off
of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10],'H:\MAT_MGMT\HP HDD and Tape Folder\Backlog
Report\[HP Backlog Report 01-06-05.xls]Sheet1'!C1:C11,11,FALSE)"

End sub

Thanks again!
Aaron
"Tom Ogilvy" wrote:

Dim fName as String, bk as Workbook
Dim rng as Range
fName = Application.GetOpenFileName()
if fName < "False" then
set bk = Workbooks.Open(fName)
End if


set rng = bk.worksheets(1).Cells.Find("ABCD")
if not rng is nothing then
msgbox "ABCD found at " & rng.Address(external:=True)
End if

See the Excel VBA help example for FindNext if you need to find multiple
instances of "ABCD" (as an example).

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hello All,

I am trying to have a file do some lookups off of another file. My
problem
is, I need the Macro to prompt me with a file browser and look at the
file
that I choose and then do the lookups off that file. The format in
the
files
will always be the same. Any ideas?

Thanks in advance!
Aaron

File Browser Prompt
 

Hi Tom,

I fixed it. I changed
set rng = bk.Worksheest("Sheet1").Range("C1:I11") to Worksheets
and
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(-10,-10,xlR1C1,True) & ",11,FALSE)"

Thanks for your help!!
Aaron

"Tom Ogilvy" wrote:

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

set rng = bk.Worksheest("Sheet1").Range("C1:I11")
'Here I need it to select the file I started the Macro in.
ThisWorkbook.Activate
Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup off of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(0,0,xlR1C1,True) & ",11,FALSE)"

End sub

May work. You might have to put rng.address(0,0,xlR1C1,True) into a string
and put in the single quotes.

--
Regards,
Tom Ogilvy

"Aaron" wrote in message
...
Hi Tom,
I can almost get this to work. Any Ideas? See below.

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

'Here I need it to select the file I started the Macro in.

Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup off
of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10],'H:\MAT_MGMT\HP HDD and Tape Folder\Backlog
Report\[HP Backlog Report 01-06-05.xls]Sheet1'!C1:C11,11,FALSE)"

End sub

Thanks again!
Aaron
"Tom Ogilvy" wrote:

Dim fName as String, bk as Workbook
Dim rng as Range
fName = Application.GetOpenFileName()
if fName < "False" then
set bk = Workbooks.Open(fName)
End if


set rng = bk.worksheets(1).Cells.Find("ABCD")
if not rng is nothing then
msgbox "ABCD found at " & rng.Address(external:=True)
End if

See the Excel VBA help example for FindNext if you need to find multiple
instances of "ABCD" (as an example).

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hello All,

I am trying to have a file do some lookups off of another file. My
problem
is, I need the Macro to prompt me with a file browser and look at the
file
that I choose and then do the lookups off that file. The format in
the
files
will always be the same. Any ideas?

Thanks in advance!
Aaron

File Browser Prompt
 

Yes, Worksheest should be Worksheets

and in the formula, the address should be absolute instead of relative, so
any non-zero number (you chose -10) could be user or use True

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(True,True,xlR1C1,True) & ",11,FALSE)"

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hi Tom,

I fixed it. I changed
set rng = bk.Worksheest("Sheet1").Range("C1:I11") to Worksheets
and
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(-10,-10,xlR1C1,True) & ",11,FALSE)"

Thanks for your help!!
Aaron

"Tom Ogilvy" wrote:

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

set rng = bk.Worksheest("Sheet1").Range("C1:I11")
'Here I need it to select the file I started the Macro in.
ThisWorkbook.Activate
Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup off
of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10]," & _
rng.address(0,0,xlR1C1,True) & ",11,FALSE)"

End sub

May work. You might have to put rng.address(0,0,xlR1C1,True) into a
string
and put in the single quotes.

--
Regards,
Tom Ogilvy

"Aaron" wrote in message
...
Hi Tom,
I can almost get this to work. Any Ideas? See below.

Public Sub Test()

Dim fName As String, bk As Workbook
Dim rng As Range
fName = Application.GetOpenFilename()
If fName < "False" Then
Set bk = Workbooks.Open(fName)
MsgBox fName
End If

'Here I need it to select the file I started the Macro in.

Range("K2").Select
'I need this Formula to change the path were it is doing the Vlookup
off
of.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-10],'H:\MAT_MGMT\HP HDD and Tape Folder\Backlog
Report\[HP Backlog Report 01-06-05.xls]Sheet1'!C1:C11,11,FALSE)"

End sub

Thanks again!
Aaron
"Tom Ogilvy" wrote:

Dim fName as String, bk as Workbook
Dim rng as Range
fName = Application.GetOpenFileName()
if fName < "False" then
set bk = Workbooks.Open(fName)
End if


set rng = bk.worksheets(1).Cells.Find("ABCD")
if not rng is nothing then
msgbox "ABCD found at " & rng.Address(external:=True)
End if

See the Excel VBA help example for FindNext if you need to find
multiple
instances of "ABCD" (as an example).

--
Regards,
Tom Ogilvy


"Aaron" wrote in message
...
Hello All,

I am trying to have a file do some lookups off of another file.
My
problem
is, I need the Macro to prompt me with a file browser and look at
the
file
that I choose and then do the lookups off that file. The format
in
the
files
will always be the same. Any ideas?

Thanks in advance!
Aaron