Excel Options for updating external links

There are many options in Excel that control this depending on the kind of link it is. I’ll cover the usual external links in formulas first, then finish up with the other kinds.

An external link formula shows the full path to the source if that workbook is closed, otherwise it shows only the workbook name in the path. For more information, see the MS article ‘Create an external reference (link) to a cell range in another workbook

      ='F:\Spreadsheets\[LinkSource.xlsx]Sheet1'!$B$3
      ='F:\Spreadsheets\LinkSource.xlsx'!Myrange

You can link to cell addresses or names; obviously named ranges are safer because addresses can be affected by insertions or deletions in the source worksheet. To find where links are used in a workbook, search in formulas for “.xl*!”; that is, an Excel file name followed by an exclamation mark.

As well as intentionally created links you enter in formulas, links can be created by Excel when you copy sheets from one workbook to another, and those sheets contain references to other sheets either obviously in formulas or less obviously in range names. If you get a prompt to update links when you open a workbook, but you cannot find any cells containing links, the link is probably in a name. Use the Name Manager (either Excel’s own or the free third-party JKP Name Manager ) to check all names for links, particularly phantom links which are not used but clutter up the list.

To control whether values are updated when the workbook containing the links (the ‘target’ workbook) is opened, use the Edit Links button in the Data tab of the Ribbon. Click the ‘Startup Prompt’ button, and select one of these three:

1: Let users choose to display the alert or not

Now, what happens at startup depends on another setting:

Options > Advanced > General: Ask to update automatic links.  (VBA: Application.AskToUpdateLinks). If true, the user is alerted and can choose whether to update or not. If false, the links are updated.

2: Don’t display the alert and don’t update automatic links

Links are not updated at startup, regardless of the setting of Application.AskToUpdateLinks.

3: Don’t display the alert and update links

Links are updated at startup, regardless of the setting of Application.AskToUpdateLinks.

In VBA, ThisWorkbook.UpdateLinks has the values 1,2, or 3 above.

 

If the source workbooks are open at the same time as the target, the formulas are part of the recalculation chain as normal. The link values from closed files are not refreshed in a recalculation. Excel provides no way to automatically refresh the values of external link formulas to CLOSED workbooks. To manually update values at any time while using a workbook, use Edit Links, and for each link click ‘Update Values’. You can add a macro that calls ActiveWorkbook.UpdateLink. To update all links, pass it the array of link sources as follows:

ActiveWorkbook.UpdateLink _
 Name:=ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)

If you omit the parameter, by default Excel uses xlLinkTypeExcelLinks. The other type of XlLInkType is xlLinkTypeOLELinks, see below for those.

If any links are bad, that raises an error. To update them one by one and trap the error:

Sub UpdateAllLinks()
   UpdateLinks xlLinkTypeExcelLinks
   UpdateLinks xlLinkTypeOLELinks
End Sub

Sub UpdateLinks(LinkType As XlLinkType)
Dim vSource As Variant
Application.DisplayAlerts = False ' comment out to see the prompt for missing link sources
For Each vSource In ActiveWorkbook.LinkSources(LinkType)
   On Error Resume Next
   Debug.Print vSource
   Application.DisplayAlerts = False
   ActiveWorkbook.UpdateLink Name:=vSource, Type:=LinkType
   If Err <> 0 Then
      Debug.Print "^Error "; Err; Err.Description
   End If
Next vSource
Application.DisplayAlerts = True
End Sub

You could call that macro in the Worksheet_Calculate event to update link values every time a recalculation happens. That will slow down performance, particularly for links over a network rather than to local drives.

To automate the calling of this macro at regular intervals, you can use the .Ontime method. Again, be aware when you do this of the length of time it takes to update the values.

In the ThisWorkbook class module:

 

Sub Workbook_Open()
      Auto_UpdateExcel Links
End Sub

In a standard module:

Sub Auto_UpdateExcelLinks()
      UpdateExcelLinks
      Application.OnTime DateAdd("s",10,Now()), "Auto_UpdateExcelLinks")
End Sub

 

Other ways to link to external data

OLE: Object Linking and Embedding can be used for other Office applications such as linking Word or PowerPoint to Excel. You could link Excel to Word if you like, odd though it looks. Copy the data in Word then in Excel Paste Special, Link, and select the format you want. ‘HTML’ suits Word tables, ‘Microsoft Word Document Object’ inserts an object with the formatting as in Word.

The Edit Links dialog allows you to choose Automatic or Manual update only for OLE links. Excel links always have the Manual option button greyed out. If it is automatic then it is refreshed live in Excel as soon as the source document is changed, even without saving the document. If it is manual then it can be updated through Edit Links, select the link, click Update Values.

The Excel setting ‘Options > Advanced > When calculating this workbook: Update links to other documents’ controls the updating of OLE links only. (VBA: ThisWorkbook.UpdateRemoteReferences ) When unchecked, OLE links do not update, even if you select ‘Update Values’ in the Edit Links dialog for that link.

 

Connections: set up by the Get External Data button. These create queries which are refreshed by the Refresh button. You can link to text files (eg CSV), other workbooks, databases, and web pages.  For further details see MS support and many other websites:

https://support.office.com/en-us/article/Create-edit-and-manage-connections-to-external-data-89d44137-f18d-49cf-953d-d22a2eea2d46

 

RTD / DDE: Real Time Data and Dynamic Data Exchange. These are ways of getting data from COM instances on a server. For more information, see MS support and the Wilmott forums.

https://support.microsoft.com/en-us/kb/289150

 

Macros: Data can be read and written by VBA code with Workbooks.Open, Querytables, ADO and SQL. Worksheet shapes can contain links to macros in other workbooks, so clicking them may cause Excel to attempt to open those other workbooks.

 

Posted in Excel/VBA, Uncategorized | Tagged | Leave a comment

A first look at the FUSE spreadsheet corpus

Following on the first paper by Titus Barik et al
static.barik.net/barik/publications/msr2015/PID3640389.pdf
and some work by Mark Townsend analysing the last row and column used in each file
http://markstownsend.com/what-are-all-the-rows-for/index.html

I downloaded the 7GB of 249,376 files and did some summary analysis of them and the VBA.

The top domain is .org (29.5%),followed by .gov (27.7%)
That’s because almost half the files are from one web site – triathlon.org. They look like files that were filled in for reporting purposes, and so contain no formulas.
Files which are simple web report downloads or automatically generated (eg quickfacts.census.gov) then they were not user-created spreadsheets at all, and so of no interest to me.
5,600 have “SpreadsheetGear” as a write access user, all from worldbank.org.

So most of the FUSE spreadsheets are of no interest to me in formula error research.
There are no .xlsm files although a simple google finds 106,000.

Of the 5037 web hosts, http://www.triathlon.org accounts for 106328 files, or 43% of the total.
http://www.triathlon.org 106328
quickfacts.census.gov 47025
theahl.com 10350
The top 3 account for 66% of the files, the top 50 (1% of the hosts) have 87% of the files.
So it’s pretty skewed towards a few domains.

The POI analysis can not handle Biff5 files, but they can be processed in Excel if you relax the File Block settings.
The top 80% of files have no formulas or very few, again because they are really data files.
12854 (5.15%) have formulas.

Only 737 had VBA code, and 472 of them had unique VBA content as determined by a MD5 hash.
They have a range of typically 10 to 2000 lines of code.
102 have “Macro recorded by…” and no Dim statements
Only 78 of 472 have Option Explicit

I have prepared a slide deck of the findings, available at:
http://www.sysmod.com/vbainfusecorpus-pobeirne.pdf (185K PDF)

Does this interest anyone?

Posted in Excel/VBA, Research | Tagged , , | 1 Comment

SoftTest Ireland Conference 2015 – Future Proof your Software Testing

SoftTest Ireland in association with the ISA Software Skillnet are hosting a one-day national conference for software test professionals.

Date: Wednesday 23rd September 2015 9am – 5.30pm
Venue: Clyde Court Hotel, Ballsbridge, Dublin 4

Cost: €100.00 per delegate
Book here: https://softtest2015.eventbrite.co.uk/

Software testing is experiencing an industry disrupting revolution, driven by rapidly evolving technologies and software delivery paradigms.
Understand the critical technologies to be learned, key skills to be developed and the professional persona required by testers to further enhance their value into the future.

Explore the future of testing with expert speakers on topics such as;
The Internet of Things
Cloud Test Ops
Test Automation
Mobile
Personal professional development

9 Speakers: Paul Gerrard, Chris Ambler, Colm Harrington, Declan O’Riordan, Claire Goss, Augusto Evangelisti, Stephen Janaway, Jonathan Wright, Matt Wynn

http://www.isa-skillnet.com/events/30/softtest-conference-2015—future-proof-your-software-testing/
t: @softtestireland
http://www.softtest.ie

SoftTest Ireland

Interest group for software testers in Ireland

Posted in Software Testing | Tagged , | Leave a comment

VBA in the spreadsheets from the Enron email corpus

The European Spreadsheet Risk 2015 Conference papers are now available at
http://www.eusprig.org/papers-presentations.htm

My presentation was on investigating the use of VBA in spreadsheets in the Enron email corpus.

http://www.eusprig.org/presentations/VBA%20in%20spreadsheets%20from%20Enron,PO’Beirne%202015.pdf

Most of the slides deal with the mechanics of how I did it, and statistics on the 538 workbooks found with unique VBA content. The network graph I did for interest was #madewithgephi.

Workbook VBA code similarity

Workbook VBA code similarity

Some conclusions I came to are:

1) The workbooks are probably not typical of the routine mass of everyday spreadsheets.
If people email spreadsheets to others, I infer that they don’t have a shared folder on the network. Therefore these workbooks are for communication. So, they are probably not an insight into the real ‘dark matter’ of EUC that stays in shared folders and never emailed – eg routine accounting workbook.

2) Apart from simple static analysis which gives a general indicator of code quality, it is very difficult to say if the VBA contains errors.
The real test is in execution of the code. But we cannot reproduce the environment in which these workbooks were created. The files we have are probably circulated as reports for reading, so after the execution of the code. Therefore do not have the preconditions to run the code. Static analysis like TM-VBA inspector only goes so far.
Code inspection can raise questions of unsafe practices and assumptions, but is time consuming.

Posted in Excel/VBA, Research | Tagged , , , , | Leave a comment

XLTest 1.55 released

I have updated my spreadsheet auditing addin.
Changes in version 1.55
1.    On startup, it checks and fixes autonumber seed problems in database
2.    Processing limit of 1 million cells made an option
3.    ExportActiveProject procedure runnable by VBA automation
4.    VBA Sort Procedures option
5.    Option to list all sheet statistics separately when scanning files
6.    Demo / 30-day evaluation version made available
7.    Utility option to display current autofilter criteria
8.    Numerous usability tweaks

http://www.sysmod.com/xltest

In order to make a time limited trial available I am now protecting it using the ‘Unviewable VBA’ app from Petros Chatzipantazis:

http://spreadsheet1.com/unviewable-vba-project-app-for-excel.html

He also markets a Ribbon Commander addin intended to make Ribbon customisation easier than XML.

Posted in Uncategorized | Tagged | Leave a comment

Excel VBA Copy Range, Paste as Table to Powerpoint 2010/2013

So, I’ve spent the last couple of weeks trying to get that one right.

Piecing together all the hints from stackoverflow and the rest, I find that I have to use the ExecuteMso method available in PP2010 and later, and use Doevents to give PP time to act in order to avoid raising errors.

In this code, mPPAppObject is the Powerpoint Application object accessed from Excel VBA eg Set mPPAppObject = GetObject(, "PowerPoint.Application")

Assuming we start with rng.Copy then if the paste is to be a table then

for i=1 to 500:Doevents :next ' 5 hundred, about 10ms timing

‘Otherwise get a Clipboard Error -2147188160 Shapes (unknown member) : Invalid request.  Clipboard is empty or contains data which may not be pasted here.

mPPAppObject.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting" ' 2010+

mPPAppObject.CommandBars.ReleaseFocus

for i=1 to 5000:Doevents :next ' 5 thousand, about 100ms
‘Otherwise calling code testing the slide.shapes collection does not see the pasted shape yet, or it does not see that the shape has a table (oShape.HasTable)

Set oShape = mPPSlide.Shapes(mPPSlide.Shapes.Count)

If we want some other paste format, then it’s a bit easier:

Set oShape = mPPSlide.Shapes.PasteSpecial(PasteDataType)

‘Where PasteDataType can be

‘ 0 ppPasteDefault ‘ truncates to correspond to visible on worksheet window
‘ 2 ppPasteEnhancedMetafile pastes correctly with full width
‘ 3 ppPasteMetafilePicture ‘ same as default, *truncated if too wide*

‘ 7 ppPasteText
‘ 8 ppPasteHTML ‘ does not work, use mPPAppObject.ActiveWindow.View.Paste

In PP 2013 I sometimes found that more than one shape would be pasted, so:

' oShape.Count property does not exist for correctly pasted shape. So use errtrapped function
If ObjectCount(oShape) > 1 Then ' handle pp 2013 bug
'Debug.Print ">1 shape pasted: "; TypeName(oShape); oShape.Count ' ShapeRange 2
Set oShape = oShape(1)
End If

Function ObjectCount(obj As Object) As Long
If obj Is Nothing Then
ObjectCount = 0
Else
ObjectCount = 1
On Error Resume Next
ObjectCount = obj.Count
'could do Err.Clear here if you like
End If
End Function

What a kludge to get something done that should be easily accessible from the COM model.

Posted in Uncategorized | Tagged , , , , | 18 Comments

ADODB Connection string for LocalDB and SQL Server Native Client

The limit on Access MDB file size is 2GB. To get around that I wanted to try out the lightweight LocalDB server, rather than SQLExpress.
SqlLocalDB.msi is 38MB
sqlncli.msi is 3MB for win32, 5MB for win64

It took a while to get the right connection string, so to save others the wasted time, here’s what I found:

http://msdn.microsoft.com/en-us/library/hh510202.aspx
SQL Server 2014 Express LocalDB
says under “Connecting to the Automatic Instance”:

The easiest way to use LocalDB is to connect to the automatic instance owned by the current user by using the connection string “Server=(localdb)\MSSQLLocalDB;Integrated Security=true”. To connect to a specific database by using the file name, connect using a connection string similar to “Server=(LocalDB)\MSSQLLocalDB; Integrated Security=true ;AttachDbFileName=D:\Data\MyDB1.mdf”.
Note
The first time a user on a computer tries to connect to LocalDB, the automatic instance must be both created and started. The extra time for the instance to be created can cause the connection attempt to fail with a timeout message. When this happens, wait a few seconds to let the creation process complete, and then connect again.

The string uses “Integrated Security=true;”, but as you can see below that failed for me.

You will see other results in google using
Server=(localdb)\v11.0;
or
Server=(localdb)\v12.0;
and you can check the version at a command prompt;
C:\> sqllocaldb v
Microsoft SQL Server 2014 (12.0.2000.8)

but MS have now gone to a version independent name
Server=(localdb)\MSSQLLocalDB;
This is not yet mentioned on
https://www.connectionstrings.com/sql-server/

As I am connecting from Excel using ADODB, my connection string will also need a Provider, which is not mentioned in the MSDN article above.

I use the SQL Server Native Client :
http://msdn.microsoft.com/en-us/library/ms131321.aspx
you can download SQLNCLI11 directly from
http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409

I initially used “Provider=SQLNCLI;…” but got an error 3706
Provider cannot be found. It may not be properly installed.
The windows script I posted earlier gives a list of what providers are already installed on your PC.
so I find I need to use the version number in this case:
“Provider=SQLNCLI11;…”
If you want to check if a specific ADO provider is installed, check it in registry at path HKEY_CLASSES_ROOT\[Provider_Name] which gives you its CLSID.

Another potential source of confusion is the use of equivalent names in the connection string
eg Trusted_Connection=true is ODBC and Integrated Security=SSPI is OLEDB.

http://stackoverflow.com/questions/3077412/what-is-the-difference-between-trusted-connection-and-integrated-security-in-a-c
gives a handy table showing that for example, these are the same:

    +----------------------+-------------------------+
    | Value                | Synonym                 |
    +----------------------+-------------------------+
    | extended properties  | attachdbfilename        |
    | timeout              | connect timeout         |
    | server               | data source             |
    | database             | initial catalog         |
    | trusted_connection   | integrated security     |
    +----------------------+-------------------------+

Error messages

Here I used the wrong version, 11 instead of 12:
“Provider=SQLNCLI11;Server=(localdb)\v11.0;Integrated Security=SSPI;”
-2147467259 SQL Server Network Interfaces: Cannot create an automatic instance.

This is correct; if you get a timeout error, simply retry:
“Provider=SQLNCLI11;Server=(localdb)\v12.0;Integrated Security=SSPI;”
-2147467259 Unable to complete login process due to delay in opening server connection
I got a timeout error on my PC after 20 seconds so now add “timeout=30;” to the string
I imagine that time might improve with Windows prefetch.

This is the version-independent string:
“Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Integrated Security=SSPI;”

The MS article uses “Integrated Security=true;”, but that failed for me:
“Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Integrated Security=true;”
-2147217887 Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

http://www.connectionstrings.com/sql-server-native-client-11-0-oledb-provider/
confirms that it should be “Trusted_Connection=yes;”

This is what I finally used:
“Provider=SQLNCLI11;Server=(localdb)\MSSQLLocalDB;Trusted_Connection=yes;timeout=30;”
I am posting this here to provide a google hit for that string, in the hope this helps someone.

Patrick

Posted in Excel/VBA | Tagged , , , , , , , | Leave a comment