|
Option Explicit
Private Sub
cmbAddExcelData_Click()
Dim
xlApp
As
Excel.Application
Dim
xlWBook
As
Excel.Workbook
Dim
xlWSheet
As
Excel.Worksheet
Dim
xlRange
As
Excel.Range
Dim
stFile
As String
Dim
i
As
Integer,
j
As Integer
Dim
vadata
As
Variant
stFile =
"C:\FlexData.xls"
Set
xlApp =
New
Excel.Application
Set
xlWBook
= xlApp.Workbooks.Open(stFile)
Set
xlWSheet
= xlWBook.Worksheets(1)
With
xlWSheet
vadata = .Range(.Range("A2"),
.Range("B65536").End(xlUp)).Value
End With
With
Me.MSFlexGrid1
.Rows
=
UBound(vadata,
1)
+
1
'Antal rader
.Cols =
2
+
1
'Antal
kolumner
.TextMatrix(0,
1)
=
"Avdelning"
'Fältnamn 1
.TextMatrix(0,
2)
=
"Belopp"
'Fältnamn 2
For
i
=
1
To
UBound(vadata,
1)
For
j =
1
To
UBound(vadata,
2)
.TextMatrix(i, j) = vadata(i, j)
Next
j
Next
i
End With
xlWBook.Close SaveChanges:=False
xlApp.Quit
Set
xlWSheet
=
Nothing
Set
xlWBook =
Nothing
Set
xlApp =
Nothing
End Sub
Private Sub
cmbAddExcelADO_Click()
Dim
cnt
As
ADODB.Connection
Dim
rst
As
ADODB.Recordset
Dim
stCon
As String,
stSQL
As String
Dim
iCount
As Integer,
iFields
As Integer,
iRows
As Integer
Dim
i
As Integer,
j
As Integer
stSQL =
"SELECT * FROM [Blad1$]"
stCon =
"Provider=Microsoft.Jet.OLEDB.4.0;"
&
_
"Data Source=C:\FlexData.xls;"
&
_
"Extended Properties=""Excel 8.0;HDR=YES"";"
Set
cnt =
New
ADODB.Connection
Set
rst =
New
ADODB.Recordset
cnt.Open stCon
With
rst
.CursorLocation = adUseClient
.Open stSQL, cnt
Set
.ActiveConnection =
Nothing
End With
iFields = rst.Fields.Count
iRows =
rst.RecordCount
With
Me.MSFlexGrid1
.Rows = iRows +
1
'Antal rader.
.Cols =
iFields
'Antal
kolumner.
.FixedCols
=
0
'Antal fasta kolumner.
'Tilldelar första raden i FlexGrid fältnamnen i Recordset.
For
i =
0
To iFields -
1
.TextMatrix(0,
i) = rst.Fields(i).Name
Next
i
Do
rst.MoveFirst
'Återställer cursorn placering i recordset.
iCount =
1
'Återställer radstart för varje fält.
Do
'Tilldelar kontrollen data från recordset.
.TextMatrix(iCount, j) = rst.Fields(j).Value
iCount = iCount +
1
rst.MoveNext
Loop Until
rst.EOF =
True
j = j +
1
Loop While
j <= iFields -
1
End With
rst.Close
cnt.Close
Set
rst =
Nothing
Set
cnt =
Nothing
End Sub
Private Sub
cmbExcelADOGetRows_Click()
Dim
cnt
As
ADODB.Connection
Dim
rst
As
ADODB.Recordset
Dim
stCon
As String,
stSQL
As String
Dim
iCount
As Integer,
iRows
As Integer
Dim
i
As
Integer,
j
As Integer
Dim
vaData
As
Variant
stSQL =
"SELECT * FROM [Blad1$]"
stCon =
"Provider=Microsoft.Jet.OLEDB.4.0;"
&
_
"Data Source=C:\FlexData.xls;"
&
_
"Extended Properties=""Excel 8.0;HDR=YES"";"
Set
cnt =
New
ADODB.Connection
Set
rst =
New
ADODB.Recordset
cnt.Open stCon
With
rst
.CursorLocation = adUseClient
.Open stSQL, cnt
Set
.ActiveConnection =
Nothing
End With
iCount =
rst.RecordCount
'Här
skapar vi en 0-baserad array.
vaData =
rst.GetRows(iCount)
With
Me.MSFlexGrid1
.Rows
= iCount +
1
.Cols
=
UBound(vaData)
+
1
.FixedCols
=
0
For
j =
0
To iCount -
1
For
i =
0
To
UBound(vaData)
.TextMatrix(j +
1,
i) = vaData(i, j)
Next
i
Next
j
End With
rst.Close
cnt.Close
Set
vaData =
Nothing
Set
rst =
Nothing
Set
cnt =
Nothing
End Sub
|