How to do a Vlookup from 1 spreadsheet to another using VBA code.

HI Guys, I have 2 Excel attachments , 1 called "Daily GSFI" and 1 called "WTD Commentary". From the WTD Commentary spreadsheet, I want to "View -> Windows -> Activate "Daily GSFI"Spreadsheet -> Create a range from "A3:I36" called "Region" (with which to do a Vlookup on later) -> View -> Windows -> Open "WTD Commentary" -> put a formula in F45 to F56 -> "=Vlookup(B45, Region, 9, False). How do I do this?
The problem is the "Daily GSFI" spreadsheet has a different ending in the name every day, eg, "Daily GSFI 18112014.xlsm" so it needs to defined in a variable.
If the Filename was not variable, the formula would look like this:
=VLOOKUP(B45,'[DailyGSFI.xlsm]Daily GSFI PL'!$A$3:$I$37,9,FALSE)
WTDCommentary.xlsm
DailyGSFI.xlsm
JustincutAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Hello

Looking at your request and looking at the files either I am totally off or you are mixed in the regions and ranges

1) Create a range from "A3:I36" called "Region"
>>>> In what sheet workbook ? Presume in 'Daily GSFI' if yes then region is "A3:I37" and not 36 Correct ?

2) Open "WTD Commentary" -> put a formula in F45 to F56
>>>> Again if we look at sheet WTD Commentary we have data from F45 to F66 so is this the Range or .... ????

gowflow
0
gowflowCommented:
Furthermore,

3) The file you posted if we are to compare the data between WTD Commentary in Row F45 downward and trying to find these values in sheet 'Daily GSFI'  Col I, then it is almost impossible the one and only amount that correspond is 81,900.00 that is the first item and that we can find in E45 and not F45.

Can you please explain the logic of what you are trying to achieve and please leave the filename issue aside and the possible solutions. As we need to understand first what you want and not how you envision resolving your problem.

Regards
gowflow
0
aikimarkCommented:
You could make a copy of the current daily file as DailyGSFI.xlsm and then use the formula you posted.  This could possibly be done in a batch file that opens/launches the WTDCommentary workbook.  It might be possible to make the copy in VBA code in the Workbook_Open() event in the WTDCommentary workbook.  I'm less sure about the success of the second method.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
This is an example of what the Open event might look like.
Private Sub Workbook_Open()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim strFormulaDate As String
    
    Workbooks.Open "C:\Users\AikiMark\Downloads\Daily GSFI " & Format(Date, "ddmmyyyy") & ".xlsm", False
    Set wkb = Workbooks("WTDCommentary.xlsm")
    Set wks = wkb.Worksheets("WTD Commentary")
    Set rng = wks.Cells.Find("Daily GSFI")
    strFormulaDate = rng.Formula
    strFormulaDate = Mid(strFormulaDate, InStr(strFormulaDate, "Daily GSFI ") + 11, 8)
'    Debug.Print strFormulaDate
    
    wks.UsedRange.Replace "Daily GSFI " & strFormulaDate, "Daily GSFI " & Format(Date, "ddmmyyyy")
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JustincutAuthor Commented:
Hi, this is the logic. I have to do a Vlookup up from the WTD Commentary spreadsheet to update the Book Value of the Companies in Column B. The spreadsheet I do the lookup to is opened via an e-mail and not saved down to any path. Thus, done manually, I have to do a Vlookup to Column D in the GSFI spreadsheet in Column D and go across to the USD MTM Position column. The GSFI PL has a variable ending too so it could have yesterday's date on the end.using VBA from a Macro in the WTD commentary file, I want to create a Range with which to do a Vlookup in the GSFI file so I can see the formulas in the WTD Commentary file instantly by pushing a Macro. Any ideas?
0
JustincutAuthor Commented:
The Range called Region I want in the GSFi file, but it must be recognized in the Vlookup formula in the WTD Commentary file.the formulas go in the F45 Cells downwards so it should look it =Vlookup(B45, Region, 12, False). Ignore the fact they are already populated.
0
JustincutAuthor Commented:
Hi Guys, this line is breaking in the Macro in the "WTD Commentary" spreadsheet.

 ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'[" & myfilename & "]" & MYSHEETNAME & "'!" & myrangename & ",16,FALSE)"

I am getting "Run-time error '1004' Application defined error. Any ideas why?
WTD-Commentary-17112014.xlsm
DAILY-GSFI-PL-20141107.xlsx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.