Importing data from SQL Server 1

[G12Consult]

Hi,

I am trying to import data from SQL Server into Excel.

I have setup the data connection etc and using a SQL statement to bring in the data.

In the SQL script I have two dates (which need to be parameters, dates which can be selected in Excel)

If I use specific dates in the WHERE clause such as '20162805' then I do not get an error.

If I replace with a ? for an excel parameter I am getting an error </code>microsoft odbc sql server driver sql server invalid column name</code>

My sql statement is below:

SELECT 
       occ.[EMPLOYEE_NO]
       ,occ.[JOB_NO]
       ,E.PREFERRED_NAME+' '+E.SURNAME AS EMPLOYEE_NAME
       ,T.TEAM_CODE
       ,T.TEAM_ALIAS
       ,T.TEAM_DESC 
       ,CASE WHEN T.PRAC_GRP_NAME='Other' THEN 'Business Services' ELSE T.PRAC_GRP_NAME END AS PRAC_GRP_NAME
       ,O.OFFICE_DESC
       ,occ.[OCCUP_POS_TITLE] 
       ,occ.PORTION_START AS PORTION_START
       ,occ.PORTION_END AS PORTION_END
       ,j.TERM_DATE AS TERM_DATE
       ,CASE occ.[EMP_STATUS]    WHEN 'FTP' THEN 'FP' WHEN 'PTP' THEN 'PP'   WHEN 'AIP' THEN 'AI'  WHEN 'AON' THEN  'AO'  ELSE occ.[EMP_STATUS]  END  AS [EMP_STATUS]
       ,R.RANK_GROUP_DESC
       ,R.RANK_DESC
       ,R.RANK_CODE
       ,e.GENDER
       ,TB.TIME_OK
      
       FROM 
       
       SQLxxxxxxx  

       --get the current occupancy for the date
       INNER JOIN
       (
              SELECT *
              FROM (
						SELECT	EMPLOYEE_NO, 
								JOB_NO, 
								PORTION_START, 
								OCCUP_COM_REAS, 
								ADMIN_LOCATION, 
								PAYPOINT, 
								OCCUP_POS_TITLE, 
								PORTION_END,
								EMP_STATUS,
								ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_NO ORDER BY PORTION_START DESC) as rn
              FROM xxxxxxxx) OCC
              WHERE occ.rn = 1
              
              
       ) occ on occ.employee_no = e.employee# and occ.rn = 1 

       --get the current job for the date
       INNER JOIN xxxx on e.EMPLOYEE# = j.EMPLOYEE# and j.JOB# = occ.JOB_NO

       LEFT JOIN xxx  ON J.TERM_REASON=TC.CODE AND TC.KIND='TERM_REASON'
       LEFT JOIN xxx  ON occ.OCCUP_COM_REAS=TOO.CODE AND TOO.KIND='OCCUP_COM_REAS'
       LEFT JOIN xxx.DBO.MASTER_DESC T ON occ.[ADMIN_LOCATION]=T.TEAM_CODE
       LEFT JOIN xxx.DBO.MASTER_DESC O ON LEFT([PAYPOINT],1)+CASE J.COMPANY_CODE    WHEN '01' THEN '10' WHEN '02' THEN '20' END=O.OFFICE_CODE
       LEFT JOIN xxx.DBO.HBM_PERSNL P ON P.EMPLOYEE_CODE = J.PAYROLL#
       LEFT JOIN xxx.DBO.TBM_PERSNL TB ON TB.EMPL_UNO = P.EMPL_UNO
       LEFT JOIN xxx.DBO.MASTER_DESC R ON TB.RANK_CODE = R.RANK_CODE
       LEFT JOIN xxxdbo].[xxx_codes] flex on flex.code = e.aboriginality
       --have had to join to HR data as not all occupancy records
       --LEFT JOIN xxx.[xxx].[dbo].[EMPLTABLE] emp on emp.emplid = e.employee#
       
              
       --LEFT JOIN xxxT ON 
       
       WHERE 1=1
              and e.employee# NOT IN ('022288', '020241')  
              AND TB.TIME_OK = 'Y'
              AND TB.RANK_CODE IN ('1', '2', '3', '4', '5', '6', '8', '9', '10', '11','12', '26', '29', '31', '400', '500', '600', '650', '800', '900' )
              AND OCC.PORTION_END >=getdate()
              AND OCC.PORTION_START <= ? AND OCC.Portion_End >= ?

So the error is on the bottom line -

AND OCC.PORTION_START <= ? AND OCC.Portion_End >= ?

Any ideas please?

 

[SkipVought]

Hi,

Dates in Excel are NUMBERS, roughly the number of days since Dec 31, 1899.

What your SQL Server query needs is a numeric STRING: YYYYMMDD, or
[tt]
=TEXT(YourDateReference, "yyyymmdd")
[/tt]
...in the Parameter cell.

Or you may need...
[tt]
="'"&TEXT(YourDateReference, "yyyymmdd")&"'"
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[G12Consult]

Thanks Skip,

I am getting the error even before I specify a cell for the parameter though. it seems to be when I put the ? in (which I believe is the symbol for a parameter when importing sql into excel).

When I go into the properties of the connection, and click parameters, its showing 1 parameter (Which I can modify) to include the specified cell.

But where is it getting this parameter from. Is this from the ? I added.

I haven't tried your solution above, but will this resolve the invalid column name error?

Thanks again

 

[SkipVought]

As a test, after putting in the ? RUN the query in MS Query ad it should ask for a parameter input. Actually two parameter inputs.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

After re-reading your thread, you may need a different approach using VBA.

First enter this code in a module and RUN. The Immediate Window will contain your Connection string and SQL

Sub Query()
    Dim sConn As String, sSQL As String
    
    sConn = ""
    
    sSQL = ""
    
    With ActiveSheet.ListObjects(1).QueryTable
        Debug.Print .Connection
        Debug.Print .Sql
        
    End With
End Sub

In my workbook, I'm querying two tables in two worksheets in my workbook, so my Immediate Window shows...
[pre]
ODBC;DSN=Excel Files;DBQ=C:\Users\Skip\Documents\TT_DB.xlsm;DefaultDir=C:\Users\Skip\Documents;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
SELECT `tblA$`.X, `tblA$`.Y, `tblA$`.c, Count(*)
FROM {oj `tblA$` `tblA$` LEFT OUTER JOIN `tblB$` `tblB$` ON `tblA$`.c = `tblB$`.c}
WHERE `tblA$`.X = `tblB$`.X
GROUP BY `tblA$`.X, `tblA$`.Y, `tblA$`.c
[/pre]

Take your connect string and assign to sConn and this is what I expect your sSQL string will look like...

'
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & " OCC.[EMPLOYEE_NO]"
    sSQL = sSQL & ",occ.[JOB_NO]"
    sSQL = sSQL & ",E.PREFERRED_NAME+' '+E.SURNAME AS EMPLOYEE_NAME"
    sSQL = sSQL & ",T.TEAM_CODE"
    sSQL = sSQL & ",T.TEAM_ALIAS"
    sSQL = sSQL & ",T.TEAM_DESC"
    sSQL = sSQL & ",CASE WHEN T.PRAC_GRP_NAME='Other' THEN 'Business Services' ELSE T.PRAC_GRP_NAME END AS PRAC_GRP_NAME"
    sSQL = sSQL & ",O.OFFICE_DESC"
    sSQL = sSQL & ",occ.[OCCUP_POS_TITLE]"
    sSQL = sSQL & ",occ.PORTION_START AS PORTION_START"
    sSQL = sSQL & ",occ.PORTION_END AS PORTION_END"
    sSQL = sSQL & ",j.TERM_DATE AS TERM_DATE"
    sSQL = sSQL & ",CASE occ.[EMP_STATUS]    WHEN 'FTP' THEN 'FP' WHEN 'PTP' THEN 'PP'   WHEN 'AIP' THEN 'AI'  WHEN 'AON' THEN  'AO'  ELSE occ.[EMP_STATUS]  END  AS [EMP_STATUS]"
    sSQL = sSQL & ",R.RANK_GROUP_DESC"
    sSQL = sSQL & ",R.RANK_DESC"
    sSQL = sSQL & ",R.RANK_CODE"
    sSQL = sSQL & ",e.GENDER"
    sSQL = sSQL & ",TB.TIME_OK"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "SQLxxxxxxx"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "       INNER Join"
    sSQL = sSQL & "       ("
    sSQL = sSQL & "              SELECT *"
    sSQL = sSQL & "              FROM ("
    sSQL = sSQL & "                        SELECT  EMPLOYEE_NO,"
    sSQL = sSQL & "                                JOB_NO,"
    sSQL = sSQL & "                                PORTION_START,"
    sSQL = sSQL & "                                OCCUP_COM_REAS,"
    sSQL = sSQL & "                                ADMIN_LOCATION,"
    sSQL = sSQL & "                                PAYPOINT,"
    sSQL = sSQL & "                                OCCUP_POS_TITLE,"
    sSQL = sSQL & "                                PORTION_END,"
    sSQL = sSQL & "                                EMP_STATUS,"
    sSQL = sSQL & "                                ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_NO ORDER BY PORTION_START DESC) as rn"
    sSQL = sSQL & "              FROM xxxxxxxx) OCC"
    sSQL = sSQL & "              WHERE OCC.rn = 1"
    sSQL = sSQL & ""
    sSQL = sSQL & "       ) occ on occ.employee_no = e.employee# and occ.rn = 1"
    sSQL = sSQL & ""
    sSQL = sSQL & "       INNER JOIN xxxx on e.EMPLOYEE# = j.EMPLOYEE# and j.JOB# = occ.JOB_NO"
    sSQL = sSQL & ""
    sSQL = sSQL & "       LEFT JOIN xxx  ON J.TERM_REASON=TC.CODE AND TC.KIND='TERM_REASON'"
    sSQL = sSQL & "       LEFT JOIN xxx  ON occ.OCCUP_COM_REAS=TOO.CODE AND TOO.KIND='OCCUP_COM_REAS'"
    sSQL = sSQL & "       LEFT JOIN xxx.DBO.MASTER_DESC T ON occ.[ADMIN_LOCATION]=T.TEAM_CODE"
    sSQL = sSQL & "       LEFT JOIN xxx.DBO.MASTER_DESC O ON LEFT([PAYPOINT],1)+CASE J.COMPANY_CODE    WHEN '01' THEN '10' WHEN '02' THEN '20' END=O.OFFICE_CODE"
    sSQL = sSQL & "       LEFT JOIN xxx.DBO.HBM_PERSNL P ON P.EMPLOYEE_CODE = J.PAYROLL#"
    sSQL = sSQL & "       LEFT JOIN xxx.DBO.TBM_PERSNL TB ON TB.EMPL_UNO = P.EMPL_UNO"
    sSQL = sSQL & "       LEFT JOIN xxx.DBO.MASTER_DESC R ON TB.RANK_CODE = R.RANK_CODE"
    sSQL = sSQL & "       LEFT JOIN xxxdbo].[xxx_codes] flex on flex.code = e.aboriginality"
    sSQL = sSQL & ""
    sSQL = sSQL & "       WHERE 1 = 1"
    sSQL = sSQL & "              and e.employee# NOT IN ('022288', '020241')"
    sSQL = sSQL & "              AND TB.TIME_OK = 'Y'"
    sSQL = sSQL & "              AND TB.RANK_CODE IN ('1', '2', '3', '4', '5', '6', '8', '9', '10', '11','12', '26', '29', '31', '400', '500', '600', '650', '800', '900' )"
    sSQL = sSQL & "              AND OCC.PORTION_END >=getdate()"
'[b]HERE'S WHERE YOUR PARAMETERS GET INSERTED[/B]
    sSQL = sSQL & "              AND OCC.PORTION_START <= '" & Format([[highlight #FCE94F]YourStartDateRef[/highlight]], "yyyymmdd") & "' "
    sSQL = sSQL & "              AND OCC.Portion_End >= '" & Format([[highlight #FCE94F]YourEndDateRef[/highlight]], "yyyymmdd") & "'"
              
    With ActiveSheet.ListObjects(1).QueryTable
        .Connection = sConn
        .Sql = sSQL
        .Refresh False
    End With

Personally, I'd use two named range names for these cells


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[G12Consult]

Thanks Skip,

I was looking at the VBA approach as well.

I am getting a subscript out of range on the line

With ActiveSheet.ListObjects(1).QueryTable

 

[SkipVought]

Did you use MS Query?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

One possibility on the VBA error is if you've selected a different worksheet before running the code. So the sheet with the List Object / Table / Query is no longer the "active sheet" - so Excel has no idea what ListObjects(1) is since looking at the wrong sheet. So make sure you've got the correct sheet selected before running your code.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[G12Consult]

I am running the code from a different sheet that the table is on. Will this effect the code?

 

[SkipVought]

Try using the actual sheet name, like if your sheet name were Query...

With Worksheets("Query").ListObjects(1).QueryTable

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[G12Consult]

Got it working with that change.

Thank you very much Skip. Much appreciated.