Export: Excel

[ermora]

So I have this application that accesses an MS-SQL 2K server via ADO. The application is written in VB.

The application uses a ListView control to display ADO recordset content. Now I added a button that exports the content of the ListView to Excel.

The method for the export from the VB app to Excel is via Arrays. It works fine.

My question is, isn't there a faster way? Right now, the export takes each column in the ListView and creates an Array. The Array is then looped thru to place in an Excel Spreadsheet (that is neither visible during the export, nor saved after it is made visible to the user).

Like I said, it works good until you hit a recordset with many records and many columns being returned.

Any suggestions?

Thanks,

 

[TysonLPrice]

I don't know if this is feasible for you but instead of writing the listview to an array write it to a text file and then use Call .Workbooks.OpenText to plop it into excel all at once.

Just for example...not the complete code:

Open a text file:

Write the headings to the text file:

Write #intFNum, "SSN"; "Claimant Name";..etc.

Write the listview to the text file with breaks:

Write #intFNum, ; listview.Value & "", vbCrLf, " ");

Plop it into Excel:

Call .Workbooks.OpenText(strXlFileName, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
Array(2, 2),

There are probably better ways but the call to Workbooks.OpenText is definately faster then cell by cell.

 

[ermora]

I'm sure the call to Workbooks.OpenText is faster, but wouldn't you have had to shift some of the wait time to the part where you're writing the text file that is later loaded?

One of the things that I want to keep is the fact that even when the ListView is exported to excel, the excel spreadsheet isn't saved - so there is no need to clean things up and no need to worry about naming it, etc.

I'll play around with Workbooks.OpenText to see it's effect.

Thanks,

 

[SkipVought]

Hi,

Check out the CopyFromRecordset method.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[TysonLPrice]

I don't think the CopyFromRecordset works in Excel 97 so it depends on what you are using. We are still in the stoneage here.

I

 

[AndyLee100]

I would build a string from the listview separating the text with commas and putting a vbcrlf at the end of each line.

Save this to a temp file.

Then use the 'Open' method of the workbook which has the filename as a parameter to open it. Excel will automatically open and correctly place the text into the columns. This is blindingly fast.

Also try using the Save method on the recordset which includes the option to save as xml. The workbook object has an 'OpenXML' method.

Andy

 

[SkipVought]

CopyFromRecordset does not work for ADO in Excel 97 -- however, it does for DAO.

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[gmmastros]

Excel can open CSV files with no problems. So, depending on the size of your data, this method may work out pretty well for you. You'll need a reference to "Windows Scripting Runtime"

[green]' Make sure you have an open Connection Object
' To your database.[/green]

Set RS = CreateObject("ADODB.Recordset")
RS.CursorLocation = adUseServer
Call RS.Open("Select * From Calendar", DB, adOpenForwardOnly, adLockReadOnly)
    
Set FSO = CreateObject("Scripting.FileSystemObject")
Call FSO.CreateTextFile("C:\TekTips.csv").Write(Replace(RS.GetString, vbTab, ","))
Set FSO = Nothing

RS.Close
Set RS = Nothing

The getstrings method of the recordset object will return all your data. Each record is seperated by a CR/LF, and each field is seperated with a tab character, which is why I replace the tab character with a comma.

With this method, I created a 1 meg CSV file in 3 seconds (on my computer).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom