none
Making my Macro compatible with all Office versions RRS feed

  • Question

  • Hi all,

    I have a macro within an Excel workbook that generates Word files. I've added a reference to Microsoft Word 14.0 Object Library and create the object like so:

    Dim appWord As Word.Application
    Set appWord = CreateObject("Word.Application.14")
    appWord.Documents.Add
    


    This works just fine. However, I need to create a version that will run on all versions of Office from 2003, not just 2010.

    Can I check which version of Office is installed and somehow add the approprite reference from code? 

    Stumped.

     

    Many thanks,


    James Finch MCDST
    • Edited by Woohoooo Wednesday, September 21, 2011 11:12 AM
    Wednesday, September 21, 2011 11:09 AM

Answers

  • Two things...
    1.  What has worked best in the past and still should,
         is to develop your code using the oldest version that the code is expected to run on.
         In your case, that would mean installing Word/Excel 2003 versions (or office 2003) on your machine.
         ( or using your neighbors machine)
         Get the code running in Word/Excel 2003 then test it in the later version(s).

    2.  Use...
         Set appWord = CreateObject("Word.Application")
         (no version specified - references should automatically adjust)
    '---
    Jim Cone
    Portland, Oregon USA
    http://blog.contextures.com/archives/2011/07/18/find-last-row-with-excel-vba/
    (workbook with "universal" Last Row function code - free)

    • Edited by Jim Cone Wednesday, September 21, 2011 3:34 PM
    • Marked as answer by Woohoooo Thursday, September 22, 2011 11:18 AM
    Wednesday, September 21, 2011 3:33 PM

All replies

  • Two things...
    1.  What has worked best in the past and still should,
         is to develop your code using the oldest version that the code is expected to run on.
         In your case, that would mean installing Word/Excel 2003 versions (or office 2003) on your machine.
         ( or using your neighbors machine)
         Get the code running in Word/Excel 2003 then test it in the later version(s).

    2.  Use...
         Set appWord = CreateObject("Word.Application")
         (no version specified - references should automatically adjust)
    '---
    Jim Cone
    Portland, Oregon USA
    http://blog.contextures.com/archives/2011/07/18/find-last-row-with-excel-vba/
    (workbook with "universal" Last Row function code - free)

    • Edited by Jim Cone Wednesday, September 21, 2011 3:34 PM
    • Marked as answer by Woohoooo Thursday, September 22, 2011 11:18 AM
    Wednesday, September 21, 2011 3:33 PM
  • Thanks Jim, I'll try this when I get home. I have Office 2003 on an old laptop.
    James Finch MCDST
    Wednesday, September 21, 2011 3:37 PM
  • Hi all,

    I have a macro within an Excel workbook that generates Word files. I've added a reference to Microsoft Word 14.0 Object Library and create the object like so:

    Dim appWord As Word.Application
    Set appWord = CreateObject("Word.Application.14")
    appWord.Documents.Add
    


    This works just fine. However, I need to create a version that will run on all versions of Office from 2003, not just 2010.

    Can I check which version of Office is installed and somehow add the approprite reference from code? 

    Stumped.

     

    Many thanks,


    James Finch MCDST


    While establishing a reference to a library helps with development, deploying without the reference (using late binding) also has its benefits.  For a way to have the best of both worlds, I use a compile time constant.  For an example see

    http://www.tmehta.com/regexp/add_code.htm

    You may also want to see

    Office 2010 VBA
    http://www.tushar-mehta.com/publish_train/xl_vba_cases/1016%20Office%202010%20VBA.shtml

     


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Wednesday, September 21, 2011 5:15 PM
  • I know this works.  What I do is run the free CodeCleaner addin on the oldest version I support.  Mine is the reverse.  I create an Excel spreadsheet showing scheduled times from a quote document.  The only problem is remembering to do it.  You need to install Office versions in order if you have multiple versions of Office on the same PC (2003 >> 2007 >> 2010).  The other thing to remember is if you have multiple versions installed on a PC is will use the last version installed unless you have an earlier running when you run the app.  For example, in my case if Excel is not running and I create an Excel spreadsheet it will use Excel 2010.  I can save it as .xlsx.  If however, Excel 2003 is running when I try to create the Excel spreadsheet it will use Excel 2003.  Trying to save to .xlsx will cause an error. 
    Wednesday, September 21, 2011 5:33 PM
  • I know this works.  What I do is run the free CodeCleaner addin on the oldest version I support.  Mine is the reverse.  I create an Excel spreadsheet showing scheduled times from a quote document.  The only problem is remembering to do it.  You need to install Office versions in order if you have multiple versions of Office on the same PC (2003 >> 2007 >> 2010).  The other thing to remember is if you have multiple versions installed on a PC is will use the last version installed unless you have an earlier running when you run the app.  For example, in my case if Excel is not running and I create an Excel spreadsheet it will use Excel 2010.  I can save it as .xlsx.  If however, Excel 2003 is running when I try to create the Excel spreadsheet it will use Excel 2003.  Trying to save to .xlsx will cause an error. 
    Wednesday, September 21, 2011 5:34 PM
  • #Const Develop = True
    
    Sub Test()
    #If Develop = True Then
    Dim wrdApp As New Word.Application
    Const wdParagraph=4
    #Else Dim wrdApp As Object #End If Set wrdApp = CreateObject("Word.Application") End Sub


    Set a reference to Word, set teh Develop constant to true and develop as normal. This gives full intellisense development etc. Once you code works, delete the reference, set Develop to False.

    You will get a series of compile errors such as for all word constants such as wdParagraph etc. Create new constants as shown above (use ?wdparagraph then press Enter in the immediate window to find the value.

    Using this method you benefit from quicker development and teh Object format which will work with all Word versions.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Wednesday, September 21, 2011 10:14 PM
  • Two things...
    1.  What has worked best in the past and still should,
         is to develop your code using the oldest version that the code is expected to run on.
         In your case, that would mean installing Word/Excel 2003 versions (or office 2003) on your machine.
         ( or using your neighbors machine)
         Get the code running in Word/Excel 2003 then test it in the later version(s).

    2.  Use...
         Set appWord = CreateObject("Word.Application")
         (no version specified - references should automatically adjust)
    '---
    Jim Cone
    Portland, Oregon USA
    http://blog.contextures.com/archives/2011/07/18/find-last-row-with-excel-vba/
    (workbook with "universal" Last Row function code - free)


    I loaded up Office 2003 on my old laptop, pasted the macro, changed Set appWord = CreateObject("Word.Application.14") to Set appWord = CreateObject("Word.Application"). Between work and home I have tested it on Office 2003, 2007 and 2010 - it works swimmingly. So simple. Many thanks Jim.
    James Finch MCDST
    Thursday, September 22, 2011 11:18 AM