Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copying in Excel 2007
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copying in Excel 2007
On Jan 14, 1:46*pm, jeh wrote:
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 It would help to see the code. Doug |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copying in Excel 2007
On Jan 14, 2:39*pm, Dougaj4 wrote:
On Jan 14, 1:46*pm, jeh wrote: 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 It would help to see the code. Doug- Hide quoted text - - Show quoted text - 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copying in Excel 2007
On Jan 15, 7:55*am, jeh wrote:
On Jan 14, wrote: On Jan 14, 1:46*pm, jeh wrote: 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 It would help to see the code. Doug- Hide quoted text - - Show quoted text - 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 - 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").Activ ate Range("A1").Select ActiveSheet.Paste Range("A1").Copy ' Avoid "do you want to keep clipboard contents" message Workbooks(FullInFileName).Close Savechanges:=False |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 crashes on copying worksheet to new workbook | Excel Discussion (Misc queries) | |||
Chart Relative To Worksheet When Copying- Excel 2007 | Charts and Charting in Excel | |||
Copying Excel 2007 chart to Word 2007 - only half of it shows | Excel Discussion (Misc queries) | |||
copying chart from Excel 2007 to Word 2007 | Charts and Charting in Excel | |||
Excel 2003 converted to 2007 copying worksheet error | Excel Discussion (Misc queries) |