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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
|
Sub GetDataTechSupport()
Sheets("Data").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Dim SelectionSql, Orderby As String
SelectionSql = "`Export Tech Support`.Week, "
SelectionSql = SelectionSql & "`Export Tech Support`.Date_,"
SelectionSql = SelectionSql & "`Export Tech Support`.Hours,"
SelectionSql = SelectionSql & "`Export Tech Support`.Initial_,"
SelectionSql = SelectionSql & "`Export Tech Support`.Activity,"
SelectionSql = SelectionSql & "`Export Tech Support`.Costs_Incurred,"
SelectionSql = SelectionSql & "`Export Tech Support`.Miles,"
SelectionSql = SelectionSql & "`Export Tech Support`.Project_Name,"
SelectionSql = SelectionSql & "`Export Tech Support`.Project_Number,"
SelectionSql = SelectionSql & "`Export Tech Support`.Category,"
SelectionSql = SelectionSql & "`Export Tech Support`.Client_No,"
SelectionSql = SelectionSql & "`Export Tech Support`.Xcharge_Recipient,"
SelectionSql = SelectionSql & "`Export Tech Support`.RateHours,"
SelectionSql = SelectionSql & "`Export Tech Support`.ID_ProdClass,"
SelectionSql = SelectionSql & "`Export Tech Support`.Compensation_Event,"
SelectionSql = SelectionSql & "`Export Tech Support`.Mats_Labour_Commitment"
Orderby = "ORDER BY `Export Tech Support`.Project_Name, `Export Tech Support`.Date_"
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC; DSN=MS Access Database; DBQ=" & PathMasterDB & ";DefaultDir=" & DirMasterDB & ";DriverId=25;FIL=MS Access;Max" _
), Array("BufferSize=2048;PageTimeout=5;;UID=admin;")), Destination:=Range( _
"A1"))
.CommandText = Array( _
"SELECT Selection" & Chr(13) & "" & Chr(10) & "FROM `" & PathMasterDB & "`.`Export Tech Support` `Export Tech Support`" & Chr(13) & "" & Chr(10) & Orderby)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
Sheets("Main").Select
Range("A1").Select
End Sub |
Partager