SQL Queries to Excel Sheets

Much of my Excel work is reporting, exports, or analysis of data for various reasons. Typically, I can extract, format, and organize a custom dataset for review from an ERP system much faster than the ERP system's own screens and reports. Tableau, OBIEE, and other BI software are great tools, but I've discovered that the fancy dashboard graphs and charts are a cool novelty for only a few months. Users ultimately want the raw data. I've also noted Tableau dashboard assets that are not even graphs or charts... it's data in sheet format that the users export to Excel anyway! LOL! So why not just build the thing in Excel to start with?

Normally, I launch my SQL query from a GET button or something similar. My Click event on that button to load data from a database might look something like this:

Sub GETbutton_Click() 
Declare DB Server and logon credentials 
   Dim datsrc As String, uname As String, upass As String
   datsrc = "MySystemName"  'this is whatever the database server is, or could be an OBDC Data Source
   uname = "DBUserName"     'the username for that database
   upass = "password"       'the password for that database

Let's connect to the DB. This example uses Microsoft's ActiveX Data Object ADODB to connect to an ODBC Data Source, but you can also go AD direct on SQL Server
   On Error Resume Next 
   Set conn = CreateObject("ADODB.Connection") 
   conn.Open "Provider=TheProviderType; Force Translate = 0;" & "Data Source=" & datsrc & ";" & 
   "User ID=" & uname & ";" & "Password=" & upass & ";"

Here's another connect method - this one on SQL server with integrated AD.
   conn.Open "Driver={SQL Server};Server={servername}; Database={DBname}; Trusted_Connection=True;"

The SQL statement... There are some important aspects here. First are the line continuations " _ and & ". Make sure they are all matching and make sure there is a trailing space at the end of each line before the last quote ". Remember this is a giant concatenation. Every space counts. There is also a limited number of line continuations that you can use. Just make your lines longer (wider than your monitor) to incorporate the whole SQL statement if the statement uses too many line continuations. Lastly, Excel does NOT use the As clause to create a column heading in row 1. But I leave the As clauses in to remind me what these columns were if I return to the code months later. You'll have to write your column headings manually into row 1.
   Sql = "Select A0.PNNUM As PartNumber, A0.PNDES As Description, " _
       & "A0.PNQTYN As QtyOnhand, A0.UCOST As UnitCost " _
       & "From InventoryMaster A0"

Let's declare the recordset, then open that recordset using the SQL statement we set defined
   Set rs = CreateObject("ADODB.Recordset") 
   Set rs.ActiveConnection = conn 
   Set rs.Open Sql

Or, if you're not exporting data back, you obviously don't need a recordset. 
Let's say the query is an UPDATE or INSERT, just execute the SQL command.
   conn.Execute Sql

Check to see if that SQL statement fired correctly. If not, then throw the error(s) to the user on-screen (MsgBox) for debugging 
   If conn.Errors.Count > 0 Then 
      For Each ADOErr In conn.Errors 
         MsgBox ADOErr.Description 
      Next 
   End If

If all went well and there are no errors, then we can finally dump the entire recordset into your sheet. I dump into row 2 and reserve row 1 for column headings.
   Sheets("Sheet2").Range("A2").CopyFromRecordset rs
End Sub

SQL statements can be very finicky. I typically develop the statement in an SQL design tool first before transferring the statement to Excel with the line continuations. I've used Microsoft's SQL Server Management Studio (SSMS), but I find it to be sluggish. These are my two favorite SQL design tools.

FLYSPEED

TOAD for SQL Server

Complex Query Statements
SQL Statements can be incredibly complex. Especially on systems that are foreign to Microsoft Windows and SQL Server. Here is an example of my complex SQL statements on the Infor XA ERP system on an IBM iSeries running IBM's DB2 database. Note that the IBM's date format is CYYMMDD so I am also using the SUBSTR function to convert the IBM date format to MM/DD/YYYY on the fly in the query (first column UPDTAV). As I mentioned above, the line continuations and spaces are critically important for these complex SQL statements to work. In the below example, I am pulling Receipts and Purchase Order information from Infor's XA ERP system database tables. Here is the SQL statement declaration formatted in VBA code with line continuations:

Sql = "Select SUBSTR(A0.UPDTAV, 4, 2) || '/' || SUBSTR(A0.UPDTAV, 6, 2) || '/' " _
    & "|| SUBSTR(INT(SUBSTR(A0.UPDTAV, 1, 1)) + 19, 1, 2) || " _
    & "SUBSTR(A0.UPDTAV, 2, 2) As TXDATE, A0.WHIDAV As WHSE, A0.TCDEAV As TXCODE, " _
    & "M0.VN35VM As VENDNAME, A0.ORDRAV As ORDERNO, A0.PISQAV As LINENO, A0.ITNOAV " _
    & "As ITEMNO, A0.BKSQAV As RELEASENO, D0.UU25AD As CUSTPN, D0.ITDSAD As DESCRIPT, " _
    & "A0.ENTUAV As UM, A0.TRMIAV As RCV_ID, A0.TRQTAV As RCV_QTY, A0.AVCSAV As AVCST_EACH " _
    & "From AMFLIB.IMHISTV5 A0 Left Outer Join AMFLIB.WHSMSTV0 B0 On A0.WHIDAV = B0.WHIDWH " _
    & "Left Outer Join AMFLIB.ITMSITV0 C0 On B0.STIDWH = C0.STIDT9 And A0.ITNOAV = C0.ITNOT9 " _
    & "Left Outer Join AMFLIB.ITMRVAV0 D0 On B0.STIDWH = D0.STIDAD And A0.ITNOAV = D0.ITNOAD " _
    & "And C0.ITRVT9 = D0.ITRVAD Left Outer Join AMFLIB.PUBITM E0 On D0.STIDAD = E0.STID1R " _
    & "And D0.ITNOAD = E0.ITNB1R And D0.ITRVAD = E0.ITRV1R Left Outer Join AMFLIB.ITMSITV0 F0 " _
    & "On D0.STIDAD = F0.STIDT9 And D0.ITNOAD = F0.ITNOT9 Left Outer Join AMFLIB.ITMRVCV0 G0 " _
    & "On D0.STIDAD = G0.STIDAW And D0.ITNOAD = G0.ITNOAW And D0.ITRVAD = G0.ITRVAW " _
    & "Left Outer Join AMFLIB.ITMPRCV0 H0 On D0.STIDAD = H0.STIDC1 And D0.ITNOAD = H0.ITNOC1 " _
    & "And D0.ITRVAD = H0.ITRVC1 And H0.IPPAC1 = '1' And H0.EDAMC1 >= 1200101 " _
    & "And H0.EDAOC1 < 1211231 Left Outer Join AMFLIB.MOMASTV6 I0 On A0.ORDRAV = I0.ORDRMY " _
    & "Left Outer Join AMFLIB.PUBMNO J0 On I0.ORDRMY = J0.ORDR0O And I0.CRDTMY = J0.CRDT0O " _
    & "Left Outer Join AMFLIB.POMASTV0 K0 On A0.PUDTAV = K0.PUDTPM And A0.ORDRAV = K0.ORDRPM " _
    & "Left Outer Join AMFLIB.PUBPUR L0 On K0.ORDRPM = L0.ORDR0N And K0.DORDPM = L0.DORD0N " _
    & "Left Outer Join AMFLIB.VENNAMV0 M0 On K0.VNDRPM = M0.VNDRVM Left Outer Join " _
    & "AMFLIB.PUBSPP N0 On M0.VNDRVM = N0.VNDRR1 Left Outer Join AMFLIB.OVERRDV1 O0 " _
    & "On K0.ORDRPM = O0.ORDRJN Left Outer Join AMFLIB.MOMASTVB P0 On A0.ORDRAV = P0.ORDRMY " _
    & "Left Outer Join AMFLIB.PUBMNO Q0 On P0.ORDRMY = Q0.ORDR0O And P0.CRDTMY = Q0.CRDT0O " _
    & "Left Outer Join AMFLIB.MBDDREVQ R0 On A0.CONOAV = R0.DDAENB And A0.ORTPAV = R0.DDDCCD " _
    & "And A0.ORDRAV = R0.DDCVNB And A0.ITMSAV = R0.DDFCNB And A0.RLNBAV = R0.DDABDD " _
    & "And R0.DDK4NB = 1 Left Outer Join AMFLIB.ITEMBLV0 S0 On A0.ITNOAV = S0.ITNOIB " _
    & "And A0.WHIDAV = S0.WHIDIB " _ 
    & "Where A0.TCDEAV In ('RI', 'RP') And A0.UPDTAV >= 1211213 " _
    & "And A0.UPDTAV <= 1211219 Order By TX_DATE Desc, A0.UPTMAV Desc"

So Excel is capable of passing massive SQL statements to a DB server but there is no mercy on syntax accuracy. First, the SQL statement syntax must obviously be perfect to query correctly. Then once transferred to Excel, the line continuations formatting in the VBA code must also obviously be perfect.

But.... if you're reading this, you already knew that :-)

BACK >
Search