DATA TRANSFER FROM SQL SERVER TO EXCEL

[selimsl]

Hi!
I have a table on SQL SERVER, and it has been connected to my vb application already.I display my data on DataGrid to users.Users can see data on datagrid using query.I want to export these data to EXCEL.How can I accomplish this job?

Thanks in advice.

 

[fredericofonseca]

you can either use a Excel object within your code or ADO, or eventually have SQL Server create a Excel file (through DTS).

All of the above options have been discussed before on

http://www.tek-tips.com,

so using the search options would be a good start point.

Once you have some code and if you have any problems do come here again with more specific problems and we will try to help.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[comaboy]

Depending on your needs, Excel has a pretty good data importer built-in, so you won't* need any code at all... click Data->Import External Data. You then have a number choices and options, nearly all of Wizard-based.

I'd suggest making a Data Source to your SQL Server and connecting pretty-much directly.

Coma

*May not.. like I said, depends on your needs ;-)

 

[m4dd0g]

here is some code that my help:

Private Sub cmdExcel_Click()
'Excell Object
Dim rsTemp
Dim lc
Dim k
Dim sRecords

Set objConn = CreateObject("ADODB.Connection")
objConn.Open "DSN=PASTEL"
mySQL = "Select Colums in table from Table Name"
Set rsTemp = objConn.Execute(mySQL)
If rsTemp.EOF Then
MsgBox "Geen data"
objConn.Close
Set objConn = Nothing
End If

Set Exlobj = CreateObject("excel.application")
Exlobj.Workbooks.Add
Screen.MousePointer = vbHourglass
With Exlobj.ActiveSheet
' Print the heading and columns
.Cells(1, 3).Value = "Your Compony Name"
.Cells(1, 3).Font.Name = "Verdana"
.Cells(1, 3).Font.Bold = True:
.Cells(1, 3).Font.Size = 14:
.Cells(4, 1).Value = "1": .Cells(4, 2).Value = "2"
.Cells(4, 3).Value = "3": .Cells(4, 4).Value = "4"
.Cells(4, 5).Value = "5": .Cells(4, 6).Value = "6"
.Cells(4, 7).Value = "7": .Cells(4, 8).Value = "8"
.Cells(4, 9).Value = "9"
End With

'For k = 1 To rsTemp.Fields.Count
' ExlObj.ActiveSheet.Cells(4, k).Font.Bold = True
'Next
Exlobj.Visible = True
'Import Data In Excell
NxtLine = 5
Do Until rsTemp.EOF
For lc = 0 To rsTemp.Fields.Count - 1
' Populate data into the sheet
Exlobj.ActiveSheet.Cells(NxtLine, lc + 1).Value = rsTemp.Fields(lc)
If rsTemp.Fields.Item(lc).Name <> "DATE" Then
Exlobj.ActiveSheet.Cells(NxtLine, lc + 1).Value = rsTemp.Fields(lc)
Else
Exlobj.ActiveSheet.Cells(NxtLine, lc + 1).Value = Format(rsTemp.Fields(lc), "dd/mm/yy")
End If
' Autoformat the sheet
Exlobj.ActiveCell.Worksheet.Cells(NxtLine, lc + 1).AutoFormat _
xlRangeAutoFormatList2, 0, regular, 3, 1, 1
Next
rsTemp.MoveNext
NxtLine = NxtLine + 1
Loop
Screen.MousePointer = vbDefault
End Sub