Results 1 to 36 of 36

Thread: How to make all versions of excel work in Visual Basic 6 pro

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    How to make all versions of excel work in Visual Basic 6 pro

    Windows 10 pro 64 bit
    Visual Basic Pro 32 bit

    When I wrote my program in the year 2005, I had Microsoft office 2003 installed on my PC.

    Currently my program works fine creating excel worksheets on my windows 10 PC.

    PROBLEM:

    If I create a setup.exe of my program and an end user installs my program on his PC but he has a different version of excel on his pc than what I have on my PC, when I wrote the program, my program will not work.

    QUESTION:
    Is it possible to have my program work with any version of excel when I create a setup.exe for distribution?

    Is there a workaround for this issue? Is there a 3rd party addon I can use for this issue?

    Please advise
    Thank You

  2. #2
    PowerPoster
    Join Date
    Feb 2017
    Posts
    3,207

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Welcome to the forums.

    You need to remove the reference to the Office version from the References of the project.
    Then, you need to declare all variables that reference Excel objects "As Object" and not as WorkBook (or whatever).
    Use CreateObject to create the main Excel object.

    For example:

    Code:
    Dim oExcelApp As Object, wb As Object
    
    Set oExcelApp = CreateObject("Excel.Application")
    
    Set wb = oExcelApp.Workbooks.Open("C:\File.xls")
    You'll lose intellisense, that's a price to pay.
    Then recompile, test it and make the installer again.

    That's all I know about it.

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,299

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by Eduardo- View Post
    You need to remove the reference to the Office version from the References of the project.
    False. If you do this you lose all of the multitude of Enum values that Office application object models use and you'll either have to define them all again or resort to inline literals (magic numbers), a very poor practice.

    Quote Originally Posted by Eduardo- View Post
    Then, you need to declare all variables that reference Excel objects "As Object" and not as WorkBook (or whatever).
    True.

    Quote Originally Posted by Eduardo- View Post
    Use CreateObject to create the main Excel object.
    True.

    Quote Originally Posted by Eduardo- View Post
    You'll lose intellisense, that's a price to pay.
    True.

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by Eduardo- View Post
    Welcome to the forums.

    You need to remove the reference to the Office version from the References of the project.
    Then, you need to declare all variables that reference Excel objects "As Object" and not as WorkBook (or whatever).
    Use CreateObject to create the main Excel object.

    For example:

    Code:
    Dim oExcelApp As Object, wb As Object
    
    Set oExcelApp = CreateObject("Excel.Application")
    
    Set wb = oExcelApp.Workbooks.Open("C:\File.xls")
    You'll lose intellisense, that's a price to pay.
    Then recompile, test it and make the installer again.

    That's all I know about it.
    Thank you for your help.
    I looked at my code and I am doing what you suggest except I do not have the reference unchecked. I will try this.

    Are you willing to look at my code with teamviewer?

    Thanks

  5. #5
    PowerPoster
    Join Date
    Feb 2017
    Posts
    3,207

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by dilettante View Post
    False. If you do this you lose all of the multitude of Enum values that Office application object models use and you'll either have to define them all again or resort to inline literals (magic numbers), a very poor practice.
    True, but that can lead to errors in the case that besides the Enums the developer also leave some object referenced with early bound, or not?
    He also needs the exact Excel version always installed in the developer machine.

    But yes, if he uses enums values, then he needs to declare them as contants, and that is what I do.

  6. #6
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,589

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Hi stspringer,

    Welcome to VBForums. Below is the function I've used for well over a decade to hand me a copy of Excel for use in my program.

    Code:
    
    Public Function ExcelApp() As Object
        ' This provides late binding of Microsoft Excel so that
        ' the version doesn't need to be known before binding.
        '
        ' Be SURE to execute a obj.Quit to remove the copy of Excel from memory.
        Dim obj As Object
        Dim Bad As Boolean
        '
        On Error Resume Next
            '
            Err.Clear
            Set obj = CreateObject("Excel.Application.11")
            '
            If Err <> 0 Then
                obj.Quit
                Set obj = Nothing
                Err.Clear
                Set obj = CreateObject("Excel.Application.10")
            End If
            '
            If Err <> 0 Then
                obj.Quit
                Set obj = Nothing
                Err.Clear
                Set obj = CreateObject("Excel.Application")
            End If
            '
            Select Case True
            Case Err <> 0
                Bad = True
            Case Val(obj.Version) < 10
                Bad = True
            End Select
            '
            If Bad Then
                obj.Quit
                Set obj = Nothing
                MsgBox "Error.  Microsoft Excel was not found on this computer.  Microsoft Excel XP (aka 2002 or v10) or later must be installed on this computer for this program to execute this feature.  This program will now be terminated.", vbCritical, "Error."
                End
            End If
            '
            Set ExcelApp = obj
        On Error GoTo 0
    End Function
    
    I haven't really even looked at that code in years, but it's used everyday. Staring at it, I see that I prefer to find an older version, but I'll take any version found on the machine.

    Also, as they say, this is late binding (i.e., the use of "Object" rather than the Excel class names). And this cancels any ability of Intellisense to help you to figure out the members (functions, methods, properties, etc) of the Excel objects. Also, it forces me to keep a growing list of Excel constants at the top of the BAS module that contains that above function. Just a short list looks like:

    Code:
    
    '
    ' Alignment constants.
    Public Enum ExcelAlignHorz
        xlLeaveAloneH = 0&
        xlLeft = -4131&
        xlRight = -4152&
        xlCenterH = -4108&
    End Enum
    #If False Then ' Intellisense fix.
        Public xlLeaveAloneH, xlLeft, xlRight, xlCenterH
    #End If
    Public Enum ExcelAlignVert
        xlLeaveAloneV = 0&
        xlTop = -4160&
        xlBottom = -4107&
        xlCenterV = -4108&
    End Enum
    #If False Then ' Intellisense fix.
        Public xlLeaveAloneV, xlTop, xlBottom, xlCenterV
    #End If
    '
    ' Line constants.
    Public Enum ExcelLineStyle
        xlContinuous = 1&   ' Not dashed.
        xlNone = -4142&     ' No line.
        xlDash = -4115&
        xlDot = -4118&
        xlDouble = -4119&
    End Enum
    #If False Then ' Intellisense fix.
        Public xlContinuous, xlNone, xlDash, xlDot, xlDouble
    #End If
    '
    Public Enum ExcelLineWeight
        xlHairline = 1&     ' Almost fuzzy line.
        xlThin = 2&         ' The DEFAULT Excel line.
        xlMedium = -4138&   ' Slightly thicker.
        xlThick = 4&        ' VERY thick, seldom used.
    End Enum
    #If False Then ' Intellisense fix.
        Public xlHairline, xlThin, xlMedium, xlThick
    #End If
    
    
    To get around the late binding problems, I keep a copy of the VBAXL10.CHM handy (the VBA help system for Excel 10). I'm also frequently popping into the Excel VBA to test things and get the values of additional constants if I need them. When you're in the VBA, you can use the Object Browser to get anything you want. Or, you can use the Debug (Immediate) Window to just print out the values of constants.

    Good Luck With It,
    Elroy

    EDIT1: Just a further FYI, to use the above function, you'd just do something like the following:

    Code:
    
        Dim xls As Object
        Set xls = ExcelApp
    
    
        ' ... do your work.
    
    
        xls.Quit
        Set xls = Nothing
    
    
    Last edited by Elroy; Jul 6th, 2017 at 08:38 AM.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,299

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Just reference the type library.

    What you guys are doing is the same as a C programmer saying "I'm don't need no steenking header file includes" and instead duplicating tons of constant definitions.

    Absurd, prone to completely unnecessary errors, unprofessional, and likely to get you whacked with a rolled newspaper in a code review - if not fired for general incompetence.

  8. #8
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,589

    Re: How to make all versions of excel work in Visual Basic 6 pro

    ** ignores yet another of dilettante's pompous rants, and continues with my code that works absolutely fine ** Why you can't just state the way you'd do things and leave it at that is truly totally beyond me. Say moderators, yet time again for some judicious pruning of posts.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,387

    Re: How to make all versions of excel work in Visual Basic 6 pro

    If I remember right, there's a way to reference the tlb file that gets around all this... it allows for early binding and since it's referenciong the tlb file and not the actual DLLs, it's version agnostic... it used to work against versions of Office up until 2010 (that's the last time I used it before moving on) so I don't know if that still works or not.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    gibra
    Guest

    Re: How to make all versions of excel work in Visual Basic 6 pro

    see

    EARLY binding and LATE binding: using together!
    http://www.vbforums.com/showthread.p...=early+binding

  11. #11
    PowerPoster
    Join Date
    Feb 2017
    Posts
    3,207

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by dilettante View Post
    Just reference the type library.

    What you guys are doing is the same as a C programmer saying "I'm don't need no steenking header file includes" and instead duplicating tons of constant definitions.

    Absurd, prone to completely unnecessary errors, unprofessional, and likely to get you whacked with a rolled newspaper in a code review - if not fired for general incompetence.
    In my opinion it is more professional to have a program that doesn't depend on some particular dependency installed (in this case a particular Excel version) to be able to make a change (to the program) in the future.

    And in most of cases it can be handled with two or three consts declarations.

    But at the end it is a matter of taste, if you want to have all and every Enum already declared at the cost of the dependency or if you prefer not to have the dependency.

  12. #12
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: How to make all versions of excel work in Visual Basic 6 pro

    FYI, a type library doesn't force any dependencies on the end user.

    you can use all the enums, and intellisense without using early binding in the release build.

  13. #13
    PowerPoster
    Join Date
    Feb 2017
    Posts
    3,207

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by stspringer View Post
    Thank you for your help.
    I looked at my code and I am doing what you suggest except I do not have the reference unchecked. I will try this.

    Are you willing to look at my code with teamviewer?

    Thanks
    Sorry, I didn't see this post before.

    I don't have Teamviewer installed, but if you have any doub, you can post that part of the code here and I or any other can help you.

  14. #14
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,589

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Here, just because I was in a TypeLib mode. Attached are the IDL and TLB for all the Excel enums based on Excel 14, but the version of Excel shouldn't matter.

    Who knows, maybe I'll start using this myself.

    Enjoy,
    Elroy

    EDIT1: And also, yet another problem with TypeLibs. If you work in an open-source mode, with others looking at your source code, these TypeLibs are yet another hurdle that others have to work through before they can execute things. I'll agree that it's not a huge hurdle, but it is a hurdle. My primary project is such that it'll load and execute on any good VB6 IDE installation. This will no longer be true if I start using TypeLibs.
    Attached Files Attached Files
    Last edited by Elroy; Jul 6th, 2017 at 12:47 PM.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  15. #15
    Frenzied Member
    Join Date
    Dec 2008
    Location
    Melbourne Australia
    Posts
    1,455

    Re: How to make all versions of excel work in Visual Basic 6 pro

    These are the 'go to' links in vbForums, for Excel automation
    http://www.vbforums.com/showthread.p...6-(or-VB5-VBA)
    http://www.vbforums.com/showthread.p...App-using-VB-6
    http://www.vbforums.com/showthread.p...using-together

    Not intending to detract from other suggestions been made in this thread (which I will check out, later)
    Rob

  16. #16

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Thank you Elroy,
    what do u mean by "hand me a copy of Excel" or was that a typo?


    I will give this a try. I am not a professional programmer I write code for my self. Hope I can grasp this. Will try

  17. #17

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Thanks to All for your input.
    Last edited by stspringer; Jul 8th, 2017 at 12:41 PM.

  18. #18

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    Re: How to make all versions of excel work in Visual Basic 6 pro

    I can write to excel worksheets just fine in my progam, my issue is different versions of excel on end users "customers" pc. I don't know what version of excel they may have I want my program to handle any version and not choke.

  19. #19

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    Re: How to make all versions of excel work in Visual Basic 6 pro

    ok will do. I am using late binding I think my I get error code 1004 when I try to save the worksheet with office 2010 installed on a pc. I think it is because of .xls and .xlsx extensions maybe I have to check for what extension is going to be used. What do u think?

  20. #20
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,589

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by stspringer View Post
    Thank you Elroy,
    what do u mean by "hand me a copy of Excel"
    I was just trying to say that the function would return an instantiated object that was the Excel application, to do with what I pleased.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  21. #21
    Addicted Member
    Join Date
    Jan 2015
    Posts
    246

    Re: How to make all versions of excel work in Visual Basic 6 pro

    I have all enums of office 2010
    It is cut down from 2010 local help file, but its remarks are all Chinese, i do not this u will like it.
    so i can suggest u, to cut down all enums from here.

    Excel 2010 Developer Reference -> Excel Object Model Reference -> Enumerations

    or you can cut down from here.
    https://msdn.microsoft.com/zh-cn/vba...erations-excel

  22. #22
    Addicted Member
    Join Date
    Jan 2015
    Posts
    246

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by Elroy View Post
    Here, just because I was in a TypeLib mode. Attached are the IDL and TLB for all the Excel enums based on Excel 14, but the version of Excel shouldn't matter.

    Who knows, maybe I'll start using this myself.

    Enjoy,
    Elroy

    EDIT1: And also, yet another problem with TypeLibs. If you work in an open-source mode, with others looking at your source code, these TypeLibs are yet another hurdle that others have to work through before they can execute things. I'll agree that it's not a huge hurdle, but it is a hurdle. My primary project is such that it'll load and execute on any good VB6 IDE installation. This will no longer be true if I start using TypeLibs.
    this typelib is good and i have download, thank you for sharing.
    but i have a different one that someone given to me before.
    i just confused that the enums in it is far more than you given here or in the local help file or on the msdn url
    i do not know why
    it seems in my version
    access 2010 260 enums
    excel 2010 406 enums
    outlook 2010 281 enums
    ppt 2010 268 enums
    publisher 2010 244 enums
    word 2010 501 enums

  23. #23
    gibra
    Guest

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by stspringer View Post
    ok will do. I am using late binding I think my I get error code 1004 when I try to save the worksheet with office 2010 installed on a pc. I think it is because of .xls and .xlsx extensions maybe I have to check for what extension is going to be used. What do u think?
    You must to use LATE BINDING approach, to avoid any compatibility issue, as explained in post #11:
    http://www.vbforums.com/showthread.p...=1#post5187267
    Doesn't matter what version user had installed, late binding work for any version.
    See the source project for use late binding and early binding together

  24. #24
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,795

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Hi @ll,

    this may be stupid, but is it possible to just Save the Workbook to the newer Excel- Versions?

    Excel 2000 to ???

    Code:
    ActiveWorkbook.SaveAs _
    Filename:="C:\Meine.Xls", _  '<----here the new Version
    FileFormat:=xlNormal
    regards
    Chris

  25. #25
    PowerPoster
    Join Date
    Feb 2017
    Posts
    3,207

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by ChrisE View Post
    Hi @ll,

    this may be stupid, but is it possible to just Save the Workbook to the newer Excel- Versions?

    Excel 2000 to ???

    Code:
    ActiveWorkbook.SaveAs _
    Filename:="C:\Meine.Xls", _  '<----here the new Version
    FileFormat:=xlNormal
    regards
    Chris
    Hello,

    Intead of xlNormal try with xlOpenXMLWorkbook (value 51).
    More info:
    XlFileFormat Enumeration.
    SaveAs method.
    Last edited by Eduardo-; Jul 9th, 2017 at 10:50 AM.

  26. #26
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,589

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Okay, here are the two procedures I use for saving Excel files. Again, I use late binding for everything when I'm automating Excel from VB6.

    Code:
    
    
    Public Sub ExcelSave(wbk As Object, Optional bForceOldFormat As Boolean = True)
        If bForceOldFormat Then
            If Int(wbk.Application.Version) > 11 Then ' It's Office 2007 or greater.
                wbk.CheckCompatibility = False
                wbk.Save
            Else
                wbk.Save
            End If
        Else
            wbk.Save
        End If
    End Sub
    
    Public Sub ExcelSaveAs(wbk As Object, sFileSpec As String, Optional bForceOldFormat As Boolean = True)
        If bForceOldFormat Then
            If Int(wbk.Application.Version) > 11 Then ' It's Office 2007 or greater.
                wbk.CheckCompatibility = False
                wbk.SaveAs sFileSpec, xlExcel8
            Else
                wbk.SaveAs sFileSpec
            End If
        Else
            If UCase$(Right$(sFileSpec, 4)) = "XLSM" Then
                wbk.SaveAs sFileSpec, xlOpenXmlWorkbookMacroEnabled
            Else
                wbk.SaveAs sFileSpec
            End If
        End If
    End Sub
    
    
    Enjoy,
    Elroy

    EDIT1: Also, the above assumes you know what you're doing with respect to file names (i.e., the XLS or XLSX extensions).

    EDIT2: And the two constants used are:

    Code:
    
    Public Const xlExcel8 = 56&
    Public Const xlOpenXmlWorkbookMacroEnabled = 52&
    
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  27. #27

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by Elroy View Post
    Okay, here are the two procedures I use for saving Excel files. Again, I use late binding for everything when I'm automating Excel from VB6.

    Code:
    
    
    Public Sub ExcelSave(wbk As Object, Optional bForceOldFormat As Boolean = True)
        If bForceOldFormat Then
            If Int(wbk.Application.Version) > 11 Then ' It's Office 2007 or greater.
                wbk.CheckCompatibility = False
                wbk.Save
            Else
                wbk.Save
            End If
        Else
            wbk.Save
        End If
    End Sub
    
    Public Sub ExcelSaveAs(wbk As Object, sFileSpec As String, Optional bForceOldFormat As Boolean = True)
        If bForceOldFormat Then
            If Int(wbk.Application.Version) > 11 Then ' It's Office 2007 or greater.
                wbk.CheckCompatibility = False
                wbk.SaveAs sFileSpec, xlExcel8
            Else
                wbk.SaveAs sFileSpec
            End If
        Else
            If UCase$(Right$(sFileSpec, 4)) = "XLSM" Then
                wbk.SaveAs sFileSpec, xlOpenXmlWorkbookMacroEnabled
            Else
                wbk.SaveAs sFileSpec
            End If
        End If
    End Sub
    
    
    Enjoy,
    Elroy

    EDIT1: Also, the above assumes you know what you're doing with respect to file names (i.e., the XLS or XLSX extensions).

    EDIT2: And the two constants used are:

    Code:
    
    Public Const xlExcel8 = 56&
    Public Const xlOpenXmlWorkbookMacroEnabled = 52&
    
    Elroy I thank you very much. I am using late binding in my program. So you are saying all will work with this code you provided here? Do i need enum as others have mentioned? Please forgive me because I wrote this program in 2005 and I have to get back into programming mind set yet. I don't program all the time, haven't programmed in awhile but I do want to fix this glitch. Thanks again

  28. #28
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,589

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Hi stspringer,

    I don't use any TypeLib to do my Excel automation. (I just mentioned them above in response to what I thought were critiques to the way I do things.) Rather, I just declare Excel constants as I need them. I have a module named mod_Gen_Excel.bas where I keep all this stuff. It's a bit of a hodge-podge of stuff, as I just add to it when I need to. Also, I tend to do much of my Excel automation "in-line" in my code, so everything I do isn't necessarily found in that module. However, I'll attach it here.

    The ExcelApp procedure will return the Excel application all ready to be set into some object variable. The ExcelSaveAs is what I posted above.

    Also, just as an FYI, my main application uses a complete mixture of all the following Excel file types: XLS, XLT, XLSX, XLTX, XLSM, & XLTM. The XLS & XLT types are the pre-Office-2007 types.

    Good Luck,
    Elroy
    Attached Files Attached Files
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  29. #29

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Thanks Elroy,
    I will try this code. I hope I can get it to work in my program.

  30. #30

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    Re: How to make all versions of excel work in Visual Basic 6 pro

    I get error sub or function not defined hWndFromMidTitle when I do a full compile in the enviorment on this function

    Public Function ExcelIsOpen() As Boolean
    If hWndFromMidTitle("Microsoft Excel") Then
    ExcelIsOpen = True
    Exit Function
    End If
    If hWndFromMidTitle("- Excel") Then
    ExcelIsOpen = True
    Exit Function
    End If
    End Function

  31. #31

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    10

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by stspringer View Post
    Thanks Elroy,
    I will try this code. I hope I can get it to work in my program.
    I get error sub or function not defined hWndFromMidTitle when I do a full compile in the enviorment on this function
    hWndFromMidTitle
    Public Function ExcelIsOpen() As Boolean
    If hWndFromMidTitle("Microsoft Excel") Then
    ExcelIsOpen = True
    Exit Function
    End If
    If hWndFromMidTitle("- Excel") Then
    ExcelIsOpen = True
    Exit Function
    End If
    End Function

  32. #32
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,589

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Ahh, stspringer,

    Sorry about that. My code just gets so inter-woven at times. The following can be thrown into a BAS module and it's enough to allow everything to execute in that module I attached above:

    Code:
    
    Option Explicit
    '
    ' GetWindow
    Private Const GW_HWNDFIRST = 0
    Private Const GW_HWNDLAST = 1
    Private Const GW_HWNDNEXT = 2
    Private Const GW_HWNDPREV = 3
    Private Const GW_OWNER = 4
    Private Const GW_CHILD = 5
    Private Const GW_MAX = 5
    '
    Private Const PROCESS_ALL_ACCESS = &H1F0FFF
    '
    Public Const MAX_PATH_W = 1024  ' This can actually go as high as 32767, but it's set at this to preserve a bit of memory.
    
    Private Declare Function GetDesktopWindow Lib "user32" () As Long
    Private Declare Function GetWindow Lib "user32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
    Private Declare Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hWnd As Long) As Long
    Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Private Declare Function EnumProcessModules Lib "psapi" (ByVal hProcess As Long, lphModule As Long, ByVal cb As Long, lpcbNeeded As Long) As Long
    Private Declare Function GetModuleFileNameExW Lib "psapi" (ByVal hProcess As Long, ByVal hModule As Long, ByVal lpFileName As Long, ByVal nSize As Long) As Long
    Private Declare Function GetModuleBaseNameW Lib "psapi" (ByVal hProcess As Long, ByVal hModule As Long, ByVal lpFileName As Long, ByVal nSize As Long) As Long
    Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As Long, lpdwProcessId As Long) As Long
    '
    
    Public Function hWndFromMidTitle(sTitle As String, Optional sExeFileNameToMatch As String = "", Optional sClassToMatch As String = "") As Long
        ' Returns zero if not found.  Typically used with forms, but may work with controls if they have a title.
        Dim hWnd As Long
        hWnd = 0
        Do
            hWnd = hWndOfNextTopLevelWindow(hWnd)
            If hWnd = 0 Then
                hWndFromMidTitle = 0
                Exit Do
            Else
                If InStr(WindowText(hWnd), sTitle) <> 0 Then
                    Select Case True
                    Case Len(sExeFileNameToMatch) <> 0 And Len(sClassToMatch) <> 0
                        If LCase$(ExeFileName(hWnd)) = LCase$(sExeFileNameToMatch) And LCase$(WindowClass(hWnd)) = LCase$(sClassToMatch) Then
                            hWndFromMidTitle = hWnd
                            Exit Do
                        End If
                    Case Len(sExeFileNameToMatch) <> 0
                        If LCase$(ExeFileName(hWnd)) = LCase$(sExeFileNameToMatch) Then
                            hWndFromMidTitle = hWnd
                            Exit Do
                        End If
                    Case Len(sClassToMatch) <> 0
                        If LCase$(WindowClass(hWnd)) = LCase$(sClassToMatch) Then
                            hWndFromMidTitle = hWnd
                            Exit Do
                        End If
                    Case Else
                        hWndFromMidTitle = hWnd
                        Exit Do
                    End Select
                End If
            End If
        Loop
    End Function
    
    
    Public Sub lSwap(L1 As Long, L2 As Long)
        ' Swaps two long.
        ' Static for speed because it might be used in sorts.
        Static lTemp As Long
        lTemp = L1
        L1 = L2
        L2 = lTemp
    End Sub
    
    
    Public Function hWndOfNextTopLevelWindow(hWnd As Long) As Long
        ' if hWnd=0 then the first window is returned.
        Dim l As Long
        If hWnd = 0 Then
            ' The desktop is the highest window
            l = GetDesktopWindow()
            ' It's first child is the 1st top level window
            hWndOfNextTopLevelWindow = GetWindow(l, GW_CHILD)
        Else
            hWndOfNextTopLevelWindow = GetWindow(hWnd, GW_HWNDNEXT)
        End If
    End Function
    
    Public Function WindowText(hWndOfInterest As Long) As String
        ' Form or control.
        Dim s As String
        Dim l As Long
        '
        l = GetWindowTextLength(hWndOfInterest)
        s = Space$(l + 1)
        l = GetWindowText(hWndOfInterest, s, l + 1)
        s = RTrimNull(s)
        WindowText = Trim$(s)
    End Function
    
    Public Function ExeFileName(hWndOfInterest As Long, Optional FullSpec As Boolean = False) As String
        Dim rtn As Long
        Dim lProcMods() As Long
        Dim sFileName As String
        Dim lSize As Long
        Dim lRequired As Long
        Dim hProcess As Long
        Dim hWndOfFormWithFocus As Long
        Dim l As Long
        '
        lSize = 4
        ReDim lProcMods(0)
        '
        hProcess = OpenProcess(PROCESS_ALL_ACCESS, 0&, ProcessId(hWndOfInterest))
        ' Enumerate modules.
        rtn = EnumProcessModules(hProcess, lProcMods(0), lSize, lRequired)
        ' If array is not large enough to hold all results, number of bytes required is in lRequired.
        If lRequired > lSize Then
            lSize = lRequired
            ReDim lProcMods(0 To (lSize / 4) - 1)
            rtn = EnumProcessModules(hProcess, lProcMods(0), lSize, lRequired)
        End If
        ' lProcMods() now holds the list of module handles associated with the process.
        ' The zeroth element is the main program.
        sFileName = String$(MAX_PATH_W, 0)
        If FullSpec Then
            rtn = GetModuleFileNameExW(hProcess, lProcMods(0), StrPtr(sFileName), Len(sFileName))
        Else
            rtn = GetModuleBaseNameW(hProcess, lProcMods(0), StrPtr(sFileName), Len(sFileName))
        End If
        ExeFileName = Left$(sFileName, rtn)
        rtn = CloseHandle(hProcess)
    End Function
    
    Public Function WindowClass(hWndOfInterest As Long) As String
        Dim i As Long
        Dim sClassName As String * 256
        i = GetClassName(hWndOfInterest, sClassName, 256)
        WindowClass = Left$(sClassName, i)
    End Function
    
    Public Function RTrimNull(s As String) As String
        Dim i As Integer
        i = InStr(s, vbNullChar)
        If i Then
            RTrimNull = Left$(s, i - 1)
        Else
            RTrimNull = s
        End If
    End Function
    
    
    Public Function ProcessId(hWndOfInterest As Long) As Long
        ' This process ID is unique to the entire application to which the window belongs.
        ' A process ID will always be unique for each running copy of an application, even if more than one copy is running.
        Dim lProcId As Long
        Call GetWindowThreadProcessId(hWndOfInterest, lProcId)
        ProcessId = lProcId
    End Function
    
    
    
    Enjoy,
    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  33. #33
    gibra
    Guest

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Elroy: subclassing... a lot of API's...
    To work with Excel files in VB6 do not need to use that code.
    I have the impression that you are complicating things that in reality are much simpler.

    I have never used, and will never use, all that code.
    IMHO

  34. #34
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,589

    Re: How to make all versions of excel work in Visual Basic 6 pro

    gibra,

    No problem. There's no subclassing in it, but it's all good.

    Alternatively, you could just cut out the functions in my Excel module that make calls over to the API stuff.

    But hey, I was just trying to reply to a request by stspringer. To each their own.

    Best,
    Elroy

    EDIT1: gibra, also, just as an FYI. stspringer asked for the module I use to do my Excel automation. I gave it to him, but I forgot that it had some calls to some other procedures outside of that module. All I've done is to gather up those calls and post them in post#32. It's up to stspringer as to whether or not he uses my Excel VB6 module. And, to say again, another option is to cut out the procedures from the Excel VB6 module that make those calls. It's his choice. I'm done with this thread though. I'll let you help him out from here.
    Last edited by Elroy; Jul 11th, 2017 at 05:58 PM.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  35. #35
    gibra
    Guest

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Ok Elroy, I understand.
    But I believe that all this is moving away from the initial OP problem, which is solved very easily, using the snippet code indicated by Eduardo in post #2.
    More than enough.

  36. #36
    Addicted Member
    Join Date
    Mar 2009
    Posts
    212

    Re: How to make all versions of excel work in Visual Basic 6 pro

    Quote Originally Posted by Elroy View Post
    Here, just because I was in a TypeLib mode. Attached are the IDL and TLB for all the Excel enums based on Excel 14, but the version of Excel shouldn't matter.

    Who knows, maybe I'll start using this myself.

    Enjoy,
    Elroy

    EDIT1: And also, yet another problem with TypeLibs. If you work in an open-source mode, with others looking at your source code, these TypeLibs are yet another hurdle that others have to work through before they can execute things. I'll agree that it's not a huge hurdle, but it is a hurdle. My primary project is such that it'll load and execute on any good VB6 IDE installation. This will no longer be true if I start using TypeLibs.
    The version of Excel shouldn't matter, UNTILL you use enum values/functions which are only available with Excel 14 or up..
    So you should always keep in mind if you want people with older versions to be able to use your application that you don't use enumvalues/functions which are only available with newer versions (so if you know you're using newer function, make sure you have some sort of check on the version and warn people with lower versions).

Posting Permissions

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



Click Here to Expand Forum to Full Width