N
nmci_dmar
Hello Alll,
I am trying to pull data from a SQL server app called Remedy. I
has a ODBC driver that I am using. I am trying to edit an existing se
of code to manipulate it to my conditions. The situation I have is th
existing code will only work for 1 "Base" code in the where clause an
1 Status. I support 60 Bases. I need to configure a Where clause tha
supports 60 Bases. The problem I run in to is every time I go over
bases / status where clauses I run into a type mismatch.
Here is my code can anyone help..
Public Sub Querry()
' Querry of the Remedy Server For Tickets
With Sheet10.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Remedy;ARServer=" & server
";ARServerPort=5280;UID=" & UserName & ";PWD=" & password
";ARAuthentication=;ARDiaryDescend=1;ARUseUn" _
), Array("derscores=1;ARNameReplace=1;SERVER=NotTheServer"))
Destination:=Sheet10.Range("A2"))
.CommandText = Array( _
"SELECT HPD_HelpDesk.Create_Time, HPD_HelpDesk.Case_ID_
HPD_HelpDesk.Status, HPD_HelpDesk.Escalated_, HPD_HelpDesk.Base
HPD_HelpDesk.Assigned_To_Group_, HPD_HelpDesk.Category
HPD_HelpDesk.Type, HPD_HelpDesk.Item," _
, _
" HPD_HelpDesk.Machine_Name_, HPD_HelpDesk.Description
HPD_HelpDesk.Work_Log, HPD_HelpDesk.Requester_Name_
HPD_HelpDesk.Phone_Number, HPD_HelpDesk.Building, HPD_HelpDesk.Floor
HPD_HelpDesk.Room_Cube, HPD_HelpDesk.Network_Jack" & Chr(13) & ""
Chr(10) & "FROM HP" _
, _
"D_HelpDesk HPD_HelpDesk" & Chr(13) & "" & Chr(10) & "WHER
(HPD_HelpDesk.Status<='Resolved') AND (HPD_HelpDesk.Base= 'ABNY')"
Chr(13) & "" & Chr(10) & "ORDER BY HPD_HelpDesk.Case_ID_")
.Name = "Query from AR System ODBC Data Source"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
' Sheet7.PivotTables("Count of Case ID").RefreshTable
' Sheet9.PivotTables("MACs").RefreshTable
End Sub
The example only shows 1 Status / Base in the where clause and a
previously stated I need to get the results for 60 bases on th
existing status...
I am trying to pull data from a SQL server app called Remedy. I
has a ODBC driver that I am using. I am trying to edit an existing se
of code to manipulate it to my conditions. The situation I have is th
existing code will only work for 1 "Base" code in the where clause an
1 Status. I support 60 Bases. I need to configure a Where clause tha
supports 60 Bases. The problem I run in to is every time I go over
bases / status where clauses I run into a type mismatch.
Here is my code can anyone help..
Public Sub Querry()
' Querry of the Remedy Server For Tickets
With Sheet10.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Remedy;ARServer=" & server
";ARServerPort=5280;UID=" & UserName & ";PWD=" & password
";ARAuthentication=;ARDiaryDescend=1;ARUseUn" _
), Array("derscores=1;ARNameReplace=1;SERVER=NotTheServer"))
Destination:=Sheet10.Range("A2"))
.CommandText = Array( _
"SELECT HPD_HelpDesk.Create_Time, HPD_HelpDesk.Case_ID_
HPD_HelpDesk.Status, HPD_HelpDesk.Escalated_, HPD_HelpDesk.Base
HPD_HelpDesk.Assigned_To_Group_, HPD_HelpDesk.Category
HPD_HelpDesk.Type, HPD_HelpDesk.Item," _
, _
" HPD_HelpDesk.Machine_Name_, HPD_HelpDesk.Description
HPD_HelpDesk.Work_Log, HPD_HelpDesk.Requester_Name_
HPD_HelpDesk.Phone_Number, HPD_HelpDesk.Building, HPD_HelpDesk.Floor
HPD_HelpDesk.Room_Cube, HPD_HelpDesk.Network_Jack" & Chr(13) & ""
Chr(10) & "FROM HP" _
, _
"D_HelpDesk HPD_HelpDesk" & Chr(13) & "" & Chr(10) & "WHER
(HPD_HelpDesk.Status<='Resolved') AND (HPD_HelpDesk.Base= 'ABNY')"
Chr(13) & "" & Chr(10) & "ORDER BY HPD_HelpDesk.Case_ID_")
.Name = "Query from AR System ODBC Data Source"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
' Sheet7.PivotTables("Count of Case ID").RefreshTable
' Sheet9.PivotTables("MACs").RefreshTable
End Sub
The example only shows 1 Status / Base in the where clause and a
previously stated I need to get the results for 60 bases on th
existing status...