Microsoft Office Tutorials and References
In Depth Information
Importing and Formatting a Text File
'Set the headers in Bold¶
Range("3:3").Font.Bold = True¶
'Add a "TOTAL" column in I¶
'Use the value in B1 that contains the number of records¶
Range("I3").Value = "TOTAL"¶
Range("I4").Resize(Range("B1").Value).FormulaR1C1 = "=RC3*RC4"¶
'And a Sum of all orders¶
Range("I4").Offset(Range( _¶
"B1").Value).FormulaR1C1 = "=SUM(R4C:R[-1]C)"¶
'Remove cell "B1", which contains the total number of records¶
'Make sure that A1 is a date¶
If Not Application.IsNumber(Range("A1")) Then¶
Range("A1").Value = DateValue(Range("A1").Text)¶
End If¶
'Make sure that the zip codes are formatted correctly¶
Range("H:H").NumberFormat = "00000"¶
'Format the Total and Amount as currency¶
Range("C:C, I:I").NumberFormat = "$#,##0.00"¶
'Autofit all columns¶
'Copy this worksheet to the WhichBook book (and close this file)¶
With ActiveSheet¶
.Copy After:=WhichBook.Sheets(WhichBook.Sheets.Count)¶
.Parent.Close SaveChanges:=False¶
End With¶
End Sub¶
Note: The 'ProcessFile' procedure takes a specific type of text file that was created
just for this topic. This code needs to be modified to adapt to the type of text
files being imported. The easiest way to do that is to begin with a new
workbook and then record a macro. While the recorder is running, open the
text file, clean it, and apply the formatting that is needed. Compare your
macro code with the code used in the procedure. Edit the procedure so that it
uses some of the code that was recorded.
Search JabSto ::

Custom Search