Worksheet copying in Excel 2007

  • Thread starter Thread starter jeh
  • Start date Start date
J

jeh

For years I've use a simple macro to copy sets of fixed format text
files into a related excel workbook, one workbook per set of input
files (up to ~60) and one new worksheet per input file. The input
files are about 400 rows x 20 columns. No problem in excel 2000.

However in Excel 2007 the same macro crashes, at the copy sheet line,
with the error message "Excel cannot insert the sheet into the
destination workbook because it contains fewer rows and columns that
the source workbook".

Has anyone got an explanation and/or a workaround for this in E2007?
Reverting to Excel 2000 isn't a long-term option.

TIA

John
 
For years I've use a simple macro to copy sets of fixed format text
files into a related excel workbook, one workbook per set of input
files (up to ~60) and one new worksheet per input file.  The input
files are about 400 rows x 20 columns.  No problem in excel 2000.

However in Excel 2007 the same macro crashes, at the copy sheet line,
with the error message  "Excel cannot insert the sheet into the
destination workbook because it contains fewer rows and columns that
the source workbook".

Has anyone got an explanation and/or a workaround for this in E2007?
Reverting to Excel 2000 isn't a long-term option.

TIA

John
Click to expand...

It would help to see the code.

Doug
 
It would help to see the code.

Doug- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Doug. The relevant code is:

Workbooks.OpenText Filename:=FullInFileName, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(5, 1), Array
(7, 1), _
Array(8, 1), Array(16, 1), Array(24, 1), Array(32,
1), Array(40, 1), _
Array(48, 1), Array(49, 1), Array(56, 1), Array(57,
1), Array(64, 1), _
Array(72, 1))
Sheets(InSheetName).Copy After:=Workbooks(DestFileName).Sheets
("Notes")

(FullInFileName, InSheetName and DestFileName are all confirmed
correct)

John
 
Thanks Doug.  The relevant code is:

      Workbooks.OpenText Filename:=FullInFileName, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(5, 1),Array
(7, 1), _
                   Array(8, 1), Array(16, 1), Array(24, 1), Array(32,
1), Array(40, 1), _
                   Array(48, 1), Array(49, 1), Array(56, 1), Array(57,
1), Array(64, 1), _
                   Array(72, 1))
      Sheets(InSheetName).Copy After:=Workbooks(DestFileName).Sheets
("Notes")

 (FullInFileName, InSheetName and DestFileName are all confirmed
correct)

John- Hide quoted text -

- Show quoted text -
Click to expand...

John - according to the Excel 2007 help the Worksheets.copy method
"Copies the sheet to another location in the workbook."

Here's how I do what you want:

Workbooks.OpenText Filename:=FullInFileName, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(5, 1), Array(7, 1), _
Array(8, 1), Array(16, 1), Array(24, 1), Array(32, 1), Array(40, 1), _
Array(48, 1), Array(49, 1), Array(56, 1), Array(57, 1), Array(64, 1),
_
Array(72, 1))
ActiveSheet.Cells.Copy

Workbooks(DestFileName).Worksheets("Sheet2").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1").Copy ' Avoid "do you want to keep clipboard contents"
message
Workbooks(FullInFileName).Close Savechanges:=False
 
Back
Top