в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:49Z

1 версия

Новая страница

==Excel based database==



<source lang="vb">

Sub ExcelExample()
Dim r As Integer, f As Integer
Dim vrecs As Variant
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim fld As ADODB.Field
Set cn = New ADODB.Connection
cn.Provider = "Microsoft OLE DB Provider for ODBC Drivers"
cn.ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=C:\mydb.mdb;"
cn.Open
Debug.Print cn.ConnectionString
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM Employees", cn, adOpenDynamic, adLockOptimistic
For Each fld In rs.Fields
Debug.Print fld.Name,
Next
Debug.Print
vrecs = rs.GetRows(6)
For r = 0 To UBound(vrecs, 1)
For f = 0 To UBound(vrecs, 2)
Debug.Print vrecs(f, r),
Next
Debug.Print
Next
Debug.Print "adAddNew: " & rs.Supports(adAddNew)
Debug.Print "adBookmark: " & rs.Supports(adBookmark)
Debug.Print "adDelete: " & rs.Supports(adDelete)
Debug.Print "adFind: " & rs.Supports(adFind)
Debug.Print "adUpdate: " & rs.Supports(adUpdate)
Debug.Print "adMovePrevious: " & rs.Supports(adMovePrevious)

rs.Close
cn.Close

End Sub
</source>

==Insert a row to a worksheet by using the SQL statement==



<source lang="vb">

Public Sub WorksheetInsert()
Dim Connection As ADODB.Connection
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\Sales.xls;" & _
"Extended Properties=Excel 8.0;"

Dim SQL As String

SQL = "INSERT INTO [Sales$] VALUES("VA", "On", "Computers", "Mid", 30)"
Set Connection = New ADODB.Connection
Call Connection.Open(ConnectionString)

Call Connection.Execute(SQL, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
Connection.Close
Set Connection = Nothing
End Sub
</source>

==Opening an Excel Spreadsheet with ADO==



<source lang="vb">

Sub Open_ExcelSpread()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & _
"\Report.xls;" & _
"Extended Properties=Excel 8.0;"
conn.Close
Set conn = Nothing
End Sub
</source>

==Use ADO to read the data from Access database to Excel==



<source lang="vb">

Public Sub SavedQuery()

Dim Field As ADODB.Field
Dim Recordset As ADODB.Recordset
Dim Offset As Long

Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydb.mdb;Persist Security Info=False"

Set Recordset = New ADODB.Recordset
Call Recordset.Open("[Sales By Category]", ConnectionString, _
CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
CommandTypeEnum.adCmdTable)
If Not Recordset.EOF Then
With Sheet1.Range("A1")
For Each Field In Recordset.Fields
.Offset(0, Offset).Value = Field.Name
Offset = Offset + 1
Next Field
.Resize(1, Recordset.Fields.Count).Font.Bold = True
End With
Call Sheet1.Range("A2").CopyFromRecordset(Recordset)
Sheet1.UsedRange.EntireColumn.AutoFit
Else
Debug.Print "Error: No records returned."
End If
Recordset.Close
Set Recordset = Nothing
End Sub
</source>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/Excel ADO - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия