Getting information on Excel external data range from VB Script

[mtruman42]

I've got a VB Script that is used to convert Excel workbooks to PDF. The workbooks each have an external data range populated from a CSV file. The data range properties are set to refresh on file open. The script simply opens the workbook, does a RefreshAll to refresh the CSV data and then does a SaveAs PDF. Works fine.

What I want to do is put an indicator in the output that shows the date the data was refreshed (the date of the CSV file). My thought was to just put this into a cell above the data range. I can't figure out how to get the properties of the data range (specifically the CSV file name/path) from VB Script. Another possible solution would be to have a way for the external data range date/version to be populated in a cell on the refresh of the data range (if that is possible). I'm open to other suggestions on how to show the "version" of the external data as well. Any help appreciated.

[Leith Ross]

Hello mtruman42,

Welcome to the Forum!

You should post your code for reference. I need to understand your terminology. You say you have a VB Script. Is this a VBA macro or a VBScript file? What do you mean by an external range?

[mtruman42]

It is a VB Script file. Here it is:

Please Login or Register  to view this content.

The sheets all have a single data range that is created using "Data->From Text" to create a connection to a CSV file. The connection properties are set to refresh on file open so that the latest data from the CSV is imported into the sheet. The CSV is updated daily via a SQL query and the VB Script program is then run to open the workbook, import the latest CSV and save as a PDF. The PDFs are then distributed to the users. Hope that it explains it well enough.

[T-J]

Look at the QueryTable object (Name and Connection Properties) and also (new in Excel 2007) the Workbook.Connections Property.

Please Login or Register  to view this content.

From the QT Connection Property you can get the full path and name of CSV file used as the external data source, then use FSO to get the DateCreated or DateLastModified property, then insert this string in a cell.

[mtruman42]

Thanks T-J, that was perfect. I actually took a bit of a different tack and put this into a VBA script in the workbook that fires on the worksheet change event. It gets the modify time from the .CSV file and puts it in the page footer. Here's the code:

Please Login or Register  to view this content.

Since the connection property is set to refresh on workbook open the data gets updated automatically and the footer gets updated with the update time of the external data. Perfect! Better this way than doing it from the external VB Script that does the publish to PDF since the update date is also reflected if printed directly from Excel.

[mtruman42]

I've unfortunately run into a new problem with this solution. I have a workbook that needs to have subtotals added to the QueryTable range. I added the last two lines (the Range and Subtotal functions) to the original VB macro in the spreadsheet:

Please Login or Register  to view this content.

This works fine when I open the workbook manually. The CSV data refreshes and the subtotals get populated correctly. I can then manually do a SaveAs PDF and the PDF is written correctly with the subtotals included.

The problem comes in when I use my VB Script automation program (see original post) which opens the workbook, refreshes the QueryTable and does a SaveAs PDF. It gets through the call to RefreshAll and then brings up a Visual Basic error dialog box with the following error:

Please Login or Register  to view this content.

If I click the Debug button on the error it brings me to the Selection.Subtotal line in the VB macro in the workbook.

Anyone have any thoughts about what's going on here? I've been tearing my hair out with this for a couple of days now.

Thanks again for your help.