runtime error 1004 cant copy sheet from source to dest

Question

Really this is curious. Let me try to explain what is going on. I wrote a macro in word that is calling a macro of an excelfile

Set excel = CreateObject("excel.application")
excel.Visible = True
excel.workbooks.Open FileName:=tablePath
IDcolumn = excel.Application.Run("main", generatedExelPath)

the "main" function being called from the word macro is "simply" generating a new excel-file by copying specific data from the called excel file.

So we got 1 word and 2 excel documents. some sheets of excel1 have to be copied to excel2.

therefore I use:

For i = 0 To UBound(copySheets)
    Windows(srcWinName).Activate
    Sheets(copySheets(i)).Activate
    Set AcSh = ActiveSheet
    Windows(destWinName).Activate
    If copySheets(i) = "config" Then
        AcSh.Copy Before:=Sheets(1)
    Else
        AcSh.Copy After:=ActiveSheet
    End If
Next i

where copySheets contains the sheets that needs to be copied. But the copy commands throws an error. Something like "can't copy from source to destination cause the destination has less rows and columns than the source file"

Atm excel1 is an xlsm as it was an xls file this error didn't occur. I wonder if this line, which adds the excel2 is the prob

Workbooks.Add Template:="Workbook"

maybe i should use another template but I don't know which one.

If i create an empty xlsm file, save it and then use open this empty xlsm instead of the command before the error doesn't occur. Hope someone can help me.

Ahh and one thing I should mention is: if I run the excel macro as above from the word macro the error occurs but when I close Word and manually execute the excel macro it works.

Edit: It works

I just changed the excel-options as Alain said. here his post.

Oh I know why that's happening. You have to change your default excel format: Orb > Excel Options > |Save| > Save files in this format: "Excel Workbook (.xlsx)". Your current setting will be Excel 97-2003 Workbook (.xls). – Alain Aug 29 at 12:09

can't copy from source to destination cause the destination has less rows and columns than the source file typically occurs when you try to save a sheet created in a xlsx(m) workbook into a xls workbook. This can't be down because xlsx(m) (XL 2007/2010) files have a differenct structure (more rows) than xls (XL 2003). It seems at times in your explanation you mention this, so be sure you are copying either from xls to xlsx(m) or from 'xlsx(m)` to 'xlsx(m)` — Scott Holtzman
@Scott Holtzman this means that if i'am working with an xlsm file and use Workbooks.Add Template:="Workbook" the new workbook is added as xls ? Some idea how to change it? It works with my workaround but this way I have an empty file on my harddisk just to be sure it ll be opened as xlsm file. — lorenz albert
Hmmm... your problem does seem tricky at first. How about you reverse engineer by the following: Save a copy of the tablePath workbook for your excel2 and then loop through the sheets and delete any sheet not in the copySheets array. Probably faster (and maybe more efficient) than moving sheets over? — Scott Holtzman

Alain Alain · Accepted Answer · 2012-08-28T15:15:48

The .xls extension is the Excel 2003 workbook format. These support only 65,536 rows and 256 columns.

.xlsx,.xlsb,.xlsm are Excel 2007 workbooks. These support up to 1,048,576 rows and 16,384 columns.

This error is occurring because you are trying to copy a worksheet from a 2007 workbook to a 2003 workbook. This cannot be done because the of the row and column count limitation, even if you are not actually making use of all those extra rows and columns in the sheet being copied.

The solution is to save the .xlsm workbook as a .xls file or vice versa, and then copy the sheet over. Alternatively, rather than copying the entire sheet, you can copy just the UsedRange from one sheet to another, but risk losing important formatting such as row heights and column widths.

Adding a new workbook will create a workbook in your default save format. You can ensure this is progrmatically set to a .xlsx file by forcing the default save format in VBA and then changing it back so that you don't upset the user:

Dim prevSaveFormat As Long 
prevSaveFormat = Application.DefaultSaveFormat 
Application.DefaultSaveFormat = 51  'xlsx
Workbooks.Add 
Application.DefaultSaveFormat = prevSaveFormat

A full list of file format constants is available at XlFileFormat Enumeration but here are the common ones:

51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

runtime error 1004 cant copy sheet from source to dest

Edit: It works

1 Answers