Retrieving Data from SQL Server to Excel

D

Damian Carrillo

I tried to use the macro recorder to get a base VBA code which I could
then modify to suit my needs. Unfortunately, while the steps I took
succeeded, the code itself does not function as written by the macro
recorder. See below. My question is, how can I properly format the
SQL statement in the VBA code to make it work correctly? I noticed
the script itself seems to be modified from the original, which I post
following the subroutine below:

-----------------------------------------------------------------------------------------------------------------------
--MACRO RECORDER OUTPUT FROM EXCEL 2003 GET EXTERNAL DATA
-----------------------------------------------------------------------------------------------------------------------
Sub RetrieveHeadcountFromCMSLive()

'Import Data From SQL Server to populate headcount table based on the
office
'and department of the user opening the spreadsheet.

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _

"ODBC;DSN=seassql08;Description=seassql08;UID=administrator;PWD=
[*****];APP=Microsoft Office
2003;WSID=SEAD502366;Network=DBMSSOCN;Address=se" _
), Array("assql08,1433")), Destination:=Range("A5"))
.CommandText = Array( _
"SELECT HBM_PERSNL.EMPLOYEE_CODE as EmpID,
HBM_PERSNL.EMPLOYEE_NAME as EmpName, "&chr(13)&""&chr(10)
&"HBM_PERSNL.OFFC as Offc, HBM_PERSNL.DEPT as Dept,
HBM_PERSNL.LOCATION as Loc, "&chr(13)&""&chr(10)&"HBM_PERSNL.LOGIN as
Login, HBM_PERSNL.P" _
, _
"HONE_NO as Phone, HBM_PERSNL.POSITION as Position, "&chr(13)
&""&chr(10)&"HBL_PERSNL_TYPE.PERSNL_TYP_CODE as TypeID,
HBL_PERSNL_TYPE.PERSNL_TYP_DESC as TypeName,"&chr(13)&""&chr(10)
&"TBM_PERSNL.RANK_CODE as Rank, TBM_PERSNL.PARTIME_PCNT as F" _
, _
"TE"&chr(13)&""&chr(10)&"FROM (dbo.HBM_PERSNL INNER JOIN
HBL_PERSNL_TYPE ON "&chr(13)&""&chr(10)
&"dbo.HBM_PERSNL.PERSNL_TYP_CODE =
HBL_PERSNL_TYPE.PERSNL_TYP_CODE)"&chr(13)&""&chr(10)&"INNER JOIN
TBM_PERSNL ON TBM_PERSNL.EMPL_UNO = dbo.HBM_PERSNL.EMPL_UNO"&chr(13)
&""&chr(10)&"WHER" _
, _
"E HBM_PERSNL.INACTIVE='N' and HBM_PERSNL.PERSNL_TYP_CODE NOT
IN('PERKI','RESR')"&chr(13)&""&chr(10)&"and HBM_PERSNL.LOGIN NOT IN
('','15REC','ZZZZA','EVENTS','SPALA','PZZZX','DCGU1','INTAPPADMIN','LAGU1','TECHS','DR0NE')"&chr
(13)&"" _
, _
""&chr(10)&"and HBM_PERSNL.LOGIN NOT LIKE'%TEMP%' and
HBM_PERSNL.LOGIN NOT LIKE'TRANS%'"&chr(13)&""&chr(10)&"and
HBM_PERSNL.LOGIN NOT LIKE'TRON%' and HBM_PERSNL.LOGIN NOT LIKE'POGU
%'"&chr(13)&""&chr(10)&"and HBM_PERSNL.LOGIN NOT LIKE'BIT%' and
HBM_PER" _
,,)
.Name = "Query from seassql08"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

-----------------------------------------------------------------------------------------------------------------------
--ORIGINAL WORKING SQL SERVER 2000 QUERY
-----------------------------------------------------------------------------------------------------------------------
SELECT HBM_PERSNL.EMPLOYEE_CODE as EmpID, HBM_PERSNL.EMPLOYEE_NAME as
EmpName,
HBM_PERSNL.OFFC as Offc, HBM_PERSNL.DEPT as Dept, HBM_PERSNL.LOCATION
as Loc,
HBM_PERSNL.LOGIN as Login, HBM_PERSNL.PHONE_NO as Phone,
HBM_PERSNL.POSITION as Position,
HBL_PERSNL_TYPE.PERSNL_TYP_CODE as TypeID,
HBL_PERSNL_TYPE.PERSNL_TYP_DESC as TypeName,
TBM_PERSNL.RANK_CODE as Rank, TBM_PERSNL.PARTIME_PCNT as FTE
FROM (dbo.HBM_PERSNL INNER JOIN HBL_PERSNL_TYPE ON
dbo.HBM_PERSNL.PERSNL_TYP_CODE = HBL_PERSNL_TYPE.PERSNL_TYP_CODE)
INNER JOIN TBM_PERSNL ON TBM_PERSNL.EMPL_UNO = dbo.HBM_PERSNL.EMPL_UNO
WHERE HBM_PERSNL.INACTIVE='N' and HBM_PERSNL.PERSNL_TYP_CODE NOT IN
('PERKI','RESR')
and HBM_PERSNL.LOGIN NOT IN
('','15REC','ZZZZA','EVENTS','SPALA','PZZZX','DCGU1','INTAPPADMIN','LAGU1','TECHS','DR0NE')
and HBM_PERSNL.LOGIN NOT LIKE'%TEMP%' and HBM_PERSNL.LOGIN NOT
LIKE'TRANS%'
and HBM_PERSNL.LOGIN NOT LIKE'TRON%' and HBM_PERSNL.LOGIN NOT LIKE'POGU
%'
and HBM_PERSNL.LOGIN NOT LIKE'BIT%' and HBM_PERSNL.LOGIN NOT LIKE'DPC
%'
and HBM_PERSNL.LOGIN NOT LIKE'PERK%' and HBM_PERSNL.LOGIN NOT LIKE'CMS
%'
and HBM_PERSNL.OFFC IN('13','14') --and HBM_PERSNL.DEPT IN('890')
ORDER BY HBM_PERSNL.OFFC, HBM_PERSNL.DEPT, HBM_PERSNL.EMPLOYEE_NAME
 
D

Damian Carrillo

So I found the problem. Macro Recorder added in an extra ARRAY() for
the CommandText property. Once I took the SQL statement out of the
array encapsulation and included the rest of the code that was
truncated, it functioned as expected.

-------------------------------------------------------------------------
Sub RetrieveHeadcountFromCMSLive()

'Import Data From SQL Server to populate headcount table based on the
office
'and department of the user opening the spreadsheet.

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _

"ODBC;DSN=seassql08;Description=seassql08;UID=cmsadm;PWD=cmsadm;APP=Microsoft
Office 2003;WSID=SEAD502366;Network=DBMSSOCN;Address=se" _
), Array("assql08,1433")), Destination:=Range("A5"))
.CommandText = "SELECT HBM_PERSNL.EMPLOYEE_CODE as EmpID,
HBM_PERSNL.EMPLOYEE_NAME as EmpName," & " " & _
"HBM_PERSNL.OFFC as Offc, HBM_PERSNL.DEPT
as Dept, HBM_PERSNL.LOCATION as Loc," & " " & _
"HBM_PERSNL.LOGIN as Login,
HBM_PERSNL.PHONE_NO as Phone, HBM_PERSNL.POSITION as Position," & " "
& _
"HBL_PERSNL_TYPE.PERSNL_TYP_CODE as
TypeID, HBL_PERSNL_TYPE.PERSNL_TYP_DESC as TypeName," & " " & _
"TBM_PERSNL.RANK_CODE as Rank,
TBM_PERSNL.PARTIME_PCNT as FTE" & " " & _
"FROM (dbo.HBM_PERSNL INNER JOIN
HBL_PERSNL_TYPE ON" & " " & _
"dbo.HBM_PERSNL.PERSNL_TYP_CODE =
HBL_PERSNL_TYPE.PERSNL_TYP_CODE)" & " " & _
"INNER JOIN TBM_PERSNL ON
TBM_PERSNL.EMPL_UNO = dbo.HBM_PERSNL.EMPL_UNO" & " " & _
"WHERE HBM_PERSNL.INACTIVE='N' and
HBM_PERSNL.PERSNL_TYP_CODE NOT IN ('PERKI','RESR')" & " " & _
"and HBM_PERSNL.LOGIN NOT IN
('','15REC','ZZZZA','EVENTS','SPALA','PZZZX','DCGU1','INTAPPADMIN','LAGU1','TECHS','DR0NE')"
& " " & _
"and HBM_PERSNL.LOGIN NOT LIKE'%TEMP%' and
HBM_PERSNL.LOGIN NOT LIKE'TRANS%'" & " " & _
"and HBM_PERSNL.LOGIN NOT LIKE'TRON%' and
HBM_PERSNL.LOGIN NOT LIKE'POGU%'" & " " & _
"and HBM_PERSNL.LOGIN NOT LIKE'BIT%' and
HBM_PERSNL.LOGIN NOT LIKE'DPC%'" & " " & _
"and HBM_PERSNL.LOGIN NOT LIKE'PERK%' and
HBM_PERSNL.LOGIN NOT LIKE'CMS%'" & " " & _
"and HBM_PERSNL.OFFC IN('10','09') --and
HBM_PERSNL.DEPT IN('890')" & " " & _
"ORDER BY HBM_PERSNL.OFFC,
HBM_PERSNL.DEPT, HBM_PERSNL.EMPLOYEE_NAME"
.Name = "Query from seassql08"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
-------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to populate VBA Variables from ADODB.Connection Results? 4
Stubborn Array won't Populate from single ADODB.Connection Query 4
Macro to import external data from Access query to Excel 1
Excel to text file 4
Excel Import - data limitation 0
Importing data from Access to Excel, but I need to vary the table from Access 3
macro to import access data into excell 0
Using InputBox to get desired date for a Query pulling data from Access into Excel 1

Top