upgrade macros to 07

  • Thread starter Thread starter tigo
  • Start date Start date
T

tigo

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
 
'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.
 
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 Before:=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
 
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.
 
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?
 
Back
Top