Streamlining Data Access

How often do you locate meaningful, useful, columnar data on the internet and then painstakingly copy, paste, and reformat it to meet your document needs?

How often do you search your local hard drive and all available network shares to find approved data or values for use in other documents and forms? How frequently is that needed data only available on a printed page? Worse than that, how often have you located what looked like reliable data only to find later that it was orphaned in the dark past and is no longer accurate? That means you either transcribe those characters into your form (and redo that each and every time the data changes) or just give up on your project altogether (like that can ever happen!).

What if you could have an always up-to-date data source imbedded in your desktop forms and documents? A data collection with a single, accurate source you can always rely upon.

Well, a Web Query might just be your solution and new best friend.

Producing consumable data

It all begins by creating a single source document to contain consumable data. This is the Master Data and as such, it is owned and maintained by a single department or person. It is the Data Source for a web page that provides a manageable collection of that same data. There can never be a dispute over the accuracy of this data.

The web page in the image above is populated with data from an Excel spreadsheet. If there are any changes to the spreadsheet, they are reflected on the web page.

With the appropriate web page coding and an accessible data source (the Excel file) the Excel data is always available, no matter how the user is connected, as long as they can reach the website hosting the Master Data.

Notice how the primary data, the five columns in the canary yellow section, are organized in a grid? This makes all that data available to an Excel Web Query.

Making the Connection

Once we know there is accurate, reliable, up-to-date data on the website, we can connect to it and reuse it in our document.

In Excel, we do that using a Web Query.

Begin by locating the data source for your web query using your web browser. You are mainly interested in the detail and quality level of the data.

Next open the Excel file that will be using this data. It is a good idea to bring the web query data into its own sheet or tab in the Excel file and then use it as an internal data list or source in other tabs of the file.

Preparing the New Data

You can add other features or formulas within the receiving spreadsheet to make the best use of this data once the linked, web-based data is available within the spreadsheet or form. An example would be, adding a formula that builds a lookup field to the front of the imported data so that other parts of the form or file can identify and extract the correct row and cell for use throughout your file.

Considerations

If you deploy your form pre-populated from the web-based data, and users (or your form or sheet programming) always get the latest refresh when using the form, you have a fairly solid fallback in case the form cannot contact the web query source.

Keeping very large, redundant (because it is already on the web) collections of data in individual files CAN lead to local disk storage issues. Imagine you have a web-based data source consisting of about 23,000 rows with 64 columns of financial or other relatively dense data. Maybe the original Excel data source file behind the web data is about 200KB in size. If that collection is loaded into 390 forms stored locally and every new form saves the entire collection again and again, there will be an incredible amount of duplication in your file system.

So the option to persist the web query data in your local form versus getting fresh data that isn't stored should be considered in most cases. This also might lead to creating a more compact set of web query targets.