1. Accueil
  2. Forum Codes-Sources
  3. Visual Basic
  4. Visual Basic 6

Transfert des données de data Grid vers excel

Signaler
Messages postés
34
Date d'inscription
lundi 9 mai 2011
Statut
Membre
Dernière intervention
21 mai 2012
 -
je suis en train d’effecteur une petite exemple permettant d’afficher une data Grid selon plusieurs critère de recherche mais le pb que je pas réussi d'envoyer c'est résultat dans un fichier Excel



Private Sub Afficher_Click()
On Error Resume Next
oExcel.Quit
If (Combo1.ListIndex = 0) Then
DataEnvironment1.rstable.Filter = adFilterNone 'Enlever le filtre
DataGrid1.DataMember = "table"

DataGrid1.Refresh
ElseIf (Combo1.ListIndex = 1) Then
txt = DC1.BoundText

DataEnvironment1.rstable.Filter = "Ref='" & DC1.BoundText & "'"
ElseIf (Combo1.ListIndex = 2) Then

DataEnvironment1.rstable.Filter = adFilterNone
DataEnvironment1.rstable.Filter = "Nom='" & DC2.BoundText & "'"
ElseIf (Combo1.ListIndex = 3) Then
DataEnvironment1.rstable.Filter = "Prenom='" & DC3.BoundText & "'"

ElseIf (Combo1.ListIndex = 4) Then
DataEnvironment1.rstable.Filter = "Sexe='" & DC4.BoundText & "'"

End If

Text1.Text = DataEnvironment1.rstable.RecordCount

End Sub

Private Sub Combo1_Change()
DataEnvironment1.rstable.Filter = adFilterNone 'Enlever le filtre

End Sub


Private Sub Combo1_Click()
If (Combo1.ListIndex = 0) Then

DC1.Visible = False
DC2.Visible = False
DC3.Visible = False
DC4.Visible = False
ElseIf (Combo1.ListIndex = 1) Then
DC1.Visible = True
DC2.Visible = False
DC3.Visible = False
DC4.Visible = False
ElseIf (Combo1.ListIndex = 2) Then
DC1.Visible = False
DC2.Visible = True
DC3.Visible = False
DC4.Visible = False

ElseIf (Combo1.ListIndex = 3) Then
DC1.Visible = False
DC2.Visible = False
DC3.Visible = True
DC4.Visible = False


ElseIf (Combo1.ListIndex = 4) Then
DC1.Visible = False
DC2.Visible = False
DC3.Visible = False
DC4.Visible = True

End If

End Sub

Private Sub Command1_Click()
' Dim sNWind As String
' Dim conn As New ADODB.Connection
' Dim rs As ADODB.Recordset
'sNWind = _
' "C:\Users\mourad\Documents\Monbase.mdb"
'conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
' sNWind & ";"
'conn.CursorLocation = adUseClient
'Set rs = conn.Execute("test", , adCmdTable)
Dim Rs As DAO.Recordset

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
strExcelFile = "C:\Users\mourad\Documents\copie1.xlsx"
'Transfer the data to Excel
oSheet.Range("E1").Value = "Test Voyage"
oBook.Worksheets(1).Range("E1").Font.Name = "Verdana"
oBook.Worksheets(1).Range("E1").Font.Size = 15
oBook.Worksheets(1).Range("E1").Font.Color = RGB(178, 34, 34)
oSheet.Range("E1").Font.Bold = True
oSheet.Range("E2").Value = "Liste des Passagers"
oBook.Worksheets(1).Range("E2").Font.Size = 15
oSheet.Range("G2").Value = "Tél:0147823777"
oSheet.Range("G3").Value = "Fax:0147823778"
oSheet.Range("B4").Value = Date
Dim strResultat, i As String
strResultat = DataEnvironment1.rstable.RecordCount & " " & "PAX"
oSheet.Range("C6").Value = strResultat
oSheet.Range("B6").Value = "nombre des passagers"
oSheet.Range("C6:B6").Font.Size = 12
oSheet.Range("C6:G1").Font.Bold = True
oSheet.Range("A9:J9").Font.Color = RGB(178, 34, 34)
oSheet.Range("A9:J11").Font.Size = 13
oSheet.Range("A9:J11").Font.Bold = True
oSheet.Range("A9:J11").ColumnWidth = 23
i = "J"
i = i & (DataEnvironment1.rstable.RecordCount + 9)
oSheet.Range("A9", i).Value = Array("Réf", "Nom", "prénom", "Nom Pére", " Nom Grand Pére", "Sexe", "Date de Naissance", "Nationnalité", "pass N", "Date d'éxp")
oSheet.Range("A10").CopyFromRecordset DataEnvironment1.rstable

'oSheet.Range("A10").CopyFromRecordset DataGrid1.Columns(1).Value

oSheet.Range("A1", i).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oSheet.Range("A9", i).Borders.LineStyle = xlContinuous
oSheet.Range("A10").ColumnWidth = 8
If Dir(strExcelFile) <> "" Then Kill strExcelFile
'Save the Workbook and Quit Excel
oExcel.Visible = True
oBook.SaveAs strExcelFile
'oExcel.Quit

End Sub

Private Sub Command2_Click()

DataEnvironment1.rstable.AddNew

End Sub


Private Sub Form_Load()
If DataEnvironment1.rscount.State = 1 Then
DataEnvironment1.rscount.Close
Else
DataEnvironment1.rscount.Open
End If

Combo1.AddItem "tous"
Combo1.AddItem "Ref"
Combo1.AddItem "Nom"
Combo1.AddItem "Prenom"
Combo1.AddItem "Sex"
Combo1.AddItem "Date de naissance"
Combo1.AddItem "Nationalite"

End Sub

Private Sub Option1_Click()
DataGrid1.Visible = True
DataGrid2.Visible = False
End Sub

Private Sub Option2_Click()
DataGrid2.Visible = True
DataGrid1.Visible = False
End Sub