Converting code written in excel 97 to 07/10 RRS feed

  • Question

  • Im trying to update a model written in 97 to 07/10. A copy and paste job leaves huge errors and stepping through the code it all comes undone at a <print.special>. Are the objects any different?
    Wednesday, May 2, 2012 3:29 AM

All replies

  • Virtually all VBA code written in and for Excel 97 will work in 2007/10, particularly if it worked in 32bit 97 (vs 16bit) and ported to a new 32bit Office (API code needs rewriting in 64bit). That said there are some quirks which may indeed need fixing, a few things have been deprecated, and some code would be better adapted for later versions.

    I'd need to resurrect another machine with Excel 97 to double check, but I'm pretty sure "print.special" would not compile in Excel 97, which suggests something odd about your copy/paste.

    Instead of copy/paste, retest the code in Excel 97, then open a copy of the same workbook in 2007/10 and try that.

    Peter Thornton

    Wednesday, May 2, 2012 12:25 PM
  • You cant.

    You must rewrite code ones again in most cases or you can ask about version in code. Like this for exp:

        'Get the userform's window handle
        If Val(Application.VERSION) < 9 Then
            mhWndForm = FindWindow("ThunderXFrame", oForm.Caption)    'XL97
            mhWndForm = FindWindow("ThunderDFrame", oForm.Caption)    'XL2000+
        End If
    Set oCn = CreateObject("ADODB.Connection")
        Dim strConnectionString As String
        If Val(Application.VERSION) < 12 Then
            strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                  "Data Source=" & path & file & ";" & _
                                  "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;"""
            strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source=" & path & file & ";" & _
                                  "Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;"""
        End If
        oCn.Open strConnectionString

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, May 2, 2012 12:41 PM