[RESOLVED] Populate a spreadsheet

[uk_codemonkey]

I'm trying to write a VS2002 VB procedure that will read in the contents of a recordset and then output it to Excel, without having to actually go through the hassle of coding each individual cell movement. I've done something similar in CSV format, using the Write and Writeline commands to place the information. However, what I can't replicate in Excel is the movement from one cell to another - if I try to insert a tab between each cell, it simply concatenates the data into one cell, with tabs in between them.

I'm going home now, but if someone knows what the solution is, I'm all ears

[Static]

look into

CopyFromRecordset it will be your saviour

[RobDog888]

Watch this Static

Since classic ADO is a secondary technology in .NET you should be using either ADO.NET or automating Access and Excel to get your results.

Here is some code I wrote to output a new Excel workbook from an Access table/query.

http://vbforums.com/showpost.php?p=2514577&postcount=8

[uk_codemonkey]

look into

CopyFromRecordset it will be your saviour

Hi Static

I've got a few questions for you:

1. Will CopyFromRecordset work when using VB.NET 2002 to empty the contents of a recordset into an Excel spreadsheet;
2. Will I be able to control the names of the columns that appear in the output? I can't just have it dumping out the table column names as column headers, since the output is going to a Client;

If it comes to it, I've got the ability to use Automation to populate the spreadsheet, but that's a bit of faffage that I could really live without at the moment.

Thanks

[RobDog888]

Not much control over the columns copied using the CopyFromRecordset method. You would have to adjust your query that populates your recordset.

No field names will be populated. You have to code it by looping theough the fields collection of your recordset.