Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving Data from SQL Server to Excel
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(Arra y( _ "ODBC;DSN=seassql08;Description=seassql08;UID=admi nistrator;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(1 0)&"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','DCGU 1','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','DCGU 1','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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieving Excel data | Excel Programming | |||
Retrieving Data in excel | Excel Worksheet Functions | |||
Retrieving Data: Speed of beating down rows vs retrieving from array? | Excel Programming | |||
Retrieving SQL data into Excel | Setting up and Configuration of Excel | |||
Retrieving data from database (MS Sql Server) to Excel | Excel Discussion (Misc queries) |