VBA to force compatiblity mode off


  • I have a workbook with a macro which I distribute to various users.

    It prepares another workbook on the user's PC, and copies some worksheets from my workbook to their workbook.

    All the users have Excel 2007 or 2010, and if their system does not default to compatiblity mode, evething is fine.

    However some of them have compatiblity mode, and then the line

    Sheets("MyWorksheet").Copy Before:=Workbooks(NewWorkbookOnUserComputer).Sheets(1)

    fails because the number of lines in MyWorksheet is too large (since it is an .xlsm) worksheet.

    I have tried to force the freshly created file to save in the new format, but it still remains in compatiblity mode.

    Workbooks(NewWorkbookOnUserComputer).SaveAs FileName:=strSaveFileName, CreateBackup:=False, FileFormat:=51


    Monday, April 09, 2012 4:26 PM


  • This is two ways saveas:

    'old style
    sheets("arkusz1").SaveAs FileName:="C:\raporty\nazwa_pliku.xls", FileFormat:=xlExcel8, CreateBackup:=False
    'new one
    sheets("arkusz1").SaveAs FileName:="C:\raporty\nazwa_pliku.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    If you want to copy youre range from old style first set range.

    Dim wkb as workbooks, wks as worksheet

    Dim wkb2 as workbooks, wks2 as worksheet Dim rng1 As Range, rng2 As Range Set wkb = Workbooks( Set wks = wkb.Sheets( Set rng1 = .Range("a1:" & .Cells.SpecialCells(xlLastCell).Address)

    Set wkb2 = Workbooks("dest_file")
    Set wks2 = wkb2.Sheets("Name_dest_sheet")
    Set rng2 = wks2.Range("a1")

    if wks2.rows = 65536 and rng1.rows >65536 than
    msgbox "you cant copy. youre source > dest range"
    rng1.Copy rng2
    end if

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Monday, April 09, 2012 8:55 PM