File Browser Prompt

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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).
 
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
 
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 said:
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 said:
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


the
files
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
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
:

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


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
Click to expand...
the
file
that I choose and then do the lookups off that file. The format
Click to expand...
in
the
files
will always be the same. Any ideas?

Thanks in advance!
Aaron
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UTF-8 File Opened in Excel from Firefox Browser 1
Help needed 0
How to Export Gmail Emails to PST Format? 6
Need to run a macro on opening a new excel file 0
File name a variable 6
Simple Vlookup Looping 2
Vlookup Loop 1
Export Macro to new Workbook based on Email Address 7

Back
Top