Well, to partially answer my own question: No, Tableau does not trigger VBA events when it opens an Excel workbook. As a simple test, I made a new Excel workbook and added the following code:
Private Sub Workbook_Open()
For r = 1 To 50
For c = 1 To 5
Sheet1.Cells(r, c) = Int((20 - 1 + 1) * Rnd + 1)
When I created the Tableau Data Connection to my test Excel file, there were no sheets to drag into the Drag Sheets Here pane.
So... anyone have experience/tips for writing a script or application that Tableau can connect to?
Have you consider testing out the Project Maestro beta? Project Maestro | Tableau Software
It can handle the type of messy data you mention being an issue, and outputs .tde and .hyper extract files.