none
Excel VBA compatibility with 2003, 2007 and 2010

    Question

  • Hello,

    I have to get some data from a SQL Server and import them in some specific worksheet / cells.

    A possibility would be to use VBA and program this inside Excel. However, after searching a litle bit I am not sure that it would be so easy and therefore I have the following questions:

    - My development PC is Windows 7 64 bits, but Office 2010 seems to be 32 bits. I have read somewhere that it could be a problem with ADO or OLEDB. Is it true?

    - The resulting Excel + VBA code is intended to be used by more than one user which could have different Excel versions. What should I pay attention to?

    Thanks a lot for your answers

    (if there is a better place to ask this, please tell me where)

    • Moved by William Zhou CHN Tuesday, June 14, 2011 8:48 AM Excel for Developers (From:Excel IT Pro Discussions)
    Monday, June 13, 2011 1:25 PM

Answers

  • A couple of things to watch for :
    1. References - these will be different between a 64 bit and 32 bit machine. You may have to work with the References collection to insure the proper ADO libraries are being referenced.
    2. Employ compiler directives to make your code insensitive to the nuances of each version:
      #Const XLVERSION = 2007

      #If XLVERSION = 2003 Then
          Call This
      #Else
          Call That
      #End If

      Unfortunately you'll have to declare the #Const in each Module in your VBA Project.
    Wednesday, June 15, 2011 12:27 PM

All replies

  • A couple of things to watch for :
    1. References - these will be different between a 64 bit and 32 bit machine. You may have to work with the References collection to insure the proper ADO libraries are being referenced.
    2. Employ compiler directives to make your code insensitive to the nuances of each version:
      #Const XLVERSION = 2007

      #If XLVERSION = 2003 Then
          Call This
      #Else
          Call That
      #End If

      Unfortunately you'll have to declare the #Const in each Module in your VBA Project.
    Wednesday, June 15, 2011 12:27 PM
  • Thanks a lot Syswizard for your advices. Just before closing definitively this thread: is there somewhere more information about how to use References collection related to ADO libraries?
    Monday, June 20, 2011 12:32 PM
  • This is the reference that applies to all versions of MS Office:

    C:\Program Files\Common Files\System\ADO\msado25.tlb

    If it's not in the list, just Browse to this file. If this file is missing, something is wrong with your office installation.

     

    Monday, June 20, 2011 12:40 PM