0

We developed an Excel addin written in VB.NET and we are sending dynamic SQL queries to an Azure SQL database and try to display the data returned in an Excel sheet. We are using the QueryTable object to do so.

'create Excel table to retrieve data
Dim oListObject As Excel.ListObject = Globals.ThisAddIn.Application.ActiveSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcExternal, "OLEDB;Provider=" & global_oRepository.GetVTableProvider & ";" + Me.oVTable.getDatabases().Values(0).getConnectionString(), System.Type.Missing, Excel.XlYesNoGuess.xlYes, Globals.ThisAddIn.Application.ActiveSheet.cells(Me.iRowHeaders + 2, Me.iLeft), System.Type.Missing)  '"ODBC;" + 
     oListObject.ShowHeaders = False
     oListObject.ShowTableStyleColumnStripes = False
     oListObject.ShowTableStyleFirstColumn = False
     oListObject.ShowTableStyleLastColumn = False
     oListObject.ShowTableStyleRowStripes = False
     oListObject.ShowTotals = False
     oListObject.TableStyle = ""

With oListObject.QueryTable
    .CommandType = Excel.XlCmdType.xlCmdSql
    .CommandText = Me.SQLStatement
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells
    .SavePassword = False
    .SaveData = False
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .PreserveColumnInfo = False
    oCurrentQueryTable = oListObject.QueryTable
    bCurrentQueryTableCanceled = False
    Globals.ThisAddIn.Application.EnableEvents = True
    .Refresh()

        With Globals.ThisAddIn.Application
            'Source : https://stackoverflow.com/questions/8925403/excel-vba-refresh-wait/26780134#26780134
            If Not bCurrentQueryTableCanceled Then .CalculateUntilAsyncQueriesDone()
        End With
End With

Now, every time I'm sending a SQL, the expected row count returns the correct number with that code:

Dim iResultRecordsCount As Integer = oListObject.QueryTable.ResultRange.Rows.Count

But, when I'm looking at the Excel worksheet, it seems that 1,048,576 Excel rows are inserted into the Excel document.

Excel file with a lot of rows:

After the data refresh, if I decide to save the Excel Worksheet, the file weight more than 100 Mb which is a serious performance issue.

My questions are:

  1. How is this possible?
  2. Is there a way to avoid QueryTable filling down all the cells to the limit?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Patrick Gagnon
  • 1
  • @Aaron Bertrand - It an be any SELECT * FROM statement which returns rows from a SQL Database table. Mine returns near 215 rows. – Patrick Gagnon Aug 08 '22 at 21:12

1 Answers1

0

I finally found out why...

I'm not sure if that's the only explanation but the Excel file has many cells with various data formats. If I remove all cell formats, the QueryTable behave correctly.

I hope that my answer may help someone with the same problem.

Have a good day!

Patrick Gagnon
  • 1