Sub Auto_Open() ' ' Import_Format_Data_and_Save Macro ' Macro by Jonathan Sacks ' Modified for CGear 2/22/02 JSacks '' Cells.Select Selection.ClearContents Range("A1").Select 'Get Text file from Comet ISRIPC2 'The Array represents the comma delimited fields 'The array is build by (culumn,type) 'Types are: 1=Text, 2=General(Numeric) Workbooks.OpenText FileName:="C:\comet\ebw\isripc2", Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _ Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array( _ 3, 2), Array(4, 2), Array(5, 2), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _ , 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 2), Array(16, 2)) 'Do Autoformat-Set the column Widths to autofit the fields Cells.Select Selection.Columns.AutoFit 'Center Column Headings Rows("1:1").Select Selection.Font.Bold = True With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .ShrinkToFit = False .MergeCells = False End With Range("A1").Select Cells.Select 'The following Routine is an example of adding 'subtotals to the report, a message box is presented 'And based on the answer the macro will insert sub-totals 'Ask operator to Inser Sub Totals Do_Total = MsgBox("Add Subtotals?", 4 + 32, "Add Subtotals to ISRIPC2") 'MsgBox "Response was =" & Do_Total, vbCritical, "Diag Box" If Do_Total = vbYes Then Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 8, _ 9, 10, 11, 12, 13, 14), Replace:=True, PageBreaks:=False, SummaryBelowData:= _ True End If 'Position Cursor in A1 Range("a1").Select 'Go back to the original spread sheet and close it, 'leaving open the imported sheet. Windows("ISRIPC2EB.xls").Activate ActiveWorkbook.Close SaveChanges:=False End Sub