Automatic Function Result Update when using A VLOOKUP on another F
 

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

Automatic Function Result Update when using A VLOOKUP on another F
 

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

Automatic Function Result Update when using A VLOOKUP on another F
 

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:
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.a...links/200608/1

Automatic Function Result Update when using A VLOOKUP on another F
 

Clara via OfficeKB.com wrote:
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