Automatic Function Result Update when using A VLOOKUP on another F Quicky 17.08.2006 08:53:02 Hi all, I have an excel file A that use a VLOOKUP to retrieve data from another Excel file B. The data retrieved in B file is computed with diffrerent funtions that get data from a database. When I open file A, the data from B is retrieved BUT the functions into B are not recomputed so I retrieve out of data data. Does someone know how to do in order that I get data from file B updated when I open the file A. I do not want to ask the users to first go in file B to update data and then open file A. Thank you for your help, -- Quicky

Re: Automatic Function Result Update when using A VLOOKUP on another F Bill Manville <Bill-Manville[ at ]msn.com> 17.08.2006 23:36:34 The only way you will get file B to contain up to date data is to open it. You could have file A open and close file B on startup. Sub RefreshFromFileB() Dim WS As Worksheet Dim QT As QueryTable Application.ScreenUpdating = False Workbooks.Open ThisWorkbook.Path &"\FileB.xls" For Each WS In ActiveWorkbook.Worksheets For Each QT In WS.QueryTables QT.Refresh BackgroundQuery:=False Next Next ActiveWorkbook.Save ActiveWorkbook.Close False Application.ScreenUpdating = True End Sub Sub Auto_Open() ' update from FileB when I've finished opening. Application.OnTime Now,"RefreshFromFileB" End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup

Re: Automatic Function Result Update when using A VLOOKUP on another F "clara via OfficeKB.com" <u18900[ at ]uwe> 23.08.2006 06:14:08 Hi, it works fine but could it be possible to bypass the password if I've set in file B? Thanks in advance, Clara Bill Manville wrote: [Quoted Text] >The only way you will get file B to contain up to date data is to open >it. You could have file A open and close file B on startup. > >Sub RefreshFromFileB() > Dim WS As Worksheet > Dim QT As QueryTable > Application.ScreenUpdating = False > Workbooks.Open ThisWorkbook.Path &"\FileB.xls" > For Each WS In ActiveWorkbook.Worksheets > For Each QT In WS.QueryTables > QT.Refresh BackgroundQuery:=False > Next > Next > ActiveWorkbook.Save > ActiveWorkbook.Close False > Application.ScreenUpdating = True >End Sub > >Sub Auto_Open() > ' update from FileB when I've finished opening. > Application.OnTime Now,"RefreshFromFileB" >End Sub > >Bill Manville >MVP - Microsoft Excel, Oxford, England >No email replies please - respond to newsgroup -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-links/200608/1

Re: Automatic Function Result Update when using A VLOOKUP on another F Bill Manville <Bill-Manville[ at ]msn.com> 23.09.2006 16:22:12 Clara via OfficeKB.com wrote: [Quoted Text] > it works fine but could it be possible to bypass the password if I've set in > file B? > Sorry for late reply. Sure - you can specify a password with Workbooks.Open: Workbooks.Open ThisWorkbook.Path &"\FileB.xls", Password:="MyPassword" Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup