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

Excel report doesn't work in windows 7

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?
  • 3
  • 2
2 Solutions
omgangIT ManagerCommented:
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
crcsupportAuthor Commented:
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

omgangIT ManagerCommented:
<<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
crcsupportAuthor Commented:
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
crcsupportAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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