Shaun Wingrin
asked on
Excel VB Code ODBC query results to Excel sheet
Say,
I have a MySQL select query that returns the results of 5 fields and I wish to display these in an excel sheet. Each fields results to be displayed in a separate column in the excel sheet. I'm using ODBC to link to the query stored in the MySQL database.
How can I go about this?
I have a MySQL select query that returns the results of 5 fields and I wish to display these in an excel sheet. Each fields results to be displayed in a separate column in the excel sheet. I'm using ODBC to link to the query stored in the MySQL database.
How can I go about this?
Create a view with this query in your schema. Create a ODBC DSN. Then you can simply use Get External Data / From Other Sources / From Data Connection Wizard in the Data tab and retrieve the data from your view.
ASKER
Say, I need to automate this in Excel and can't use this method as far as can tell.
Please also supply an example.
Please also supply an example.
If I understand correctly you want to pull data into Excel from MySQL
Are you familiar with the data import wizard in Excel?
You can setup a Macro that invokes a connection to an ODBC source and pulls data into the spreadsheet.
Post back if this is what you need and if you need further instructions.
Are you familiar with the data import wizard in Excel?
You can setup a Macro that invokes a connection to an ODBC source and pulls data into the spreadsheet.
Post back if this is what you need and if you need further instructions.
ASKER
You are correct. I want to use pure VB code to do this.
Please give example tx.
Please give example tx.
HHCIB? Ever heard of macros? Just record one using the steps I've described. This gives you the necessary information. E.g.
Option Explicit
Sub Macro1()
'
' Macro1 Macro
'
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=yourDsnName;", Destination:=Range("$A$1")). _
QueryTable
.CommandText = Array( _
"SELECT * FROM `schema`.`tableOrView`")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"nameOfDataConnectionFile.odc"
.ListObject.DisplayName = "someNiceNameToDisplay"
.Refresh BackgroundQuery:=False
End With
End Sub
You can do this as follows
1. Create a new macro and start recording
2. Go to the Excel Data tab -> Import External Data -> New Database Query
3. Select your Data source
4. Select your fields and whatever filtering etc you need by answering the relevant sections in the wizard
5. Select return to Excel
That's pretty much it - stop the macro and you get your VB source
1. Create a new macro and start recording
2. Go to the Excel Data tab -> Import External Data -> New Database Query
3. Select your Data source
4. Select your fields and whatever filtering etc you need by answering the relevant sections in the wizard
5. Select return to Excel
That's pretty much it - stop the macro and you get your VB source
ASKER
I have - but would like VB code - as more robust.
A macro IS code.
I have - but would like VB code - as more robust.This is not a valid statement.
If you follow the directions in my last post and look at the source of the Macro you will see it is exactly the code you need to run.
ASKER
I see what you are saying, but it uses a macro to create this - can you supply pure code pls.
What do you think, I have posted? Even when it's named MacroX, it's still pure VBA code. So what do you want?
ASKER
I'm using code of this nature:
Dim conn1 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim fld1 As ADODB.Field
Set conn1 = New ADODB.Connection
conn1.ConnectionString = "DRIVER={MySQL ODBC 5.2 ANSI Driver};SERVER=sql19.jnb2; DATABASE=a teleh_db5; UID=ateleh _5;PWD=;OP TION=3"
conn1.Open
sql1 = "select record from sosh_facileforms_subrecord s where title = 'DAY SHIFT' and value ='" & Shift & "';"
Set rs1 = conn1.Execute(sql1)
ShiftID = rs1.Fields(0).Value
rs1.Close
Dim conn1 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim fld1 As ADODB.Field
Set conn1 = New ADODB.Connection
conn1.ConnectionString = "DRIVER={MySQL ODBC 5.2 ANSI Driver};SERVER=sql19.jnb2;
conn1.Open
sql1 = "select record from sosh_facileforms_subrecord
Set rs1 = conn1.Execute(sql1)
ShiftID = rs1.Fields(0).Value
rs1.Close
So what? Just copy the data from recordset into your sheet. E.g.
ActiveSheet.Range("A1:ZZ65536").CopyFromRecordset rs1
ASKER
This seems like it will help me - will have to try and revert - tx
ASKER
Works well tx.
Pls explain difference between these two statements?
Sheets("Clockings").Activa te
Sheets("Clockings").Select
Pls explain difference between these two statements?
Sheets("Clockings").Activa
Sheets("Clockings").Select
There can be only one active object, but many selected in the Selection object. Some objects must be activated before they can be selected.
ASKER
Tx. How will I make the "Clockings" sheet active to apply your code?
You don't have to. There's normally always an ActiveSheet in Excel at this stage.
ASKER
tx. How would I return results of this statement please and insert into sheet?
conn.Execute "CALL SHIFTCAPTURE('" & CompanyNo & "','" & Shift & "');"
SHIFTCAPTURE is a stored query?
conn.Execute "CALL SHIFTCAPTURE('" & CompanyNo & "','" & Shift & "');"
SHIFTCAPTURE is a stored query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Say, If I'm actually calling a Stored Procedure which included several select queries can I create a type of record set array to return the results of each query or must I break up the stored procedure into separate select queries?
No, but you must handle the multiple resultsets wit NextRecordset, e.g.
sql1 = "CALL SHIFTCAPTURE('" & CompanyNo & "','" & Shift & "');"
Set rs1 = conn1.Execute(sql1)
Sheets("Clockings").Range("A1:ZZ65536").CopyFromRecordset rs1
Set rs1 = rs1.NextRecordSet
Sheets("otherSheet1").Range("A1:ZZ65536").CopyFromRecordset rs1
Set rs1 = rs1.NextRecordSet
Sheets("otherSheet2").Range("A1:ZZ65536").CopyFromRecordset rs1
rs1.Close
ASKER
Wow tx - save a lot of time!