1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
Imports System.IO
Imports System.Data.OleDb
Imports xls = Microsoft.Office.Interop.Excel
Imports System.Text.RegularExpressions
Private appXls As xls.Application
Private booksXls As xls.Workbook
Private sheetXls As xls.Worksheet
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Call SelectionFichierExcel()
Call ListeLesFeuilles()
End Sub
Private Sub ListeLesFeuilles()
Dim NomDeFeuille As String = ""
Dim Extention As String = LCase(Split(TextBox1.Text, ".")(UBound(Split(TextBox1.Text, "."))))
Dim i As Integer
Dim DR As System.Data.OleDb.OleDbDataReader = Nothing
Dim DT As DataTable = Nothing
Dim CMD As System.Data.OleDb.OleDbCommand = Nothing
Dim CNX As System.Data.OleDb.OleDbConnection = Nothing
Dim TableDesFeuilleXLSM() As String
ReDim TableDesFeuilleXLSM(0)
Try
Select Case Extention
Case "xls"
CNX = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TextBox1.Text & ";Extended Properties =""Excel 8.0;HDR=No"";")
Case "xlsx"
CNX = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & TextBox1.Text & ";Extended Properties =""Excel 12.0;HDR=No"";")
Case "xlsm"
appXls = New xls.Application
appXls.Workbooks.Open(TextBox1.Text,, True)
booksXls = appXls.Workbooks(1)
For i = 1 To booksXls.Worksheets.Count
sheetXls = booksXls.Worksheets(i)
ReDim Preserve TableDesFeuilleXLSM(i)
TableDesFeuilleXLSM(i - 1) = sheetXls.Name
Next i
End Select
Select Case Extention
Case "xls"
CNX.Open()
DT = New System.Data.DataTable
DT = CNX.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, {Nothing, Nothing, Nothing, "TABLE"})
ListBox2.Items.Clear()
For i = 0 To DT.Rows.Count - 1
NomDeFeuille = DT.Rows.Item(i).ItemArray(2).ToString
If Mid(NomDeFeuille, Len(NomDeFeuille)) = "$" Then
NomDeFeuille = Replace(DT.Rows.Item(i).ItemArray(2).ToString, "$", "")
If Mid(NomDeFeuille, 1, 1) = "'" Then
NomDeFeuille = Mid(NomDeFeuille, 2, Len(NomDeFeuille) - 1)
End If
ListBox2.Items.Add(NomDeFeuille)
End If
Next
Case "xlsx"
CNX.Open()
DT = New System.Data.DataTable
DT = CNX.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, {Nothing, Nothing, Nothing, "TABLE"})
ListBox2.Items.Clear()
For i = 0 To DT.Rows.Count - 1
NomDeFeuille = DT.Rows.Item(i).ItemArray(2).ToString
If Mid(NomDeFeuille, Len(NomDeFeuille)) = "$" Then
NomDeFeuille = Replace(DT.Rows.Item(i).ItemArray(2).ToString, "$", "")
If Mid(NomDeFeuille, 1, 1) = "'" Then
NomDeFeuille = Mid(NomDeFeuille, 2, Len(NomDeFeuille) - 1)
End If
ListBox2.Items.Add(NomDeFeuille)
End If
Next
Case "xlsm"
ListBox2.Items.Clear()
For i = 0 To UBound(TableDesFeuilleXLSM) - 1
ListBox2.Items.Add(TableDesFeuilleXLSM(i))
Next i
appXls.Quit()
End Select
Catch ex As Exception
MsgBox(ex.Message)
Finally
Select Case Extention
Case "xls"
If Not CNX Is Nothing Then
CNX.Close()
End If
CNX = Nothing
Case "xlsx"
If Not CNX Is Nothing Then
CNX.Close()
End If
CNX = Nothing
Case "xlsm"
If appXls IsNot Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheetXls)
System.Runtime.InteropServices.Marshal.ReleaseComObject(booksXls)
System.Runtime.InteropServices.Marshal.ReleaseComObject(appXls)
appXls = Nothing
End If
End Select
End Try
End Sub |
Connexion Excel vb.net
Répondre avec citation
Envoyé par Macstorm
Partager