2007 compatability

[VegasNath]

Using code from 2003 in 2007:

Code: Select all

wb2.Sheets(Format(strDate, "dd-mm")).Copy Before:=wb1.Sheets(1)

I use this identical line of code many times within one procedure. Bizarrely, some work and some don’t. I get the same error whether I try to copy the sheet in code or manually. Any idea’s as to why some work and some don’t?

Microsoft’s suggestion of using copy & paste from ws to ws is not really ideal.

1004.GIF

I have a theory: The code works with files that were created using 2003, but not where created using 2007 vba (from a text file), saved as xls. I would rather save the files as xls (I need the code to work in both versions).



I don't understand how there can be more rows / columns in the source workbook than the destination workbook, when both are xls files.

[HansV]

When you create a new workbook in Excel 2007, its worksheets have 1,048,576 rows by default. When you save it as an Excel 97-2003 workbook (.xls), the saved version has only 65,536 rows per sheet, but the version open in memory still has 1,048,576 rows. So if you try to copy a worksheet from the open workbook into an existing Excel 97-2003 workbook, you'll get the error message that you mention.

As a workaround, you can close and reopen the new workbook after saving it. This will load the saved version, which has 65,536 rows per sheet, into memory. You can then copy the sheet into an Excel 97-2003 workbook without problems.

[VegasNath]

Hans, thankyou for responding.

Unfortunately, this does not work for me. I have 2 macro's as follows: (the general gist)

Macro 1:
Woorkbooks.Add
Page setup, formatting etc etc.....
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strFilename, Destination:=Range("A1"))
.......
End with
More manipulation, formatting.....
ActiveWorkbook.SaveAs Filename:=strPath4 & "SECUR58 " & Format(strDateWork, "dd-mm-yy") & ".xls"
ActiveWindow.Close

Macro 2:
Set wb2 = Workbooks.Open(Filename:=strPath4 & "SECUR52 " & Format(strDate, "dd-mm-yy") & ".xls")

wb2.Sheets(1).Name = Format(strDate, "dd-mm")
wb2.Sheets(Format(strDate, "dd-mm")).Copy Before:=wb1.Sheets(1)
wb2.Close SaveChanges:=False


After closing and re-opening the wokbook, I get the error, even using a seperate macro in a different module.

Added:

I have just noticed that I am using "ActiveWindow.Close" (no idea why). Could this have anything to do with it?

[HansV]

If Excel 2007 format is the default format, the line

ActiveWorkbook.SaveAs Filename:=strPath4 & "SECUR58 " & Format(strDateWork, "dd-mm-yy") & ".xls"

will actually save as an Excel 2007 workbook (with 1,048,576 rows per sheet) even though the extension is .xls. Does it make a difference if you specify the format explicitly, like this:

ActiveWorkbook.SaveAs FileName:=strPath4 & "SECUR58 " & Format(strDateWork, "dd-mm-yy") & ".xls", FileFormat:=xlExcel8

By the way, in the first macro you save a workbook with SECUR58 in the name but in the second macro you use one with SECUR52 in the name...

[VegasNath]

Thankyou, I will test and let you know (probably later in the week).

Your btw is a good catch, I copied the wrong piece of code.