Geek asks and answers

Runtime error 1004 cannot copy sheet from source to dest

This is actually curious. Let me try to explain what is happening. I wrote a macro in a word that calls an excelfile macro

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

The "main" function, called from the word macro, "simply" generates a new excel file, copying certain data from the called excel file.

So, we got 1 word and 2 Excel documents. some sheets of excel1 must be copied to excel2.

so 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 to be copied. But copy commands cause an error. Something like “can't copy from source to target because the destination has fewer rows and columns than the original file”

Atm excel1 xlsm, xls, . , , excel2, -

Workbooks.Add Template:="Workbook"

, , , .

xlsm, , open this empty xlsm . , - .

Ahh, : excel, , , Word excel, .

:

excel, . .

, , . format: Orb > Excel > | | > : " Excel (.xlsx)". Excel 97-2003 (.xls). - 29 12:09

+5
1

.xls - Excel 2003. 65536 256 .

.xlsx, .xlsb, .xlsm - Excel 2007. 1 048 576 16 384 .

- , 2007 2003 . - , .

, .xlsm .xls , . UseRange , , .

. , .xlsx, VBA, , :

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

XlFileFormat Enumeration, :

  • 51 = xlOpenXMLWorkbook ( 2007-2010 , xlsx)

  • 52 = xlOpenXMLWorkbookMacroEnabled ( 2007-2010, xlsm)

  • 50 = xlExcel12 (Excel Binary Workbook 2007-2010 , xlsb)

  • 56 = xlExcel8 ( 97-2003 Excel 2007-2010, xls)

+3

More articles:


All Articles