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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving Data from SQL Server to Excel
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(Arra y( _ "ODBC;DSN=seassql08;Description=seassql08;UID=cmsa dm;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','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('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 ------------------------------------------------------------------------- |
Reply |
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) |