I have problems with the use of the Workbook Connections.

[Colvic]

Workbook Connections retrieves data from the server, but this data will be prepared twice per hour with a fixed time.
It takes two to five minutes.

If Excel try and retrieve the data in this period, I get the following error message:
A formula in this work sheet contains one or more invalid references.
Verify your formulas That Contain a valid path, workbook, range name, and cell reference.

So how can this be avoided?

One solution might be to let Excel walk into a error routine with a loop of at least six to seven minutes.
But where should this routine be?

Another solution is to stop the Workbook Connections for the time the data is prepared. How can I do this using VBA?

Thanks in advance

 

[SkipVought]

hi,

Please explain in greater detail

1) how you created your connection/query

2) what data source you are connnecting to and

3) the method/code that you are using to retrieve the resultset.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Colvic]

Hi,

The data is prepared in a base on the server twice every hour (real time) and
this takes about. two to five minutes. The data are not available in that period.

Excel spreadsheet is located locally on my computer and retrieves the data using the Data / Connections.

The parameters for the connection to the base located on the server is located here.

Refresh is set to once every minute, to test.

Every time Excel is trying to retrieve data in the period in which the data is prepared in the database on the server,
Excel workbook will stop and give error message when trying to retrieve data.

The error message is:
A formula in this work sheet contains one or more invalid references.
Verify your formulas That Contain a valid path, workbook, range name, and cell reference.

This is because there is no data to retrieve before the preparation is ready in the database on the server.

I see two solutions:

1. Setting time in Excel when the data can be retrieved. Set so that it does not collide with
preparation of the database on the server.
How can this be made?

2. Error routine that slows Excel but approx. 5 minutes when this error comes,
and then start up again.
How and where to add such a routine?


Hope this is more explanatory.

 

[SkipVought]

You really answered none of my questions specifically! Generalities of your information will get you general answers.

Generally, you can use the On Error Resume Next statement then attempt your query and then test the Err object in a loop.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]