Copying sheets between Excel versions

[Shaft120]

Hi

This is probably an old one that somebody can tell me, but apologies as I've been unable to search the solution on past posts.

My macro is copying some worksheets from a source workbook which is .xlsm (and needs to remain so) and copying them into to a new workbook I am creating through the code.

However, I need the new workbook to be saved down as .xls 97 - 2003, as it is used by another system that doesn't recognise anything else.

I had no problems running this on my system which is 2007. But one of my users was testing it for me (also using 2007) and she is getting an error along the lines of "the source data has more rows than the new workbook.. etc."

Short of saving it down as .xlsx initially and then re-opening it to save down again as .xls and having to delete the .xlsx file, is there another more simple way I'm missing? And why does this seem to be inconsistent across systems on the same version of Excel?

    SSPath = WkbAll.Path
    WSIName = SSPath & "\Client Data " & FndNo & " " & CStr(Format(Mdt, "dd-mm-yy")) & ".xls"
    Workbooks.Add.SaveAs (WSIName), FileFormat:=56
    Set WkbWSI = ActiveWorkbook
 
 
    On Error Resume Next
 
    Set shtTest = WkbAll.Sheets("B")
    If Err = 0 Then
        WkbAll.Sheets("B").Copy After:=WkbWSI.Sheets(WkbWSI.Sheets.Count)
        WkbWSI.Sheets("B").Name = "Valuation B"
    Else
        WkbWSI.Sheets.Add
        ActiveSheet.Name = "Valuation B"
    End If
 
    Err.Clear
 
    On Error Goto 0

All clues greatly appreciated..!

 

[Shaft120]

Ok - I've worked out the problem is due to the default setting the user has in their Excel options, "save files in this format:" which was set to 97-2003.

Thefore when the workbook.add call was made, it opened up a worksheet with 65k rows.

If this is set to 2007 - 10 then I get the full complement of rows which, even though I save ti down as .xls, remains until the file is colsed and re-opened again (which is after my code has run)

So - is there anyway to change the users default options at all? I presume not.

 

[MrKowz]

Ok - I've worked out the problem is due to the default setting the user has in their Excel options, "save files in this format:" which was set to 97-2003.

Thefore when the workbook.add call was made, it opened up a worksheet with 65k rows.

If this is set to 2007 - 10 then I get the full complement of rows which, even though I save ti down as .xls, remains until the file is colsed and re-opened again (which is after my code has run)

So - is there anyway to change the users default options at all? I presume not.

Try adding this line to the beginning of the code:

Application.DefaultSaveFormat = xlOpenXMLWorkbook

And then to switch it back at the end of the code:

Application.DefaultSaveFormat = xlExcel8

 

[Shaft120]

Try adding this line to the beginning of the code:

Application.DefaultSaveFormat = xlOpenXMLWorkbook

And then to switch it back at the end of the code:

Application.DefaultSaveFormat = xlExcel8

Diamond!!

Ta very much like

 

[MrKowz]

Diamond!!

Ta very much like

Glad it works, thanks for the feedback!

 

[RoryA]

IMO you should try to avoid changing the user's default options - is there a reason you can't add a new sheet and then copy the cells to that?

 

[Shaft120]

IMO you should try to avoid changing the user's default options - is there a reason you can't add a new sheet and then copy the cells to that?

I know what you mean, but add a new sheet to where?


To give some background..

The macro workbook (.xlsm) imports various file format data from seperate client files and formats into it as extra tabs. These are reconciled together against another summary client file (the purpose being to perform integrity checks on the seperate client files).

Once the user is happy with the reconciliaiton, the user has another option to create a consolidated file containing part of the client data now contained in tabs in the .xlsm for use in a seperate application.

The problem is that the seperate applciation only recognises .xls 97-03 files, so the output file has to be .xls and the macro workbook has to be 07-10.

 

[RoryA]

So you use WkbWSI.Sheets.Add and then copy the cells to there?

 

[Shaft120]

So you use WkbWSI.Sheets.Add and then copy the cells to there?

But if I add a sheet to WkbWSI, the sheet will still only be 65k rows long will it not?

when I create WkbWSI, the rows are being defined by the user options.

 

[RoryA]

Well, yes, but you can't save a bigger sheet as 97-2003 format anyway.