Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
file browser window | Excel Discussion (Misc queries) | |||
Opening File Browser | Excel Programming | |||
file browser when a cell in selected | Excel Programming | |||
Prompt user to select file with default file selected dialog | Excel Programming | |||
Prompt user to select file with default file selected dialog | Excel Programming |