MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Workbook update issue

  Asked By: Francisca    Date: Jan 11    Category: MS Office    Views: 2181

For some time I have been using a macro (code snippet below) to
automate the updating of my workbooks.

Workbooks.Open "E:\Current Charts\SyntheticOceanTB10YrTNotes.xls",
UpdateLinks: =1
"SyntheticOceanTB10 YrTNotes.xls!RefreshDataWeekly19_23"
Application.Run "SyntheticOceanTB10YrTNotes.xls!RefreshVolOI"
ActiveWorkbook.Close SaveChanges: =True
Workbooks.Open "E:\Current Charts\SyntheticOceanTB30YrTBonds.xls",
UpdateLinks: =1
"SyntheticOceanTB30 YrTBonds.xls!RefreshDataWeekly19_23"
Application. Run "SyntheticOceanTB30 YrTBonds.xls!RefreshVolOI"
ActiveWorkbook.Close SaveChanges: =True

This macro is contained in a "Master Update" workbook which I open to
run and update other workbooks. What the code does is, in the above
case, open a workbook called "SyntheticOceanTB10YrTNotes" and run two
macros contained in this workbook to update two web queries, one each
on sheets called "Barchart" and "VolOI" and close and save the changes
and then repeat the process for the workbook for T Bonds. This process
is repeated for a series of workbooks, in alphabetical order.

For many months this worked without problems but recently I had to
change to a LAN for my internet connection and since then the above
code hasn't worked properly. What appears to be happening is that a
few workbooks update as normal and then the program "freezes," being
unable to complete the web query for the particular workbook that
should be being updated at that moment. Is it possible that being
connected to the internet through the LAN is interfering with the web
query from within the workbook, and if so, can I somehow reconfigure
the web query to avoid this problem in future? Not having to connect
through the LAN is not an option.

An alternative approach might be to alter the code above (introduce
error handling?) so that if the web query fails on any workbook after
a set amount of time or number of attempts the code moves on to the
next workbook and then comes back to the problematic workbook at the
end or perhaps gives a message that the problematic workbook should be
updated manually. My research on the web as to how I might code this
has been fruitless so far.

Any help would be much appreciated.



1 Answer Found

Answer #1    Answered By: Lurleen Fischer     Answered On: Jan 11

well the first thing I would check is which workbooks/queries are not
working correctly and making sure that the new LAN path to access them has
not changed you may have to implement a "file exists" check for error
handling.. the other thing (and I myself haven't done this but from other
questions/networking point of view) there might be proxy server settings or
a firewall that is effecting your code.. again a check for file exist or
if connected might yield the answers .. other than that you may have to talk
to the sys admin to see if the change to LAN is the problem.

Didn't find what you were looking for? Find more on Workbook update issue Or get search suggestion and latest updates.