I'm creating a pivot table using vb.net and the data is from sqlserver
(desktop). I have been successful at creating the pivot table, which includes
a 'date' column field. I'd like to group the date column field by months and
quarters, but can't come up with the correct code. Here is the code I've
written to create the pivot table. Any help to code grouping the date column
field would be appreciated.
Thanks,
Jerry
Imports System
Imports System.Runtime.InteropServices
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.
EventArgs) Handles MyBase.Load
'' COMs for excel and office references were added to project
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheets As Excel.Worksheets
Dim ConnectionString As String = _
"Server=jerry;" & _
"DataBase=CTS;" & _
"user ID=sa;password=ruth2jerry"
Dim ptSQL As String
ptSQL = "SELECT * FROM tblActualForecast2"
Dim cnSQL As SqlConnection
Dim cmSQL As SqlCommand
Dim drSQL As SqlDataReader
Dim dsSQL As DataSet
Dim Row As Integer
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlApp.Visible = False
Try
cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()
cmSQL = New SqlCommand(ptSQL, cnSQL)
drSQL = cmSQL.ExecuteReader
xlSheet.Cells(1, 1).value = "ActualCase"
xlSheet.Cells(1, 2).value = "ActualDate"
xlSheet.Cells(1, 3).value = "ActualComm"
xlSheet.Cells(1, 4).value = "ActualAcctHandler"
Row = 2
While drSQL.Read
xlSheet.Cells(Row, 1).value = drSQL.Item("actualcase")
xlSheet.Cells(Row, 2).value = drSQL.Item("actualDate")
xlSheet.Cells(Row, 3).value = drSQL.Item("actualComm")
xlSheet.Cells(Row, 4).value = drSQL.Item("actualAcctHandler")
Row = Row + 1
End While
Catch ex As Exception
MsgBox(ex.Message)
Finally
drSQL.Close()
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
End Try
xlSheet.Cells.EntireColumn.AutoFit()
Dim xllastcell As String
xllastcell = xlSheet.Cells.SpecialCells(Excel.XlCellType.
xlCellTypeLastCell).Address
xlApp.Sheets.Add.name = "CTS Pivot Table"
xlBook.ActiveSheet.PivotTableWizard(Excel.XlPivotT ableSourceType.
xlDatabase, xlSheet.Range("A1:" & xllastcell))
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualCase").
Orientation = Excel.XlPivotFieldOrientation.xlRowField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualDate").
Orientation = Excel.XlPivotFieldOrientation.xlColumnField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualComm").
Orientation = Excel.XlPivotFieldOrientation.xlDataField
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualAcctHandler").
Orientation = Excel.XlPivotFieldOrientation.xlPageField
' Get the last cell in the pivot table.
xllastcell = xlBook.ActiveSheet.Cells.SpecialCells(Excel.XlCell Type.
xlCellTypeLastCell).Address
' Set the number format for the data cells
xlBook.ActiveSheet.range("B5:" & xllastcell).numberformat = "$##,##0.
00"
'' Worth considering ---
xlApp.CommandBars("PivotTable").Visible = False
'''' Group the selection... ??
'''' Here's where I need to the help to group the date column..
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualcase").Subtotals
(1) = False
xlBook.ActiveSheet.Cells.EntireColumn.AutoFit()
xlApp.Visible = True
End Sub
End Class 3 10167
Jerry - the code you're using is at least syntactically correct. I created
a dummy db with those fields and created a pivot table from there. Here's
what I'd advise doing since I don't have access to yoru db (BTW, as a
security measure, change your password as soon as you read this and disable
your SA account - the bad guys have a lot of bots that paruse these
newsgroups looking for stuff just like that- not trying to lecture, just
looking out ;-) ).
Record a new macro. Then, Go ahead and use Get External data and then pull
the data in using Microsoft Query. This code you can ignore although to be
honest, in many instances it's a lot faster than using ADO.NET to get t the
same place.. Then just manually create the code for your pivot table and
stop recording the macro. You'll have a ton of crap code written, but it
will ultimately provide you with the correct code . the only problem other
than erroneous code (which it tneds to generate a lot of) is that it may
hard code stuff in there instead of using relative references. In general
though, this is pretty easy to work around. I know I'm probably just
telling you stuff you already know, but without the data, it's hard for me
to figure it out.
If you want, write me out a small csv or xml file and send it to WilliamRyan
At Gmaildotcom and I'll try to work through . I'll be up for a while
tonight so i'll do what I can for you.
Cheers,
bill
"Jerry K via DotNetMonster.com" <fo***@DotNetMonster.com> wrote in message
news:53***********@DotNetMonster.com... I'm creating a pivot table using vb.net and the data is from sqlserver (desktop). I have been successful at creating the pivot table, which includes a 'date' column field. I'd like to group the date column field by months and quarters, but can't come up with the correct code. Here is the code I've written to create the pivot table. Any help to code grouping the date column field would be appreciated.
Thanks, Jerry
Imports System Imports System.Runtime.InteropServices Imports System.Data Imports System.Data.SqlClient Imports System.IO
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System. EventArgs) Handles MyBase.Load
'' COMs for excel and office references were added to project
Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlSheets As Excel.Worksheets
Dim ConnectionString As String = _ "Server=jerry;" & _ "DataBase=CTS;" & _ "user ID=sa;password=ruth2jerry"
Dim ptSQL As String ptSQL = "SELECT * FROM tblActualForecast2"
Dim cnSQL As SqlConnection Dim cmSQL As SqlCommand Dim drSQL As SqlDataReader Dim dsSQL As DataSet
Dim Row As Integer
xlApp = CType(CreateObject("Excel.Application"), Excel.Application) xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook) xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlApp.Visible = False
Try cnSQL = New SqlConnection(ConnectionString) cnSQL.Open() cmSQL = New SqlCommand(ptSQL, cnSQL) drSQL = cmSQL.ExecuteReader
xlSheet.Cells(1, 1).value = "ActualCase" xlSheet.Cells(1, 2).value = "ActualDate" xlSheet.Cells(1, 3).value = "ActualComm" xlSheet.Cells(1, 4).value = "ActualAcctHandler"
Row = 2
While drSQL.Read xlSheet.Cells(Row, 1).value = drSQL.Item("actualcase") xlSheet.Cells(Row, 2).value = drSQL.Item("actualDate") xlSheet.Cells(Row, 3).value = drSQL.Item("actualComm") xlSheet.Cells(Row, 4).value = drSQL.Item("actualAcctHandler") Row = Row + 1 End While
Catch ex As Exception MsgBox(ex.Message) Finally drSQL.Close() cnSQL.Close() cmSQL.Dispose() cnSQL.Dispose() End Try
xlSheet.Cells.EntireColumn.AutoFit()
Dim xllastcell As String xllastcell = xlSheet.Cells.SpecialCells(Excel.XlCellType. xlCellTypeLastCell).Address
xlApp.Sheets.Add.name = "CTS Pivot Table"
xlBook.ActiveSheet.PivotTableWizard(Excel.XlPivotT ableSourceType. xlDatabase, xlSheet.Range("A1:" & xllastcell))
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualCase"). Orientation = Excel.XlPivotFieldOrientation.xlRowField xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualDate"). Orientation = Excel.XlPivotFieldOrientation.xlColumnField xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualComm"). Orientation = Excel.XlPivotFieldOrientation.xlDataField xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualAcctHandler"). Orientation = Excel.XlPivotFieldOrientation.xlPageField
' Get the last cell in the pivot table. xllastcell = xlBook.ActiveSheet.Cells.SpecialCells(Excel.XlCell Type. xlCellTypeLastCell).Address ' Set the number format for the data cells xlBook.ActiveSheet.range("B5:" & xllastcell).numberformat = "$##,##0. 00"
'' Worth considering --- xlApp.CommandBars("PivotTable").Visible = False
'''' Group the selection... ?? '''' Here's where I need to the help to group the date column..
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualcase").Subtotals (1) = False xlBook.ActiveSheet.Cells.EntireColumn.AutoFit()
xlApp.Visible = True
End Sub
End Class
Jerry - the code you're using is at least syntactically correct. I created
a dummy db with those fields and created a pivot table from there. Here's
what I'd advise doing since I don't have access to yoru db (BTW, as a
security measure, change your password as soon as you read this and disable
your SA account - the bad guys have a lot of bots that paruse these
newsgroups looking for stuff just like that- not trying to lecture, just
looking out ;-) ).
Record a new macro. Then, Go ahead and use Get External data and then pull
the data in using Microsoft Query. This code you can ignore although to be
honest, in many instances it's a lot faster than using ADO.NET to get t the
same place.. Then just manually create the code for your pivot table and
stop recording the macro. You'll have a ton of crap code written, but it
will ultimately provide you with the correct code . the only problem other
than erroneous code (which it tneds to generate a lot of) is that it may
hard code stuff in there instead of using relative references. In general
though, this is pretty easy to work around. I know I'm probably just
telling you stuff you already know, but without the data, it's hard for me
to figure it out.
If you want, write me out a small csv or xml file and send it to WilliamRyan
At Gmaildotcom and I'll try to work through . I'll be up for a while
tonight so i'll do what I can for you.
Cheers,
bill
"Jerry K via DotNetMonster.com" <fo***@DotNetMonster.com> wrote in message
news:53***********@DotNetMonster.com... I'm creating a pivot table using vb.net and the data is from sqlserver (desktop). I have been successful at creating the pivot table, which includes a 'date' column field. I'd like to group the date column field by months and quarters, but can't come up with the correct code. Here is the code I've written to create the pivot table. Any help to code grouping the date column field would be appreciated.
Thanks, Jerry
Imports System Imports System.Runtime.InteropServices Imports System.Data Imports System.Data.SqlClient Imports System.IO
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System. EventArgs) Handles MyBase.Load
'' COMs for excel and office references were added to project
Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlSheets As Excel.Worksheets
Dim ConnectionString As String = _ "Server=jerry;" & _ "DataBase=CTS;" & _ "user ID=sa;password=ruth2jerry"
Dim ptSQL As String ptSQL = "SELECT * FROM tblActualForecast2"
Dim cnSQL As SqlConnection Dim cmSQL As SqlCommand Dim drSQL As SqlDataReader Dim dsSQL As DataSet
Dim Row As Integer
xlApp = CType(CreateObject("Excel.Application"), Excel.Application) xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook) xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlApp.Visible = False
Try cnSQL = New SqlConnection(ConnectionString) cnSQL.Open() cmSQL = New SqlCommand(ptSQL, cnSQL) drSQL = cmSQL.ExecuteReader
xlSheet.Cells(1, 1).value = "ActualCase" xlSheet.Cells(1, 2).value = "ActualDate" xlSheet.Cells(1, 3).value = "ActualComm" xlSheet.Cells(1, 4).value = "ActualAcctHandler"
Row = 2
While drSQL.Read xlSheet.Cells(Row, 1).value = drSQL.Item("actualcase") xlSheet.Cells(Row, 2).value = drSQL.Item("actualDate") xlSheet.Cells(Row, 3).value = drSQL.Item("actualComm") xlSheet.Cells(Row, 4).value = drSQL.Item("actualAcctHandler") Row = Row + 1 End While
Catch ex As Exception MsgBox(ex.Message) Finally drSQL.Close() cnSQL.Close() cmSQL.Dispose() cnSQL.Dispose() End Try
xlSheet.Cells.EntireColumn.AutoFit()
Dim xllastcell As String xllastcell = xlSheet.Cells.SpecialCells(Excel.XlCellType. xlCellTypeLastCell).Address
xlApp.Sheets.Add.name = "CTS Pivot Table"
xlBook.ActiveSheet.PivotTableWizard(Excel.XlPivotT ableSourceType. xlDatabase, xlSheet.Range("A1:" & xllastcell))
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualCase"). Orientation = Excel.XlPivotFieldOrientation.xlRowField xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualDate"). Orientation = Excel.XlPivotFieldOrientation.xlColumnField xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualComm"). Orientation = Excel.XlPivotFieldOrientation.xlDataField xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualAcctHandler"). Orientation = Excel.XlPivotFieldOrientation.xlPageField
' Get the last cell in the pivot table. xllastcell = xlBook.ActiveSheet.Cells.SpecialCells(Excel.XlCell Type. xlCellTypeLastCell).Address ' Set the number format for the data cells xlBook.ActiveSheet.range("B5:" & xllastcell).numberformat = "$##,##0. 00"
'' Worth considering --- xlApp.CommandBars("PivotTable").Visible = False
'''' Group the selection... ?? '''' Here's where I need to the help to group the date column..
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualcase").Subtotals (1) = False xlBook.ActiveSheet.Cells.EntireColumn.AutoFit()
xlApp.Visible = True
End Sub
End Class
Thanks Bill. I realized the sa,pw after I sent the message. I'll fix it.
I recorded a macro and used the code, modified a bit because I'm using
VB.net and am referencing excel. So my code looks like the following... it
contains hard cell references, but that's ok for now. It worked up until the
last few lines that set up the grouping and that's when I get an errror
message about object not set. If I comment out the grouping lines it works
great.
xlBook.PivotCaches.Add(SourceType:=Excel.XlPivotTa bleSourceType.xlDatabase,
SourceData:= _
"Sheet1!R1C1:R37C4").CreatePivotTable(TableDestina tion:="",
TableName:= _
"PivotTable1")
xlBook.ActiveSheet.PivotTableWizard(TableDestinati on:=xlBook.ActiveSheet.Cells(3, 1))
xlBook.ActiveSheet.Cells(3, 1).Select()
xlBook.ActiveSheet.PivotTables("PivotTable1").Smal lGrid = False
With
xlBook.ActiveSheet.PivotTables("PivotTable1").Pivo tFields("ActualCase")
.Orientation = Excel.XlPivotFieldOrientation.xlRowField
.Position = 1
End With
With
xlBook.ActiveSheet.PivotTables("PivotTable1").Pivo tFields("ActualDate")
.Orientation = Excel.XlPivotFieldOrientation.xlColumnField
.Position = 1
End With
With
xlBook.ActiveSheet.PivotTables("PivotTable1").Pivo tFields("ActualComm")
.Orientation = Excel.XlPivotFieldOrientation.xlDataField
.Position = 1
End With
With
xlBook.ActiveSheet.PivotTables("PivotTable1").Pivo tFields("ActualAcctHandler")
.Orientation = Excel.XlPivotFieldOrientation.xlPageField
.Position = 1
End With
''''''' Here's where the trouble is ---
Dim array As Array
Dim xlrange As Excel.Range
xlrange = xlBook.ActiveSheet.range("B3")
xlrange.Group(Start:=True, End:=True, Periods:=array(False, _
,False, False, False, True, True, False))
"Jerry K via DotNetMonster.com" wrote: I'm creating a pivot table using vb.net and the data is from sqlserver (desktop). I have been successful at creating the pivot table, which includes a 'date' column field. I'd like to group the date column field by months and quarters, but can't come up with the correct code. Here is the code I've written to create the pivot table. Any help to code grouping the date column field would be appreciated.
Thanks, Jerry
Imports System Imports System.Runtime.InteropServices Imports System.Data Imports System.Data.SqlClient Imports System.IO
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System. EventArgs) Handles MyBase.Load
'' COMs for excel and office references were added to project
Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlSheets As Excel.Worksheets
Dim ConnectionString As String = _ "Server=jerry;" & _ "DataBase=CTS;" & _ "user ID=sa;password=ruth2jerry"
Dim ptSQL As String ptSQL = "SELECT * FROM tblActualForecast2"
Dim cnSQL As SqlConnection Dim cmSQL As SqlCommand Dim drSQL As SqlDataReader Dim dsSQL As DataSet
Dim Row As Integer
xlApp = CType(CreateObject("Excel.Application"), Excel.Application) xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook) xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlApp.Visible = False
Try cnSQL = New SqlConnection(ConnectionString) cnSQL.Open() cmSQL = New SqlCommand(ptSQL, cnSQL) drSQL = cmSQL.ExecuteReader
xlSheet.Cells(1, 1).value = "ActualCase" xlSheet.Cells(1, 2).value = "ActualDate" xlSheet.Cells(1, 3).value = "ActualComm" xlSheet.Cells(1, 4).value = "ActualAcctHandler"
Row = 2
While drSQL.Read xlSheet.Cells(Row, 1).value = drSQL.Item("actualcase") xlSheet.Cells(Row, 2).value = drSQL.Item("actualDate") xlSheet.Cells(Row, 3).value = drSQL.Item("actualComm") xlSheet.Cells(Row, 4).value = drSQL.Item("actualAcctHandler") Row = Row + 1 End While
Catch ex As Exception MsgBox(ex.Message) Finally drSQL.Close() cnSQL.Close() cmSQL.Dispose() cnSQL.Dispose() End Try
xlSheet.Cells.EntireColumn.AutoFit()
Dim xllastcell As String xllastcell = xlSheet.Cells.SpecialCells(Excel.XlCellType. xlCellTypeLastCell).Address
xlApp.Sheets.Add.name = "CTS Pivot Table"
xlBook.ActiveSheet.PivotTableWizard(Excel.XlPivotT ableSourceType. xlDatabase, xlSheet.Range("A1:" & xllastcell))
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualCase"). Orientation = Excel.XlPivotFieldOrientation.xlRowField xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualDate"). Orientation = Excel.XlPivotFieldOrientation.xlColumnField xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualComm"). Orientation = Excel.XlPivotFieldOrientation.xlDataField xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualAcctHandler"). Orientation = Excel.XlPivotFieldOrientation.xlPageField
' Get the last cell in the pivot table. xllastcell = xlBook.ActiveSheet.Cells.SpecialCells(Excel.XlCell Type. xlCellTypeLastCell).Address ' Set the number format for the data cells xlBook.ActiveSheet.range("B5:" & xllastcell).numberformat = "$##,##0. 00"
'' Worth considering --- xlApp.CommandBars("PivotTable").Visible = False
'''' Group the selection... ?? '''' Here's where I need to the help to group the date column..
xlBook.ActiveSheet.PivotTables(1).PivotFields("act ualcase").Subtotals (1) = False xlBook.ActiveSheet.Cells.EntireColumn.AutoFit()
xlApp.Visible = True
End Sub
End Class This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Prasad Patil |
last post by:
Hi,
I have created a report in excel, it uses Pivot tables.
The excel has two sheets
1: Pivot ( Contains the Pivot Table)
2: Data (Data Requred to populate the pivot table
I create an...
|
by: Clive Moss |
last post by:
I am in the middle of writing a system for a client who has Access 2000
installed on 8 PC's
I have Access 2002 and the system I have written relies on pivot tables to
display results. I now find...
|
by: Johnny Meredith |
last post by:
Dear All,
I have an Access database that tracks the progress of income tax
audits. When the taxing authorities make a change (an "Adjustment"), I
record the pertinent information in the...
|
by: Rami |
last post by:
Has any body tried using pivot tables in C#
I am trying to achieve pivot table functionality ( Rendering Row fields / Column Fields / Data fields ) in C# with Excel 2003. I have complete data in one...
|
by: Jerome Ranch |
last post by:
I consider myself an Excel PT wizard of sorts, but now I have a
situation with so much infromationthat I need to categorize and
summarize, that I will use access to manage it.
Interestingly,...
|
by: Zlatko Matić |
last post by:
I have experienced some problems with total operations (sum, min, max, avg
etc) in pivot tables nad pivot charts in .mde.
In .mdb I can activate any totals operation. on both notebook and desktop...
|
by: nikila |
last post by:
Hi,
I have to create excel pivot tables from vb.net. Already I am creating excel
file using oledb connection. I want to use the same to create the excel
pivot tables. Can anyone please help me...
|
by: STUFIX |
last post by:
Hi all,
I created a pivot table that allowed users to select certain customers from a drop down list and display the relevant data - it worked fine but has now stopped allowing them to do that.
...
|
by: mld01s |
last post by:
I really need help!!! I dont know if its possible to share pivot tables, or see pivot tables in other machines that the one where the tables were created.
This is what happens:
I created a...
|
by: chopin |
last post by:
Pivot tables in Access are very powerful. The major flaw is presentation. I know exporting to excel is an option, however I want to merge many pivot tables into one report in Access. Is there any...
|
by: Rina0 |
last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
|
by: erikbower65 |
last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps:
1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal.
2. Connect to...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |