Posted by Jon Sacks on June 22, 2006 at 08:49:15:
In Reply to: Launching Excel posted by Louisa Townsley on June 21, 2006 at 10:17:44:
I choose to use a different approach. Excel allows you to create an "Auto_Open" macro that will launch when the user opens the file.
I create an empty spread sheet that contains nothing but this macro.
The macro opens the comma delimited file and uses the import wizard to define the fields and properly format them. In the following example a comma delimited file called "ISRIPC2" that is generated by the report generator, is imported into a spread sheet, formatted and the operator is asked if they want to add sub-totals. It then closes the file containing the macro and leaves the user in the new imported file.
A few important notes:
1. The report generator must use the PRINTER [filename],[DIR],H command to get you the column headings.
2. The report generator should use the LAUNCH [path]\[Excel file containing the macro] not the output file in the printer statement.
3. I always create these files on the local system so there is no contention over the file on the network. This can be TMP but I prefer to have multiple local scratch folders configured.
Here is the macro:
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
JSACKS<<