Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel VB Code ODBC query results to Excel sheet

Posted on 2014-04-11
23
Medium Priority
?
610 Views
Last Modified: 2014-04-25
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?
0
Comment
Question by:shaunwingin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
  • 3
23 Comments
 
LVL 35

Expert Comment

by:ste5an
ID: 39993518
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.
0
 

Author Comment

by:shaunwingin
ID: 39993525
Say, I need to automate this in Excel and can't use this method as far as can tell.
Please also supply an example.
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 39993569
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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:shaunwingin
ID: 39993654
You are correct. I want to use pure VB code to do this.
Please give example tx.
0
 
LVL 35

Expert Comment

by:ste5an
ID: 39993699
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

Open in new window

0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 39993791
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
0
 

Author Comment

by:shaunwingin
ID: 39993937
I have - but would like VB code - as more robust.
0
 
LVL 35

Expert Comment

by:ste5an
ID: 39994021
A macro IS code.
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 39994055
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.
0
 

Author Comment

by:shaunwingin
ID: 40015861
I see what you are saying, but it uses a macro to create this - can you supply pure code pls.
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40015883
What do you think, I have posted? Even when it's named MacroX, it's still pure VBA code. So what do you want?
0
 

Author Comment

by:shaunwingin
ID: 40016976
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=ateleh_db5;UID=ateleh_5;PWD=;OPTION=3"
conn1.Open

sql1 = "select record from sosh_facileforms_subrecords where title = 'DAY SHIFT' and value ='" & Shift & "';"
Set rs1 = conn1.Execute(sql1)
ShiftID = rs1.Fields(0).Value
rs1.Close
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40017073
So what? Just copy the data from recordset into your sheet. E.g.

ActiveSheet.Range("A1:ZZ65536").CopyFromRecordset rs1

Open in new window

0
 

Author Comment

by:shaunwingin
ID: 40017412
This seems like it will help me - will have to try and revert - tx
0
 

Author Comment

by:shaunwingin
ID: 40017502
Works well tx.
Pls explain difference between these two statements?
 Sheets("Clockings").Activate
    Sheets("Clockings").Select
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40017529
There can be only one active object, but many selected in the Selection object. Some objects must be activated before they can be selected.
0
 

Author Comment

by:shaunwingin
ID: 40017587
Tx. How will I make the "Clockings" sheet active to apply your code?
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40017708
You don't have to. There's normally always an ActiveSheet in Excel at this stage.
0
 

Author Comment

by:shaunwingin
ID: 40018602
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?
0
 
LVL 35

Accepted Solution

by:
ste5an earned 2000 total points
ID: 40019422
It's the same as you already have:

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=ateleh_db5;UID=ateleh_5;PWD=;OPTION=3"
conn1.Open

sql1 = "CALL SHIFTCAPTURE('" & CompanyNo & "','" & Shift & "');"
Set rs1 = conn1.Execute(sql1)
Sheets("Clockings").Range("A1:ZZ65536").CopyFromRecordset rs1
rs1.Close 

Open in new window

0
 

Author Comment

by:shaunwingin
ID: 40021914
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?
0
 
LVL 35

Expert Comment

by:ste5an
ID: 40022014
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 

Open in new window

0
 

Author Comment

by:shaunwingin
ID: 40022279
Wow tx - save a lot of time!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question