Re: Excel References

From: Joe (anonymous_at_discussions.microsoft.com)
Date: 03/18/04 

Date: Thu, 18 Mar 2004 07:36:11 -0800

Rob,

I have another question, if you would be so kind.

When I run the application of my machine (XL 2002 and Windows 2000), after I re-establish the References, XL runs fine. If I run it on a Windows XP machine (w/ XL 2002), and the references are retained by Excel, it gives a Error 71, Disk not ready error. And if I run it on a Windows Server 2000 machine with XL 2002, it gives a Error 52, Bad file name or number error.

What is strange is that I am not accessing a disk or providing a file name from within the DLLs, so I'm not sure what to make of this.

Do you have any idea what this could be?

I have included the workbook and DLL code below.

Thanks,

Joe

XL workbook Code:

Option Explicit

Private WithEvents cXL As CTXS.cXLLink

Private Sub cXL_userVerified(pfUserVerified As Boolean)
    If pfUserVerified Then
        cXL.Unprotect
    Else
        MsgBox "The VERICIS® configuration work*** has not been registered to run on this workstation.", vbCritical, "Error!"
        MsgBox "Please call Camtronics at 1.800.634.5151 for assistance.", , vbNullString
        Call ExitApp
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    cXL.Protect
    Set cXL = Nothing
End Sub

Private Sub Workbook_Open()
    Set cXL = New CTXS.cXLLink
    If Not Err Then
        cXL.Workbook = ThisWorkbook <-- The code bombs on this line.
    Else
        Call ExitApp
    End If
End Sub

Private Sub ExitApp()
    application.Quit
End Sub

This is the code from the cXLLink class in the DLL:

Option Explicit

Private m_XLWbk As Excel.Workbook
Private f As frmSecurity
Private fUserVerified As Boolean

Public Event userVerified(pfUserVerified As Boolean)

Public Property Let Workbook(ByVal wbk As Excel.Workbook)
    Set m_XLWbk = wbk
    Call logMsg("Set wbk.")
    With m_XLWbk
        .Worksheets(WKS_CTX).Visible = True
        .Worksheets(WKS_ENABLE_MACROS).Visible = xlVeryHidden
    End With
    Call logMsg("CTX activated.")
    Call Show
    If Not f.fIsAdmin Then Call InformXL
End Property

Private Sub Class_Initialize()
    Set f = New frmSecurity
    Call populateScrollAreaCollection
End Sub

Private Sub Class_Terminate()
    Unload f
    Set colScrollArea = Nothing
    Set f = Nothing
End Sub

Private Sub InformXL()
    RaiseEvent userVerified(f.fIsValidUser)
End Sub

Private Sub Show()
    f.Show vbModal
    If f.fIsAdmin Then Call ShowWorksheets(pfIsAdmin:=True)
End Sub

Public Sub Protect()
    Call HideWorksheets
End Sub

Public Sub Unprotect()
    Call ShowWorksheets
End Sub

Private Sub HideWorksheets()
    Dim wks As Excel.Work***
    For Each wks In m_XLWbk.Worksheets
        If wks.Name = WKS_ENABLE_MACROS Then
            With wks
                .Visible = xlSheetVisible
                .Activate
            End With
        Else
            If f.fIsAdmin Then
                Select Case wks.Name
                    Case WKS_ECHO
                        wks.Columns(HIDE_COLUMNS_ECHO).EntireColumn.Hidden = True
                    Case WKS_CATH
                        wks.Columns(HIDE_COLUMNS_CATH).EntireColumn.Hidden = True
                    Case WKS_NUCLEAR
                        wks.Columns(HIDE_COLUMNS_NUCLEAR).EntireColumn.Hidden = True
                End Select
            End If
            wks.Visible = xlVeryHidden
        End If
    Next wks
    m_XLWbk.Save
End Sub

Private Sub ShowWorksheets(Optional pfIsAdmin As Boolean = False)
    Dim wks As Excel.Work***
    Dim rng As Excel.Range
    Dim t As String
    Dim tt As String
    
    If pfIsAdmin Then
        For Each wks In m_XLWbk.Worksheets
            With wks
                .Unprotect "ctxs"
                .Visible = xlSheetVisible
                .ScrollArea = vbNullString
                .Rows.EntireRow.Hidden = False
                .Columns.EntireColumn.Hidden = False
            End With
        Next wks
        m_XLWbk.Worksheets(WKS_MAIN).Range("Version").Locked = False
    Else
        For Each wks In m_XLWbk.Worksheets
            If wks.Name = WKS_CTX Or wks.Name = WKS_ENABLE_MACROS Then
                wks.Visible = xlVeryHidden
            Else
                With wks
                    t = colScrollArea(.Name)
                    .Unprotect "ctxs"
                    .Visible = xlSheetVisible
                    .ScrollArea = t
                    
                    tt = rowsToHide(t)
                    .Rows(CInt(Mid(tt, InStr(tt, "/") + 1))).RowHeight = 13.5
                    .Rows(Left$(tt, InStr(tt, "/") - 1)).EntireRow.Hidden = True
                    
                    tt = columnsToHide(t)
                    .Columns(Mid(tt, InStr(tt, "/") + 1)).ColumnWidth = 1.43
                    .Columns(Left$(tt, InStr(tt, "/") - 1)).EntireColumn.Hidden = True
                    
                    .Range(t).FormulaHidden = True
                    .Range(t).Locked = False
                    .Protect "ctxs"
                End With
            End If
        Next wks
    End If
    m_XLWbk.Worksheets(WKS_MAIN).Activate
End Sub