1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
|
'ouvrir le fichier destination
Set DONNEES_DE_BASE = Application.Workbooks.Open(ThisWorkbook.Path & Parameters.Sheets("Chemin_Templates").Range("B1"))
'Lancer une requête SQL pour peupler une feuille
With DONNEES_DE_BASE.Sheets("INC actif").QueryTables.Add(Connection:= _
"ODBC;DSN=SCPROD;UID=SC_LECT;PWD=FFFFF;DBQ=SCBD;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=0;", Destination:=DONNEES_DE_BASE.Sheets("INC actif").Range("A2"))
.CommandText = "SELECT PROBSUMMARYM1.NUMBERPRGN, PROBSUMMARYM1.OPEN_TIME, PROBSUMMARYM1.UPDATE_TIME, PROBSUMMARYM1.STATUS, PROBSUMMARYM1.CLOSE_TIME, PROBSUMMARYM1.CLOSED_BY, PROBSUMMARYM1.CATEGORY, PROBSUMMARYM1.PROB" & _
"LEM_TYPE, PROBSUMMARYM1.REFERENCE_NO, PROBSUMMARYM1.COMPANY, PROBSUMMARYM1.CORP_STRUCTURE, PROBSUMMARYM1.CONTACT_NAME, PROBSUMMARYM1.BRIEF_DESCRIPTION, PROBSUMMARYM1.RESOLUTION, PROBSUMMARYM1.RESOLVED" & _
"TIME, PROBSUMMARYM1.SEVERITY_CODE, PROBSUMMARYM1.ASSIGNEE_NAME, PROBSUMMARYM1.ASSIGNMENT, PROBSUMMARYM1.PRODUCT_TYPE, PROBSUMMARYM1.SUBCATEGORY, PROBSUMMARYM1.SITE_CATEGORY, PROBSUMMARYM1.INCIDENT_ID" & _
"PROBSUMMARYM1.SAGIR_REF_EXTERNE, PROBSUMMARYM1.SAGIR_CHANGE_NO, PROBSUMMARYM1.REOPENED_BY, PROBSUMMARYM1.OTI_TOSC_CONSUMER_REFERENCE, PROBSUMMARYM1.LAST_ACTIVITY, PROBSUMMARYM1.REOPEN_TIME, PROBSUMM" & _
"ARYM1.RESOLVED_GROUP, PROBSUMMARYM1.RESOLVED_BY, PROBSUMMARYM1.PRIORITY_CODE, PROBSUMMARYM1.USER_PRIORITY, PROBSUMMARYM1.OPENED_BY, PROBSUMMARYM1.USER_TYPE, PROBSUMMARYM1.ACTION SC_SUM.PROBSUMMARYM1" & _
"PROBSUMMARYM1 WHERE (PROBSUMMARYM1.STATUS='open') OR (PROBSUMMARYM1.STATUS='resolved') OR (PROBSUMMARYM1.STATUS='updated')"
.Name = "INC_Actifs"
.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 |
Partager