Select ALL Data and Copy to Excel

Oct 12, 2011 at 8:46 AM

Hello!

I have the following Code, to copy row by row and column by column from a sqlite3 database to a excel sheet.

it works fine, just take a while cause row by row and column by column

    colCount = SQLite3ColumnCount(stmtHandle)
    For i = 0 To colCount - 1
        colName = SQLite3ColumnName(stmtHandle, i)
        colType = SQLite3ColumnType(stmtHandle, i)
        colTypeName = TypeName(colType)
        colValue = ColumnValue(stmtHandle, i, colType)
       
        On Error Resume Next
        Cells(RowID, i + 1) = CStr(colValue)
    Next

1.) is it possible to copy all data (all rows and all columns) from a table to a excel sheet in one step or one command
2.) Which command counts the columns in a table?

 

Thanks

Ralf

Oct 12, 2011 at 9:29 AM

Hi Ralf,

Probably slowest, is how you are putting the data into Excel. It is much faster to build up an array of variants in VBA, then set the whole range to the array in one go, instead of calling myCell.Value = theValue inside a loop. One way to test this would be to replace the SQLite data access code with fixed strings and numbers that you place into the range, and time this - just to get a relative speed of the data access part vs. the Excel data insertion.

I'm not sure what the rest of your code looks like, but I guess getting the column type, count and name only needs to be done once for the statement, then for each row you call SQLite3Step, followed by getting the value for each column. That should be pretty fast.

To get the columns for a table the easiest is just to execute "SELECT * FROM MyTable" and check what the result set looks like. I'm not sure if that answers your second question.

Regards,

Govert