Posted by Jon Sacks on August 25, 2006 at 08:08:28:
I thought I would repost a response in an earlier thread on how I get files into Excel. Since Excel wants to treat numeric looking strings as numeric in .csv imports, I prefer to use the "Worbooks.OpenText" VB command to get the data properly formated within a macro called "Auto_Open", the Auto open will run as soon as the Excel Spread Sheet. Based on the below all the user does is run the report and then it simply is presented in a formated spread sheet for the user in Excel. The user does not even need to open Excel or the Auto_Open spread sheet.
It's important to note that this macro is in a static Excel spread sheet. This spread sheet will never contain the data, the spread sheet is opening the datafile in a new spread sheet and formating it. By doing this you can leave the macro spread sheet as read-only and don't have to worry about the end user screwing it up.
-----------------------------------------------------------
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
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\tmp\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