[SOLVED] Anyone going from MySql on web server to Excel on desktop?

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

I found some more on this and posted it to:
http://www.itsdoc.org/tiki/tiki-inde...+VBA+and+MySQL


"Rick" <[email protected]> wrote in message
news:[email protected]...
> 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
>