| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
upgrade macros to 07
I had macros running in Excel 97 and now was upgraded to 07. The macro won't
run, error says there aren't enough rows in destination file. I"m a novice macro user and this was written by someone a year ago who has since retired. It gets a text file, dumps it to Excel and formats it (takes out repetitive garbage headers that reoccur throughout). What do I need to do to transition my existing excel files with macros to 07? TY in advance |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
upgrade macros to 07
Post the code of your macro and the exact error message
-- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... I had macros running in Excel 97 and now was upgraded to 07. The macro won't run, error says there aren't enough rows in destination file. I"m a novice macro user and this was written by someone a year ago who has since retired. It gets a text file, dumps it to Excel and formats it (takes out repetitive garbage headers that reoccur throughout). What do I need to do to transition my existing excel files with macros to 07? TY in advance |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
upgrade macros to 07
'Run Time error 1004':
Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then us the Copy and Paste commands to instert it into the sheets of another workbook. "Niek Otten" wrote: Post the code of your macro and the exact error message -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... I had macros running in Excel 97 and now was upgraded to 07. The macro won't run, error says there aren't enough rows in destination file. I"m a novice macro user and this was written by someone a year ago who has since retired. It gets a text file, dumps it to Excel and formats it (takes out repetitive garbage headers that reoccur throughout). What do I need to do to transition my existing excel files with macros to 07? TY in advance |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
upgrade macros to 07
myFile = Application.GetOpenFilename("Text Files,*.txt") Workbooks.OpenText Filename:=myFile, _ Origin:=437, _ StartRow:=3, _ DataType:=xlFixedWidth, _ FieldInfo:=Array( _ Array(0, 1), Array(8, 1), Array(14, 1), Array(25, 1), Array(29, 1), Array(39, 1), Array(42, _ 1), Array(75, 1), Array(98, 1), Array(106, 1), Array(110, 1), Array(114, 1), Array(137, 1), _ Array(148, 1), Array(165, 1), Array(180, 1), Array(190, 1)), TrailingMinusNumbers:= _ True ActiveWindow.WindowState = xlNormal ActiveWindow.SmallScroll Down:=-6 ActiveSheet.Move Befo=Workbooks("del022410.xls").Sheets(1) Rows("2:2").Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveCell.FormulaR1C1 = "DateShip" Range("B1").Select ActiveCell.FormulaR1C1 = "Days Late" Range("C1").Select ActiveCell.FormulaR1C1 = "Late Order" Range("D1").Select ActiveCell.FormulaR1C1 = "Itm Rel" Columns("E:E").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Quantity" Range("M1").Select ActiveCell.FormulaR1C1 = "Dollars" Range("N1").Select ActiveCell.FormulaR1C1 = "OnHand" Range("O1").Select ActiveCell.FormulaR1C1 = "Alloc" Range("A1").Select "Niek Otten" wrote: Post the code of your macro and the exact error message -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... I had macros running in Excel 97 and now was upgraded to 07. The macro won't run, error says there aren't enough rows in destination file. I"m a novice macro user and this was written by someone a year ago who has since retired. It gets a text file, dumps it to Excel and formats it (takes out repetitive garbage headers that reoccur throughout). What do I need to do to transition my existing excel files with macros to 07? TY in advance |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
upgrade macros to 07
It seems you are using an Excel 2003 format file with 64k rows and an .xls
file type extension. Save the destination file as an Excel 2007 file (with a million rows, it will get a .xlsx extension) and I expect your problem will be gone. -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... myFile = Application.GetOpenFilename("Text Files,*.txt") Workbooks.OpenText Filename:=myFile, _ Origin:=437, _ StartRow:=3, _ DataType:=xlFixedWidth, _ FieldInfo:=Array( _ Array(0, 1), Array(8, 1), Array(14, 1), Array(25, 1), Array(29, 1), Array(39, 1), Array(42, _ 1), Array(75, 1), Array(98, 1), Array(106, 1), Array(110, 1), Array(114, 1), Array(137, 1), _ Array(148, 1), Array(165, 1), Array(180, 1), Array(190, 1)), TrailingMinusNumbers:= _ True ActiveWindow.WindowState = xlNormal ActiveWindow.SmallScroll Down:=-6 ActiveSheet.Move Befo=Workbooks("del022410.xls").Sheets(1) Rows("2:2").Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveCell.FormulaR1C1 = "DateShip" Range("B1").Select ActiveCell.FormulaR1C1 = "Days Late" Range("C1").Select ActiveCell.FormulaR1C1 = "Late Order" Range("D1").Select ActiveCell.FormulaR1C1 = "Itm Rel" Columns("E:E").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Quantity" Range("M1").Select ActiveCell.FormulaR1C1 = "Dollars" Range("N1").Select ActiveCell.FormulaR1C1 = "OnHand" Range("O1").Select ActiveCell.FormulaR1C1 = "Alloc" Range("A1").Select "Niek Otten" wrote: Post the code of your macro and the exact error message -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... I had macros running in Excel 97 and now was upgraded to 07. The macro won't run, error says there aren't enough rows in destination file. I"m a novice macro user and this was written by someone a year ago who has since retired. It gets a text file, dumps it to Excel and formats it (takes out repetitive garbage headers that reoccur throughout). What do I need to do to transition my existing excel files with macros to 07? TY in advance |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
upgrade macros to 07
Thank you. Unfortunately, I"m getting the same error.
"Niek Otten" wrote: It seems you are using an Excel 2003 format file with 64k rows and an .xls file type extension. Save the destination file as an Excel 2007 file (with a million rows, it will get a .xlsx extension) and I expect your problem will be gone. -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... myFile = Application.GetOpenFilename("Text Files,*.txt") Workbooks.OpenText Filename:=myFile, _ Origin:=437, _ StartRow:=3, _ DataType:=xlFixedWidth, _ FieldInfo:=Array( _ Array(0, 1), Array(8, 1), Array(14, 1), Array(25, 1), Array(29, 1), Array(39, 1), Array(42, _ 1), Array(75, 1), Array(98, 1), Array(106, 1), Array(110, 1), Array(114, 1), Array(137, 1), _ Array(148, 1), Array(165, 1), Array(180, 1), Array(190, 1)), TrailingMinusNumbers:= _ True ActiveWindow.WindowState = xlNormal ActiveWindow.SmallScroll Down:=-6 ActiveSheet.Move Befo=Workbooks("del022410.xls").Sheets(1) Rows("2:2").Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveCell.FormulaR1C1 = "DateShip" Range("B1").Select ActiveCell.FormulaR1C1 = "Days Late" Range("C1").Select ActiveCell.FormulaR1C1 = "Late Order" Range("D1").Select ActiveCell.FormulaR1C1 = "Itm Rel" Columns("E:E").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Quantity" Range("M1").Select ActiveCell.FormulaR1C1 = "Dollars" Range("N1").Select ActiveCell.FormulaR1C1 = "OnHand" Range("O1").Select ActiveCell.FormulaR1C1 = "Alloc" Range("A1").Select "Niek Otten" wrote: Post the code of your macro and the exact error message -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... I had macros running in Excel 97 and now was upgraded to 07. The macro won't run, error says there aren't enough rows in destination file. I"m a novice macro user and this was written by someone a year ago who has since retired. It gets a text file, dumps it to Excel and formats it (takes out repetitive garbage headers that reoccur throughout). What do I need to do to transition my existing excel files with macros to 07? TY in advance |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
upgrade macros to 07
tried it again (had to change the file name in vbs) but now I get this error:
Run-time error '9': Subscript out of range Can you help? "tigo" wrote: Thank you. Unfortunately, I"m getting the same error. "Niek Otten" wrote: It seems you are using an Excel 2003 format file with 64k rows and an .xls file type extension. Save the destination file as an Excel 2007 file (with a million rows, it will get a .xlsx extension) and I expect your problem will be gone. -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... myFile = Application.GetOpenFilename("Text Files,*.txt") Workbooks.OpenText Filename:=myFile, _ Origin:=437, _ StartRow:=3, _ DataType:=xlFixedWidth, _ FieldInfo:=Array( _ Array(0, 1), Array(8, 1), Array(14, 1), Array(25, 1), Array(29, 1), Array(39, 1), Array(42, _ 1), Array(75, 1), Array(98, 1), Array(106, 1), Array(110, 1), Array(114, 1), Array(137, 1), _ Array(148, 1), Array(165, 1), Array(180, 1), Array(190, 1)), TrailingMinusNumbers:= _ True ActiveWindow.WindowState = xlNormal ActiveWindow.SmallScroll Down:=-6 ActiveSheet.Move Befo=Workbooks("del022410.xls").Sheets(1) Rows("2:2").Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveCell.FormulaR1C1 = "DateShip" Range("B1").Select ActiveCell.FormulaR1C1 = "Days Late" Range("C1").Select ActiveCell.FormulaR1C1 = "Late Order" Range("D1").Select ActiveCell.FormulaR1C1 = "Itm Rel" Columns("E:E").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Quantity" Range("M1").Select ActiveCell.FormulaR1C1 = "Dollars" Range("N1").Select ActiveCell.FormulaR1C1 = "OnHand" Range("O1").Select ActiveCell.FormulaR1C1 = "Alloc" Range("A1").Select "Niek Otten" wrote: Post the code of your macro and the exact error message -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... I had macros running in Excel 97 and now was upgraded to 07. The macro won't run, error says there aren't enough rows in destination file. I"m a novice macro user and this was written by someone a year ago who has since retired. It gets a text file, dumps it to Excel and formats it (takes out repetitive garbage headers that reoccur throughout). What do I need to do to transition my existing excel files with macros to 07? TY in advance |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
upgrade macros to 07
ActiveSheet.Move Befo=Workbooks("del022410.xlsx").Sheets(1)
"tigo" wrote: tried it again (had to change the file name in vbs) but now I get this error: Run-time error '9': Subscript out of range Can you help? "tigo" wrote: Thank you. Unfortunately, I"m getting the same error. "Niek Otten" wrote: It seems you are using an Excel 2003 format file with 64k rows and an .xls file type extension. Save the destination file as an Excel 2007 file (with a million rows, it will get a .xlsx extension) and I expect your problem will be gone. -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... myFile = Application.GetOpenFilename("Text Files,*.txt") Workbooks.OpenText Filename:=myFile, _ Origin:=437, _ StartRow:=3, _ DataType:=xlFixedWidth, _ FieldInfo:=Array( _ Array(0, 1), Array(8, 1), Array(14, 1), Array(25, 1), Array(29, 1), Array(39, 1), Array(42, _ 1), Array(75, 1), Array(98, 1), Array(106, 1), Array(110, 1), Array(114, 1), Array(137, 1), _ Array(148, 1), Array(165, 1), Array(180, 1), Array(190, 1)), TrailingMinusNumbers:= _ True ActiveWindow.WindowState = xlNormal ActiveWindow.SmallScroll Down:=-6 ActiveSheet.Move Befo=Workbooks("del022410.xls").Sheets(1) Rows("2:2").Select Selection.Delete Shift:=xlUp Range("A1").Select ActiveCell.FormulaR1C1 = "DateShip" Range("B1").Select ActiveCell.FormulaR1C1 = "Days Late" Range("C1").Select ActiveCell.FormulaR1C1 = "Late Order" Range("D1").Select ActiveCell.FormulaR1C1 = "Itm Rel" Columns("E:E").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Quantity" Range("M1").Select ActiveCell.FormulaR1C1 = "Dollars" Range("N1").Select ActiveCell.FormulaR1C1 = "OnHand" Range("O1").Select ActiveCell.FormulaR1C1 = "Alloc" Range("A1").Select "Niek Otten" wrote: Post the code of your macro and the exact error message -- Kind regards, Niek Otten Microsoft MVP - Excel "tigo" wrote in message ... I had macros running in Excel 97 and now was upgraded to 07. The macro won't run, error says there aren't enough rows in destination file. I"m a novice macro user and this was written by someone a year ago who has since retired. It gets a text file, dumps it to Excel and formats it (takes out repetitive garbage headers that reoccur throughout). What do I need to do to transition my existing excel files with macros to 07? TY in advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Upgrade to 2007 | New Users to Excel | |||
| How do I upgrade to 2003 | Excel Discussion (Misc queries) | |||
| Excel Upgrade | Setting up and Configuration of Excel | |||
| Excel upgrade | Setting up and Configuration of Excel | |||
| Lost XL on XP upgrade | Excel Discussion (Misc queries) | |||
Linear Mode
