Microsoft Office Tutorials and References
In Depth Information
Importing and Formatting a Text File
'Create a new workbook to store the imported files¶
Set Book = Workbooks.Add(xlWorksheet)¶
For i = LBound(File) To UBound(File)¶
ProcessFile WhichFile:=CStr(File(i)), WhichBook:=Book¶
Next i¶
'Delete the first sheet (unused one)¶
Application.DisplayAlerts = False¶
Book.Sheets(1).Delete¶
Application.DisplayAlerts = True¶
'Restore screen updating¶
Application.ScreenUpdating = True¶
End Sub¶
' * * * * *¶
Sub ProcessFile(ByVal WhichFile As String, _¶
ByRef WhichBook As Workbook)¶
'Variable definition¶
Dim WS As Worksheet¶
Dim ColumnInformation As Variant¶
'Define the column types in the text file¶
'All columns will be imported as general,¶
'excepting columns 6, 7 and 8 which are dates¶
'(in dd/mm/yy format) and zip codes¶
ColumnInformation = Array(Array(1, 1), _¶
Array(2, 1), _¶
Array(3, 1), _¶
Array(4, 1), _¶
Array(5, 1), _¶
Array(6, 4), _¶
Array(7, 4), _¶
Array(8, 2))¶
'xlGeneralFormat = 1¶
'xlTextFormat = 2¶
'xlDMYFormat = 4¶
'To skip a column, use xlSkipColumn = 9¶
'Open the text file using the TextImportWizard¶
Workbooks.OpenText Filename:=WhichFile, Origin:=xlWindows, _¶
StartRow:=1, DataType:=xlDelimited, _¶
TextQualifier:=xlTextQualifierDoubleQuote, _¶
ConsecutiveDelimiter:=True, Tab:=True, _¶
FieldInfo:=ColumnInformation¶
'Do some formatting to the book. This sample files have¶
'the headers repeat every 50 records, make sure that¶
'they are imported only once¶
Do While Application.CountIf(Range("A4:A" & Rows.Count), _¶
"ID") > 0¶
Cells(Application.Match("ID", Range("A4:A" & Rows.Count), _¶
0) + 3, 1).EntireRow.Delete Shift:=xlShiftUp¶
Loop¶
Exl
Search JabSto ::




Custom Search