Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code works in VB 6 and Word, but not Excel (97/2000/2202)

    I've posted a topic in the VB.VBA newsgroup on the news.devx.com server that
    indicates either I made an error or there is a bug in Excel 97/Excel
    2000/Excel 2002 that does not occur using IDENTICAL (except for 1 line) code
    in VB 6, Word 97/200/2002, and Excel 97/2000/2002. The line that differs
    is:

    ' strCurrentDir = ThisWorkbook.Path & "" ' For Excel
    strCurrentDir = App.Path & "" ' For VB
    ' strCurrentDir = ThisDocument.Path & "" ' For Word

    The code is intended to register a DLL, and that works, the problem occurs
    when there is an extant DLL registered with the same name.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code works in VB 6 and Word, but not Excel (97/2000/2202)

    Exactly what path do you need?

    - the path of the workbook that contains the code (thisworkbook.Path)
    - the path the Application is in (Application.Path)
    - other paths?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code works in VB 6 and Word, but not Excel (97/2000/2202)

    In this example, the path is the same whether using the vbp, xls or .dot file,
    All the files are in the same directory, including the DLL to be registered.

    The one statement changes only because that is required to run the code.
    I could just as well hardcoded the path.

    In order to test the code, all the Excel workbook, the Word template, the DLL file and the VB project for the test need to be in the same directory.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code works in VB 6 and Word, but not Excel (97/2000/2202)

    You say there is an error for Excel. What exactly *is* the error?

    Maybe you could show us more of the code and point out the line(s) that fail to work, including the error message.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code works in VB 6 and Word, but not Excel (97/2000/2202)

    The error is that after unregistering the DLL, Excel does not allow the deletion or replacement of the DLL.

    The code is posted in the newsgroup I identified in my original article in this thread.

    I've since added calls to CoFreeUnusedLibraries and CoFreeLibrary. Doesn't Help.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code works in VB 6 and Word, but not Excel (97/2000/2202)

    Sometimes it helps to end the macro before doing the deletion/replacement. To do that I use a trick like this:

    Sub PartOne()
    'statement
    'statement
    'last statement
    Application.Ontime Now, "PartTwo"
    End Sub

    Sub PartTwo()
    'Deletion/replacement code
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code works in VB 6 and Word, but not Excel (97/2000/2202)

    I had thought about that, but I believe the problem is that Excel is active. For example, although the VB executable works correctly from VB, it does not work correctly when Shelled from Excel as follows:
    <pre>Option Explicit

    Private Sub ShellVBInstall()
    Dim vntReturnValue As Variant
    Dim strCurrentDir As String

    strCurrentDir = ThisWorkbook.path & "" ' For Excel
    ' strCurrentDir = App.path & "" ' For VB
    ' strCurrentDir = ThisDocument.Path & "" ' For Word

    vntReturnValue = Shell(strCurrentDir & "InstallDLL.exe", vbNormalFocus)
    End Sub
    </pre>


    Perhaps, I could shut down Excel right after the VB task is shelled, and add code to the VB task to wait until Excel shuts down,
    but on a multiuser system, not sure that would help.

    Easiest, I guess to have the user separately run an install, either from VB (since they will need to have the VB runtimes
    anyway to use the DLL), or install via Word. The latter seems unnatural for an Excel app. Might even run into a system
    that has Excel, but no Word.

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code works in VB 6 and Word, but not Excel (97/2000/2202)

    I just tried splitting the code.
    This does seem to avoid the problem of the bug in Excel.

    It's been a loooong day, when I'm more alert, I check it out further.

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code works in VB 6 and Word, but not Excel (97/2000/2202)

    RETRACTION

    I spoke too soon.

    Splitting the code does not avoid the problem.

    Perhaps, if I split the code differently?
    But I'm not about to waste any more time trying to avoid this idiotic Excel design flaw.
    I'll just do the deed via straignt VB or via Word VBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •