G
Guest
Is anyone doing VBA/Excel to MySql stuff? Perhaps with Microsoft
ActiveX DataObjects 2.5 Library (msado15.dll).
Could use some help / simple sample code.
As of about three weeks ago am doing multiple table joins off of
Excel Spreadsheets (all local). VERY COOL! Some simple code below
thanks to (and adapted from) Osgrid.
This is a key part of a larger project. See http://www.ITSDoc.org for
more info.
Rick
sub mySQL1()
'use SQL with Excel tables as input, Excel table as output
'need a ref to Microsoft ActiveX DataObjects 2.5 Library (msado15.dll)
'http://www.ozgrid.com/forum/showthread.php?t=25076
'To attach to tables use any of the following:
' WorkSheet "Select * from [Sheet1$]" (entire used range returned)
' Ranges "Select * from [Sheet1$A1:B10]"
' Named Ranges "Select * from [Named Range]"
'single 'quotes' around text values, nothing around numbers -
inside "" for Excel
'WHERE can use any of the following: = <> > < <= >= BETWEEN LIKE
'LIKE wildcard is "%", examples: 'x%' '%x' '%x%'
'Entire Excel column must be uniform datatype (int, date, float, txt,
etc)
'Top row must be labels
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String, stSQL As String
Dim i As Long, lnMode As Long
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & ActiveWorkbook.FullName & ";" _
& "Extended Properties=""Excel 8.0;HDR=YES"";"
stSQL = ActiveSheet.Range("b1")
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
cnt.Open stCon
rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic
'A check to see that records actually exist.
If Not rst.EOF Then
With Application
..ScreenUpdating = False
'Collect the present calculation-mode.
lnMode = .Calculation
..Calculation = xlCalculationManual
'Copy the records to the PT Data worksheet.
Workbooks.Add
Range("A1").CopyFromRecordset rst
Range("A1").CurrentRegion.Columns.AutoFit
'Reset the calculation-mode.
..Calculation = lnMode
..ScreenUpdating = True
End With
Else
MsgBox "No records could be found!", vbCritical
End If
'Cleaning up
If CBool(rst.State And adStateOpen) Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) Then cnt.Close
Set cnt = Nothing
End Sub
ActiveX DataObjects 2.5 Library (msado15.dll).
Could use some help / simple sample code.
As of about three weeks ago am doing multiple table joins off of
Excel Spreadsheets (all local). VERY COOL! Some simple code below
thanks to (and adapted from) Osgrid.
This is a key part of a larger project. See http://www.ITSDoc.org for
more info.
Rick
sub mySQL1()
'use SQL with Excel tables as input, Excel table as output
'need a ref to Microsoft ActiveX DataObjects 2.5 Library (msado15.dll)
'http://www.ozgrid.com/forum/showthread.php?t=25076
'To attach to tables use any of the following:
' WorkSheet "Select * from [Sheet1$]" (entire used range returned)
' Ranges "Select * from [Sheet1$A1:B10]"
' Named Ranges "Select * from [Named Range]"
'single 'quotes' around text values, nothing around numbers -
inside "" for Excel
'WHERE can use any of the following: = <> > < <= >= BETWEEN LIKE
'LIKE wildcard is "%", examples: 'x%' '%x' '%x%'
'Entire Excel column must be uniform datatype (int, date, float, txt,
etc)
'Top row must be labels
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String, stSQL As String
Dim i As Long, lnMode As Long
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & ActiveWorkbook.FullName & ";" _
& "Extended Properties=""Excel 8.0;HDR=YES"";"
stSQL = ActiveSheet.Range("b1")
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
cnt.Open stCon
rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic
'A check to see that records actually exist.
If Not rst.EOF Then
With Application
..ScreenUpdating = False
'Collect the present calculation-mode.
lnMode = .Calculation
..Calculation = xlCalculationManual
'Copy the records to the PT Data worksheet.
Workbooks.Add
Range("A1").CopyFromRecordset rst
Range("A1").CurrentRegion.Columns.AutoFit
'Reset the calculation-mode.
..Calculation = lnMode
..ScreenUpdating = True
End With
Else
MsgBox "No records could be found!", vbCritical
End If
'Cleaning up
If CBool(rst.State And adStateOpen) Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) Then cnt.Close
Set cnt = Nothing
End Sub