• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

My web page hangs while trying to fetch data into my Pivot Cache

Hi I am trying to import some data into an Excel Pivot Cache. The codes are in VB.NET. As soon as the PivotTables.Add() is encountered, my browser just hangs!! Nothing happens, no error nothing! Can someone help??

Imports System.Data.OracleClient
Imports System.Runtime.InteropServices
Imports System.IO

Public Class ASP_Pivot_Table
    Dim strConnection As String
    Dim strSQL As String
    Dim xlApp As Excel.Application
    Dim m_oExcelApp As Excel.ApplicationClass  'method
    Dim m_oBooks As Excel.Workbooks       'interface - collection of all Workbook objects that are currently open in the Microsoft Excel Application
    Dim m_oBook As Excel._Workbook    'interface
    Dim m_oSheet As Excel._Worksheet 'interface
    Dim m_oPt As Excel.PivotTable
    Dim m_sReportTemplate As String
    Dim m_nReportIndex As Int32
    Dim m_sDataTemplate As String
    Dim m_sExportFormat As String
    Dim m_sOutputCache As String

    Public Property setQuery()
            Return strSQL
        End Get
        Set(ByVal Value)
            strSQL = Value
        End Set
    End Property

    Public Sub OpenReportTemplate()

        If (Not (m_oExcelApp Is Nothing)) Then
        End If

        m_oExcelApp = New Excel.ApplicationClass
        m_oExcelApp.Visible = True 'False
        m_oBooks = m_oExcelApp.Workbooks

        ' EXCEL VERSION 10.0 or higher
        m_oBook = m_oBooks.Open(m_sReportTemplate)

        ' END EXCEL VERSION 10.0 or higher
        m_oSheet = CType(m_oBook.Worksheets(m_nReportIndex), Excel._Worksheet)

    End Sub

    Public Sub CreatePivot()

        Dim sReportFile As String
        Dim sMessage As String
        Dim sDeleteFile As String

          ' Get temporary file name

        ' Get valid report tempate

        Dim xlRange As Excel.Range = m_oSheet.Range("B2")

        'Create the Pivot Cache
        Dim ptCache As Excel.PivotCache = m_oBook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlExternal)


            'Set up the Pivot Cache
            With ptCache
                .CommandText = strSQL
                .CommandType = Excel.XlCmdType.xlCmdSql
                .Connection = "ODBC;DRIVER={Oracle in Ora_home};UID=abcd;PWD=123;SERVER=MyServer;"
            End With

        End Try
        m_oSheet.Name = "PT_Report"

            'Create the Pivot Table
            Dim ptTable As Excel.PivotTables = CType(m_oSheet, Excel.PivotTables).PivotTables.Add(ptCache, xlRange, "PT_Report")  ' -> This line hangs!!!!!!!
   Thanks in advance
  • 4
  • 4
1 Solution
One of the biggest problems with using Excel like this is that it is not a server application, it runs in a user workspace. That means that it will try and show dialogs, etc etc and you won't ever see them as it will show them in ASPNETs user workspace, not yours.

If this is your problem, then there is no way around it other than try and guess what the dialog says and find a way not to show it. Office Web Components are the server-based component from MS that will do a lot of Excel's functionality without the user workspace problems.

Here's the original MSDN KB article I read when I discovered this, much to my disappointment:

Considerations for server-side Automation of Office

anand_007Author Commented:
I have seen this article before :). What I am doing is to create the Excel sheet on the Server and then export it to the user end. This is not a truly network environement but my users are loyal Excel followers .. they can't have enough of Excel!

As for your first comment, I had made that assumption initially. So I tested the code in a standlaone VB.NET application. I created a dll out of the Pivot table bit, created properties to set the SQL string and the connection from the calling VB.NET program and ran it. It runs beautifully there. But somehow it won't create the Pivot Table here .. I am at my wit's end!!
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

anand_007Author Commented:
Also, I commented out the Pivot Table bit where the code is hanging. This is followed by the Orientation for the elements. I commented out that bit as well and ran - the rest of the code runs and produces an output - its just this one bit that is obstinate :(

Did you have any luck with this? As no-one else has posted here, why don't you request this question be closed and refunded in the admin area, and open a new one in http://www.experts-exchange.com/Applications/MS_Office/Excel/, unless you found a solution, in which case you could PAQ and refund instead.

anand_007Author Commented:
Hi, no I've had no luck on this!! I don't suppose this is an area ventured into, unless there is a specific requirement on those lines. There is a problem while trying to populate the cache. On a plain vanilla VB.NET, you can see an MS Query connection opening and a query being fired to Oracle from Excel. However, this doesn't seem to happen on ASP.NET. The crux I feel lies in the ODBC connection - this seems to fail when you want to connect from Excel. If there were any other way to connect to Oracle, my hunch is this would succeed. But I haven't found out any such method online yet. I'm exploring OWC and third party tools
anand_007Author Commented:
How do I PAQ???
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now