Excel and VBA Newbie

[Meadsie]

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

 

[Trevor G]

When declaring a date in Excel VBA it assumes you are using USA system, perhaps changing this

Dim create_timestamp  As Date
To
Dim create_timestamp  As Long

 

[Meadsie]

Hiya

Unfortunately that hasnt worked...