excel not responding - updating Historian Client Excel add-in, ends in Type Mismatch (error 13) error

[andrewwhy]

Hi,

I have a spreadsheet, which pulls data from Wonderware Historian into Excel, which I later graph. The spreadsheet has a few very small macros, but it now takes a very long time to open. Excel starts by (not responding) [not even calculating, pretty sure], and ends with the Type Mismatch (error 13) error, before it allows me to work with the spreadsheet.

The macros I have are:

Private Sub Workbook_Open()
Application.Calculation = xlManual
Application.CalculateBeforeSave = False
ActiveWorkbook.RefreshAll
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CalculateBeforeSave = True
Me.Save
End Sub


Function LastSaved() As Date
Application.Volatile
LastSaved = ThisWorkbook.BuiltinDocumentProperties(12)
End Function

These macros are used to choose when to calculate and pull data from the Historian client, otherwise it takes a long time to work with the spreadsheet (save all the changes till last), and also to output when the spreadsheet was last saved.

Why am I getting a type mismatch error, and perhaps more importantly, why is my spreadsheet "not responding" for such a long time before reaching the error?

 

[GJSBED]

Am i right in thinking Your first macro is refreshing a dynamic table linked by some fashion to the Wonderware database?
In which case I'd point the finger at the SQL query/connection to that database, could be these wonderware folk have changed something on you without mentioning it?

Long periods of not responding aren't too much to worry about, it's the programs way of saying 'i need to think about this leave me alone' rather than call it 'crashing' altogether, happens often when dealing with large amounts of data.