Solved

Excel report doesn't work in windows 7

Posted on 2014-03-18
5
432 Views
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?
0
Comment
Question by:crcsupport
  • 3
  • 2
5 Comments
 
LVL 28

Assisted Solution

by:omgang
omgang earned 500 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
0
 
LVL 1

Author Comment

by:crcsupport
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?

macro
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
    Else
        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
        Else
            With wsData
                ' Initialize the Data sheet
                wsData.UsedRange.ClearContents
                
                ' 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
                rsReport.MoveNext
            Loop
    
            ' 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

ErrorHandler:
    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
        .Open
    End With

    Exit Sub

ErrorHandler:
    MsgBox GetString(TVResStringAddInMsgConnectionError) & " " & Err.Description, vbOKOnly + vbCritical
    End
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
        mcnConnection.Close
        Set mcnConnection = Nothing
    Else
        ' the connection is not currently open
        Exit Sub
    End If

    Exit Sub

ErrorHandler:
    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
     
ErrorHandler:
    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
    Else
        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
        Loop
    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

0
 
LVL 28

Accepted Solution

by:
omgang earned 500 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
http://social.technet.microsoft.com/Forums/office/en-US/8173df0d-1b2e-48ab-b18e-d618b14d560a/where-should-i-store-excel-addins-with-office-2007-and-windows-2007?forum=excel

OM Gang
0
 
LVL 1

Author Comment

by:crcsupport
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
0
 
LVL 1

Author Comment

by:crcsupport
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
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
OfficeMate Freezes on login or does not load after login credentials are input.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now