Results 1 to 8 of 8

Thread: [RESOLVED] Open/Save Excel workbook from VB6

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Resolved [RESOLVED] Open/Save Excel workbook from VB6

    I want to open a template .xls Excel file, write data to it and save it under another name. What dll file(s) do I need to include in my Setup to have the necessary files available for this.

    I included a reference to Microsoft Office 10.0 Object Library in my project but the location of the file is trimmed in the references screen. All I can see is Location: C:\Program Files\Common Files\Microsoft Shared\Office10\MS???"

    I looked in this directory and there is only one .dll file called MSO.DLL. I included this in my setup but it fails to register. Does it need to be registered or are there other files I need to include?

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: Open/Save Excel workbook from VB6

    Hi Bezzie,

    I literally have software that uses Excel to do exactly what you're describing, and it's running on MANY computers with various versions of Windows and various versions of MS-Office. I use late-binding (i.e., no reference to MS-Office), but I know that it works just fine with early-binding (the way you're doing it).

    I regret to say that I really don't understand your question. At first glance, it seems that you have a borked installation of MS-Office. If it were me, I'd try the MS-Office repair function as a first attempt to fix it. Regarding what DLL files MS-Office uses, who can really say. However, that's truly not something you should need to worry about if/when you've got a good installation of MS-Office on your computer.

    Maybe that'll help.

    Good Luck,
    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. To all, peace and happiness.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: Open/Save Excel workbook from VB6

    Found a good explanation on one of the VB Forums written by si_the_geek that explained the late binding. Changed my code to use that and removed the reference to MS Office and got it working. But before I could mark the thread resolved I had to switch the PC off due to a heavy thunderstorm in the area.

  4. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: [RESOLVED] Open/Save Excel workbook from VB6

    Ahhh, hi Bezzie,

    Yes, late-binding gives you the advantage of not needing to know which version of MS-Office you'll be dealing with. However, there are two downsides: 1) you lose of of VB6's really nice Intellisense help, and 2) you have to declare all the MS-Office constants that you'll be using.

    Regarding the Intellisense, I tend to get around that by doing all my alpha-testing within the VBA rather than within the VB6 IDE. And regarding the constants, I just find myself often hopping over to the VBA and printing out the value of the constants I need in the VBA debug/immediate (Ctrl-G) window, and then creating those constants in my program. Here's just a small snippet of a module I have named Gen_Excel.bas:

    Code:
    
    Public Const xlPicture = -4147&
    Public Const xlPrinter = 2&
    '
    Public Const xlCalculationManual = -4135&
    Public Const xlCalculationAutomatic = -4105&
    '
    Public Const xlCategory = 1&
    Public Const xlValue = 2&
    '
    Public Const xlSolid = 1&
    '
    Public Const xlPasteValues = -4163
    '
    ' Alignment constants.
    Public Enum ExcelAlignHorz
        xlLeaveAloneH = 0&
        xlHAlignGeneral = 1&
        xlHAlignLeft = -4131&
        xlRight = -4152&
        xlCenterH = -4108&
    End Enum
    #If False Then ' Intellisense fix.
        Public xlLeaveAloneH, xlHAlignLeft, xlRight, xlCenterH
    #End If
    Public Enum ExcelAlignVert
        xlLeaveAloneV = 0&
        xlTop = -4160&
        xlBottom = -4107&
        xlVAlignCenter = -4108&
    End Enum
    #If False Then ' Intellisense fix.
        Public xlLeaveAloneV, xlTop, xlBottom, xlVAlignCenter
    #End If
    
    Good Luck,
    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. To all, peace and happiness.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: [RESOLVED] Open/Save Excel workbook from VB6

    Yeah. Had to look up those already

    Just a question or two if you don't mind.

    I have Office 2010 on my PC. I know some of the users has versions before that, mainly 2007 but there might even be older versions while others will have newer versions. How would it work if the template was created in 2010 and the program tries to open it in an earlier version? I believe the newer versions will open older files without a problem but will it work the other way around?

    In the meantime I saved my template as an 97-2003 workbook but now I have an annoying "Do you want to save the file." message that keeps popping up when closing the Excel instance. This message pops up right after I used oXLSBook.SaveAs to save the template file with a new name and the program tries to close Excel. Even opening the saved file later (not via the program but with Excel itself) and closing it right away without doing anything on the file still gives the "Do you want to save the file." popup message. I see it says at the top of the screen that Excel opened it in "Compatibility Mode" when I open the file at a later stage.

    The most annoying part of it all is that the "Do you want to save the file." message sometimes ends up behind a window that can't be moved and then I'm stuck there. Opening Excel then brings the message to the foreground and I can close the previous instance that the program was unable to close because of the annoying message.

    I don't want to view Excel when writing the report. Just open the template, write the report and close Excel. No user intervention required. Just a message at the end saying the file was created

    Any suggestions appreciated.

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

    Re: [RESOLVED] Open/Save Excel workbook from VB6

    Hi Bezzie,

    Maybe these two procedures will help you. This is the way I always save an Excel file I'm working on:

    Code:
    
    Option Explicit
    
    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)
        Const xlOpenXmlWorkbookMacroEnabled = 52&
        Const xlExcel8 = 56&
        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
    
    The wbk is an open Workbook you're working on. You can see where I futz around with different versions.

    Also, regarding the old files, the new XLSX format was introduced with Office 2007. Those files can't be opened with anything earlier than Office 2007. However, you've still got a SaveAs Excel 97-2003 option. You see me using it in the above.

    I'm not entirely certain about this, but, "in most cases", anything back to Office 2007 will open these XLSX files, and anything back to Office 1993 will open the XLS files. I'm nervous about that "in most cases" statement. If you use "newer" features, older versions of Office will probably complain, but still open the file.

    Best of Luck,
    Elroy

    EDIT1: Just as another comment, I try to stay in the older XLS format, but I am slowly moving to the newer XLSX/XLSM format for some things (forcing my clients to have Office 2007 or greater). But, when saving an old format (XLS) even with the latest Office, I've never seen anyone have a problem if/when they email that to someone else. Many of my clients have the latest version of Office, but I've still got 2010. I never have any problems opening their files (XLS or XLSX/XLSM).
    Last edited by Elroy; Dec 13th, 2017 at 02:01 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. To all, peace and happiness.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: [RESOLVED] Open/Save Excel workbook from VB6

    That was my thinking when I used the .xls format. Difficult to test if one doesn't have regular access to PC's with older versions on it

    Thanks for the info and the code snippet. Going to incorporate it later today. First have my day job to attend to now

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2007
    Location
    Roodepoort, South Africa
    Posts
    472

    Re: [RESOLVED] Open/Save Excel workbook from VB6

    Still got the same "Want to save" message box. Further googling got that sorted out with a one statement macro in the ThisWorkbook objects BeforeClose event.
    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Me.Saved = True
    End Sub
    This in turn triggered a "Privacy Warning" message. More googling got that sorted out as well by disabling "Remove personal information from file properties on save."

    So hopefully this will be working now...

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