Form to Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Form to Excel

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2005-12-14 : 04:37:42

Hi

I have a form designed in VB 6.0 and after saving that form I want the entered form fields in to a excel sheet.
Is this possible? If yes, How?

Njoy Life

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 05:56:33

Connect EXCEL with this


Set cn = New ADODB.Connection

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=FileName.xls;" & _
            "Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    .Open
 End With

and use Recordset to access sheet names

Madhivanan

Failing to plan is Planning to fail

yonabout
Posting Yak Master

112 Posts

Posted - 2005-12-14 : 06:49:49

Can you use this method if you want to format the results instead of just dumping them out?

I've done something similar by iterating through the recordset I've produced and writing to excel, but I had to generate sub headings at certain points. Could I use your method instead, because I've just tested it and its much quicker.

I also had problems where Excel 2000 doesn't care what you dump in to it, but excel 97 requires the data to be explicitly converted before it would write it to excel so if it's a decimal value coming from the recordset I've had to do this:


objxlsht.Cells(lngExcelRow, intExcelCol).Value = CDbl(rs(intexcelcol - 1).Value)

where in excel 2000 I just did this:


objxlsht.Cells(lngExcelRow, intExcelCol).Value = rs(intexcelcol - 1).Value

Have you had this kind of problem?

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources