Excel report doesn't work in windows 7

Posted on 2014-03-18
Medium Priority
Last Modified: 2014-03-20
I have an old excel report application which still works in windows XP and Excel 2003. But after I upgraded my pc to windows 7 64 bit and Office 2007, the report throws error below. Actually I tested on both windows 7 32bit and 64bit, it's the same error. So, I guess it's related to windows xp and 7, not 32bit or 64bit;

"There was a problem connecting to the database. Please try running the report again. If the problem persists, contact your system administrator. Login failed for user 'reporter'" I'm not sure this uses ODBC, either. The server is SQL 2000 Express

I'm not good at excel, The excel report file shows as xlt and when I open the report from office 2007, then go to macro, it shows a bunch of codes. Where do I start to solve the connection problem, in administrative tools/odbc or modifying the macro?
Question by:crcsupport
  • 3
  • 2
LVL 28

Assisted Solution

omgang earned 2000 total points
ID: 39938326
Login failed for user 'reporter'
sure seems to indicate you're getting to the database but not getting authenticated.

Maybe best to start by copying all the code you are seeing in the VBE and pasting it here.
OM Gang

Author Comment

ID: 39942522
I opened the macro tab in office 2007, it shows as below. The script 1 part is from workbook and script 2 is from cDatabase under Class modules. I noticed the script 1 in workbook says on the top 'database connection is passed from Add-in' So, where does it mean I have to modify the connection string?

script 1 in workbook
Option Explicit

' database connection object passed in from Add-In
Private mcnAddInConnection As ADODB.Connection

Public Sub RetrieveReport(ByRef lReturnCode As Long)
    On Error GoTo ErrorHandler
    ' Database access variables
    Dim sSQL                        As String
    Dim rsReport                    As ADODB.Recordset
    Dim oDatabase                   As cDatabase
    ' Values used for populating the spreadsheet
    Dim lInboundCalls               As Long
    Dim lOutboundCalls              As Long
    Dim dblAvgTalkTimeInbound       As Double
    Dim dblAvgTalkTimeOutbound      As Double
    Dim dtDate                      As Date
    Dim sInterval                   As String
    Dim lRow                        As Long
    Dim sTitle                      As String
    Dim bOK                         As Boolean

    ' Report parameter database-formatted values
    Dim sDBQueue                    As String
    Dim sDBPeriod                   As String
    Dim sDBInterval                 As String
    Dim sDBStartDate                As String
    Dim sDBEndDate                  As String

    ' Report parameter display-formatted values (descriptions)
    Dim sQueueDesc                  As String
    Dim sIntervalDesc               As String

    bOK = True
    ' open the connection to the database
    Set oDatabase = New cDatabase
    ' Setup necessary references to the worksheets in the workbook
    Call SetWorksheetReferences
    ' set the date constants
    Call SetDateFormatStrings
    On Error Resume Next
    ' Drill down the Parameters by reading the report parameter values on the options sheet
    ' Get Queue
    Call GetParmValue(wsOptions, QUEUE_WG_PARM, sDBQueue, sQueueDesc, bOK)
    If bOK Then
        ' Get Period
        Call GetParmValue(wsOptions, PERIOD_PARM, sDBPeriod, sPeriodDesc, bOK)
        If bOK Then
            ' Get Interval
            Call GetParmValue(wsOptions, INTERVAL_PARM, sDBInterval, sIntervalDesc, bOK)
            If bOK Then
                ' Parse Period
                Call Common.ParseDateRange(sDBPeriod, sDBStartDate, sDBEndDate, bOK)
            End If
        End If
    End If
    If Not bOK Then
        lReturnCode = 10
        Application.ScreenUpdating = False
        ' Open the ADO connection to the SQL Server
        Call oDatabase.OpenDBConnection(mcnAddInConnection)
        ' Execute the stored procedure for the report with values from the options sheet
        sSQL = "Exec sp_rptCallTrends " & sDBQueue
        sSQL = sSQL & ", " & sDBInterval
        sSQL = sSQL & ", " & sDBStartDate
        sSQL = sSQL & ", " & sDBEndDate
        sSQL = sSQL & ", 1"
        ' pass in the SQL to the ADO Recordset
        Set rsReport = oDatabase.OpenRecordset(sSQL)
        If (rsReport.EOF And rsReport.BOF) Then
            ' check for an error in the stored procedure
            If CLng(oDatabase.ErrorInfo.Item(2)) <> 0 Then
                ' write the error info to the third sheet on the report
                With wsOptions.Range("A100")
                    .Value = "Error Information:"
                    .Value = .Value & Chr(10) & "   SQL = " & oDatabase.ErrorInfo.Item(1)
                    .Value = .Value & Chr(10) & "   Error Number = " & oDatabase.ErrorInfo.Item(2)
                    .Value = .Value & Chr(10) & "   Error Description = " & oDatabase.ErrorInfo.Item(3)
                End With
            End If
            lReturnCode = 11
            bOK = False
            With wsData
                ' Initialize the Data sheet
                ' Setup Data sheet columns
                Select Case sDBInterval
                    Case tvTimePeriodHour
                        .Columns("A").NumberFormat = msShortDateFormat & " " & msTimeFormat ' "m/d/yyyy  h:mm AM/PM"
                    Case tvTimePeriodDay, tvTimePeriodWeek
                        .Columns("A").NumberFormat = msShortDateFormat ' "m/d/yyyy"
                    Case tvTimePeriodMonth, tvTimePeriodQuarter
                        .Columns("A").NumberFormat = "m/yyyy"
                    Case tvTimePeriodYear
                        .Columns("A").NumberFormat = "yyyy"
                End Select
                .Range("A1").Value = GetString(tvResRepOutboundCallTrendsInterval) ' "Interval"
                .Range("B1").Value = GetString(tvResRepOutboundCallTrendsInboundCalls) ' "Inbound Calls"
                .Range("C1").Value = GetString(tvResRepOutboundCallTrendsOutboundCalls) ' "Outbound Calls"
                .Range("D1").Value = GetString(tvResRepOutboundCallTrendsAVGTTIB) ' "Average Talk Time Inbound"
                .Range("E1").Value = GetString(tvResRepOutboundCallTrendsAVGTT) ' "Average Talk Time Outbound""
            End With
            ' Populate Spreadsheet
            lRow = FIRST_DATA_ROW
            Do While Not rsReport.EOF
                sInterval = rsReport!Interval
                lInboundCalls = rsReport!InboundCalls
                lOutboundCalls = rsReport!OutboundCalls
                dblAvgTalkTimeInbound = rsReport!AverageTalkTimeInbound
                dblAvgTalkTimeOutbound = rsReport!AverageTalkTimeOutbound
                dtDate = GetRecordsetDate(sInterval, sDBInterval)
                With wsData
                    .Range("A" & CStr(lRow)).Value = dtDate
                    .Range("B" & CStr(lRow)).Value = lInboundCalls
                    .Range("C" & CStr(lRow)).Value = lOutboundCalls
                    .Range("D" & CStr(lRow)).Value = dblAvgTalkTimeInbound
                    .Range("E" & CStr(lRow)).Value = dblAvgTalkTimeOutbound
                End With
                lRow = lRow + 1
            ' sort the data sheet by date
            wsData.Range("A2", "E" & CStr(lRow - 1)).Sort Key1:=wsData.Rows(2), Header:=xlNo, Orientation:=xlSortColumns
            ' set the charts axes values and read the resource file for the report title
            Call SetupChartTitles(tvResRepOutboundCallTrendsY1Axis, tvResRepOutboundCallTrendsRptTitle, _
                "B4", tvResRepOutboundCallTrendsY2Axis)
            ' Set the title and data range
            With wsChart
                .HasTitle = True
                .ChartTitle.Text = wsOptions.Range("B4") & IIf(UCase(wsOptions.Range("C4")) = "YES", " - " & sQueueDesc, "")
                .ChartWizard Source:=wsData.Range("A1", "E" & CStr(lRow - 1))
            End With
            Call SetMinimumAndMaximumScales
            Call SetupTimeAxis(wsChart, sDBInterval, sIntervalDesc)
            If Not (rsReport.EOF And rsReport.BOF) Then
                Call SetHeadersAndFooters(wsChart, wsData, sQueueDesc, sDBPeriod, tvResRepOutboundCallTrendsRptTitle)
            End If
            ' we need to explicity state the legend's placement, since otherwise x-axis labels will get cut off
            wsChart.Legend.Position = xlLegendPositionBottom
        End If
        Application.ScreenUpdating = True
        ' close the connection to the SQL Server
        Set oDatabase = Nothing
    End If
    Exit Sub

    lReturnCode = 12
End Sub

Open in new window

Script 2 in cDatabase
Option Explicit

' Please do not modify this module within any particular report. It is a shared module
' with common functions and subroutines and any changes will affect all reports.
' Developers: If changes are required, do so within Visual Basic itself as as seperate
' module. Then, save the module, check out all reports from VSS, and run the addMod utility.
' Customers: If this report has been shipped and you need to make a change to this module,
' please make sure that after making modifications, you export the module as a .cls file
' extension, and then import the module into ALL of the reports, thus replacing the existing module

'ADO variables
Private mcnConnection As ADODB.Connection
Private mcolErrorInfo As New Collection

Public Sub OpenDBConnection(ByVal Connection As ADODB.Connection)
    On Error GoTo ErrorHandler

    Dim sConnString As String

    ' instantiate the ADO connection object
    Set mcnConnection = New ADODB.Connection
    sConnString = Connection.ConnectionString

    With mcnConnection
        .ConnectionString = sConnString
    End With

    Exit Sub

    MsgBox GetString(TVResStringAddInMsgConnectionError) & " " & Err.Description, vbOKOnly + vbCritical
End Sub

Public Sub CloseDBConnection()
    On Error GoTo ErrorHandler
    ' close out all ADO variables; first, evaluate
    ' whether the connection has been established
    If Not mcnConnection Is Nothing Then
        ' the connection is open
        Set mcnConnection = Nothing
        ' the connection is not currently open
        Exit Sub
    End If

    Exit Sub

    MsgBox GetString(TVResStringAddInMsgInternalError) & " " & Err.Description, vbOKOnly + vbCritical
End Sub

Public Function OpenRecordset(ByRef sSQLExec As String) As ADODB.Recordset
    On Error GoTo ErrorHandler
    ' execute the stored procedure for the report
    Set OpenRecordset = mcnConnection.Execute(sSQLExec)
    Exit Function
    MsgBox GetString(TVResStringAddInMsgConnectionError) & " " & Err.Description, vbOKOnly + vbCritical
    ' add the error info and SQL to a Collection
    With mcolErrorInfo
        .Add sSQLExec
        .Add CStr(Err.Number)
        .Add CStr(Err.Description)
    End With
End Function

Private Function CheckDBConnection() As Boolean
    On Error Resume Next
    If Not mcnConnection Is Nothing Then
        CheckDBConnection = True
        CheckDBConnection = False
    End If
End Function

Private Sub Class_Initialize()
    On Error Resume Next
    ' clear the errors Collection
    With mcolErrorInfo
        Do While (.Count > 1)
            .Remove 1
    End With
End Sub

Private Sub Class_Terminate()
    On Error Resume Next
    ' close the connection to the server
    Call CloseDBConnection
End Sub

Public Property Get ErrorInfo() As Collection
    On Error Resume Next
    Set ErrorInfo = mcolErrorInfo
End Property

Open in new window

LVL 28

Accepted Solution

omgang earned 2000 total points
ID: 39942773
<<So, where does it mean I have to modify the connection string?>>

That's a great question and, I'm betting, the whole cause of your issue.   You have an Excel Add-In somewhere that contains the db connection information.

There are some typical locations for Add-ins to be saved but they really can be anywhere

OM Gang

Author Comment

ID: 39943351
I found the Add-in, it's located in c:\program files(x86)\televantage\reports.
Now, I opened it in 2007, it's blank, just blue background. Then I opened VBE by pressing alt+F11, the project 'TVReports.xla', it asks password to open it. :)
I'm pretty much F*ed. lol

Author Comment

ID: 39943357
how to crack password in vba project: http://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project

I will try this, but not sure

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question