Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.links
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Currency to Text | Excel Worksheet Functions | |||
convert numbers to text | Excel Discussion (Misc queries) | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
Amount or Numbers in Words | New Users to Excel |