Excel automation. Create Pivot Table

[jadams0173]

Hey all. using VS 2005 and excel 2000

I've create an excel spreadsheet and populated it with the data I need. In the same spreadsheet on a new tab I need to create a pivot table. I've read various articles and looked at examples but I still can't get it to work.

Here is how I populate my spreadsheet

Public Shared Sub WriteSpreadsheetFromArray(ByRef strOutputArray(,) As String, Optional ByVal strExcelFileOutPath As String = "", Optional ByVal oOrientation As Excel.XlPageOrientation = Excel.XlPageOrientation.xlPortrait, Optional ByVal bVisible As Boolean = False)
        Dim objxlOutApp As Excel.Application = Nothing
        Dim objxlOutWBook As Excel.Workbook = Nothing
        Dim objxlOutSheet As Excel.Worksheet
        Dim objxlRange As Excel.Range
        Dim iProcID As Integer

        Try
            'Try to Open Excel, Add a workbook and worksheet
            objxlOutApp = New Excel.Application
            objxlOutWBook = objxlOutApp.Workbooks.Add '.Add.Sheets
            objxlOutSheet = DirectCast(objxlOutWBook.Sheets.Item(1), Excel.Worksheet)
            iProcID = GetExcelProcess()
        Catch ex As Exception
            'MessageBox.Show("While trying to Open Excel recieved error:" & ex.Message, "Export to Excel Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Try
                If Not objxlOutWBook Is Nothing Then
                    objxlOutWBook.Close()  'If an error occured we want to close the workbook
                End If
                If Not objxlOutApp Is Nothing Then
                    objxlOutApp.Quit() 'If an error occured we want to close Excel
                End If
            Catch
            End Try
            'NAR(objxlRange)
            objxlOutWBook.Close()
            NAR(objxlOutWBook)
            objxlOutApp.Quit()
            NAR(objxlOutApp)
            If iProcID > 0 Then
                KillRecentExcel(iProcID)
            End If
            Exit Sub
            'An error occured so we don't want to continue
        End Try
        Try
            objxlOutApp.DisplayAlerts = False 'This will prevent any message prompts from Excel (IE.."Do you want to save before closing?")
            objxlOutApp.Visible = False    'We don't want the app visible while we are populating it.
            'This is the easiest way I have found to populate a spreadsheet
            'First we get the range based on the size of our array
            objxlOutSheet.Range("A1").Value = "On Time Delivery Report:  " & Date.Now
            'objxlRange = objxlOutSheet.Range(Chr(strOutputArray.GetLowerBound(1) + 1 + 64) & (strOutputArray.GetLowerBound(0) + 1) & ":" & Chr(strOutputArray.GetUpperBound(1) + 1 + 64) & (strOutputArray.GetUpperBound(0) + 1))
            objxlRange = objxlOutSheet.Range(GeneralTools.ReturnBase26(strOutputArray.GetLowerBound(1) + 1) & (strOutputArray.GetLowerBound(0) + 2) & ":" & GeneralTools.ReturnBase26(strOutputArray.GetUpperBound(1) + 1) & (strOutputArray.GetUpperBound(0) + 2))
            'Next we set the value of that range to our array
            With objxlRange
                .Value = strOutputArray
                .Font.Name = "Arial"
                .Font.Size = 8
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .VerticalAlignment = Excel.XlVAlign.xlVAlignBottom
                '.Interior.ColorIndex = 0
                '.Interior.Pattern = Excel.XlPattern.xlPatternSolid
            End With

Any ideas on how to take this and make a pivot table?

 

[Sorwen]

The only thing I can suggest is to record a macro of creating a pivot table and see what it does. I've never done it in office 2000 so I couldn't help you. I don't even have the original 2003 I did as I moved everything over to 2007 and didn't keep it. I can tell you that the differences between 2003/2007 were both small and huge. I spent quite a while changing between the two.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[jadams0173]

Hey Sorwen. I've recorded a macro and am trying to get it into my vb 2005 application. So far, it has been not a fun task.

 

[Sorwen]

If you post what you have I might be able to help. I just didn't know what differences it might have between versions.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!