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
| Sub ExtractData(SqlText As String, SheetName As String)
' Uncomment next line to get information on every SQL query run
' MsgBox SqlText, vbOKOnly, "SQL"
MsgBox SqlText, vbInformation, "SQL"
' Removing existing spreadsheet
If (WorksheetExists(SheetName)) Then
Application.DisplayAlerts = False
Sheets(SheetName).Delete
Application.DisplayAlerts = True
End If
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = SheetName
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;driver={Adaptive Server Enterprise};database=maBase;server=monHosta;port=5510;UID=monUser;Password=monPwd;" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array(SqlText)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = SheetName
.Refresh BackgroundQuery:=False
End With
ActiveSheet.Cells.EntireColumn.AutoFit
End Sub |
Partager