Hi Everyone,
This is my first post - I am ok in excel but a newbie to VB.
I have written a SQL query that retrieves the data into an Excel Spreadsheet (Template) - The results I want to show in a pivot table rather than just as list.
I can add the pivot table and save - but when i re run the report the pivot table isnt there anymore?
Can someone please help?
Below is my VB / SQL Script
kind regards
Richard
Sub GetData()
Dim create_timestamp As Date
Dim response As Variant
ShowStartDateBox:
response = InputBox(Prompt:="Please enter Start date." & vbCrLf & _
"Format is dd/mm/yyyy, e.g. Day Month Year 30/11/2012", _
Title:="Activity Report", Default:="")
Worksheets("Parms").Range("C1").Value = response
If response = "" Then Exit Sub
If IsDate(response) = False Then GoTo ShowStartDateBox
ts_date = response
Worksheets("Parms").Range("A2").Value = ts_date
Dim respTeam As String
Dim startDate As Date
respTeam = Worksheets("Parms").Range("A1").Value
startDate = Worksheets("Parms").Range("A2").Value
' delete worksheets
Dim ws As Worksheet
Application.DisplayAlerts = False
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Parms" Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True
On Error GoTo 0
' add new worksheet
Worksheets.Add(After:=Worksheets("Parms")).Name = "DataSheet"
Worksheets("DataSheet").Select ' set active sheet
sqlString = "select s.team, p.displayname, l.description, count (*) " _
& "from Event e " _
& "inner join lookup l on e.type = l.code " _
& "inner join person p on e.create_user = p.person_ref " _
& "inner join person_type pt on p.person_ref = pt.person_ref " _
& "inner join staff s on pt.person_type_ref = s.person_type_ref " _
& "Where l.code_type = '123' and e.create_timestamp > Convert (DATETIME, '" & startDate & "' , 103) and s.team = 'HKFO' " _
& "Group by p.DisplayName, e.Type, l.Description, s.team " _
& "Order by p.DisplayName; "
conString = "ODBC;DSN=P7HRGOLIVE;Database=P7HRGOLIVE"
With ActiveSheet.QueryTables.Add(Connection:=conString, _
Destination:=Range("A1"), Sql:=sqlString)
.BackgroundQuery = False
.Refresh
End With
Worksheets.Add(After:=Worksheets("DataSheet")).Name = "Timesheets"
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
End Sub
This is my first post - I am ok in excel but a newbie to VB.
I have written a SQL query that retrieves the data into an Excel Spreadsheet (Template) - The results I want to show in a pivot table rather than just as list.
I can add the pivot table and save - but when i re run the report the pivot table isnt there anymore?
Can someone please help?
Below is my VB / SQL Script
kind regards
Richard
Sub GetData()
Dim create_timestamp As Date
Dim response As Variant
ShowStartDateBox:
response = InputBox(Prompt:="Please enter Start date." & vbCrLf & _
"Format is dd/mm/yyyy, e.g. Day Month Year 30/11/2012", _
Title:="Activity Report", Default:="")
Worksheets("Parms").Range("C1").Value = response
If response = "" Then Exit Sub
If IsDate(response) = False Then GoTo ShowStartDateBox
ts_date = response
Worksheets("Parms").Range("A2").Value = ts_date
Dim respTeam As String
Dim startDate As Date
respTeam = Worksheets("Parms").Range("A1").Value
startDate = Worksheets("Parms").Range("A2").Value
' delete worksheets
Dim ws As Worksheet
Application.DisplayAlerts = False
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Parms" Then
ws.Delete
End If
Next ws
Application.DisplayAlerts = True
On Error GoTo 0
' add new worksheet
Worksheets.Add(After:=Worksheets("Parms")).Name = "DataSheet"
Worksheets("DataSheet").Select ' set active sheet
sqlString = "select s.team, p.displayname, l.description, count (*) " _
& "from Event e " _
& "inner join lookup l on e.type = l.code " _
& "inner join person p on e.create_user = p.person_ref " _
& "inner join person_type pt on p.person_ref = pt.person_ref " _
& "inner join staff s on pt.person_type_ref = s.person_type_ref " _
& "Where l.code_type = '123' and e.create_timestamp > Convert (DATETIME, '" & startDate & "' , 103) and s.team = 'HKFO' " _
& "Group by p.DisplayName, e.Type, l.Description, s.team " _
& "Order by p.DisplayName; "
conString = "ODBC;DSN=P7HRGOLIVE;Database=P7HRGOLIVE"
With ActiveSheet.QueryTables.Add(Connection:=conString, _
Destination:=Range("A1"), Sql:=sqlString)
.BackgroundQuery = False
.Refresh
End With
Worksheets.Add(After:=Worksheets("DataSheet")).Name = "Timesheets"
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
End Sub