VBA/Excel/Access/Word/Excel/Text to Excel
Содержание
Excel has built-in options to read files where fields are delimited by tabs, semicolons, commas, or spaces.
<source lang="vb">
Sub Main()
Workbooks.OpenText Filename:= "C:\sales.txt", Origin:=437, DataType:=xlDelimited, Other:=True, OtherChar:= "|", FieldInfo:=...
End Sub
</source>
Import a file
<source lang="vb">
Option Explicit Sub ImportDemo()
Dim fname$ Dim newworkb As Workbook fname = ThisWorkbook.Path + "\file.txt" Workbooks.OpenText Filename:=fname, _ Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _ Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _ Other:=False, DecimalSeparator:=".", ThousandsSeparator:=" ", _ FieldInfo:=Array(Array(1, 1), Array(2, 3), Array(3, 1), Array(4, 1)) Set newworkb = ActiveWorkbook newworkb.Worksheets(1).Copy after:=ThisWorkbook.Worksheets(1) newworkb.Close False
End Sub
</source>
Opening Delimited Files Simplified
<source lang="vb">
Sub TestOpenDelimitedFile()
Dim myWorkbook As Workbook Dim vFields As Variant vFields = Array(Array(3, xlMDYFormat)) Set myWorkbook = OpenDelimitedFile("C:\tab delimited.txt", 2,xlTextQualifierNone, False, vbTab, vFields) Set myWorkbook = Nothing
End Sub Function OpenDelimitedFile(sFile As String, _
lStartRow As Long, _ TxtQualifier As XlTextQualifier, _ bConsecutiveDelimiter As Boolean, _ sDelimiter As String, _ Optional vFieldInfo As Variant) As Workbook On Error GoTo ErrHandler If IsMissing(vFieldInfo) Then Application.Workbooks.OpenText _ Filename:=sFile, _ StartRow:=lStartRow, _ DataType:=xlDelimited, _ TextQualifier:=TxtQualifier, _ ConsecutiveDelimiter:=bConsecutiveDelimiter, _ Other:=True, _ OtherChar:=sDelimiter Else Application.Workbooks.OpenText _ Filename:=sFile, _ StartRow:=lStartRow, _ DataType:=xlDelimited, _ TextQualifier:=TxtQualifier, _ ConsecutiveDelimiter:=bConsecutiveDelimiter, _ Other:=True, _ OtherChar:=sDelimiter, _ FieldInfo:=vFieldInfo End If Set OpenDelimitedFile = ActiveWorkbook
ExitPoint:
Exit Function
ErrHandler:
Set OpenDelimitedFile = Nothing Resume ExitPoint
End Function
</source>
Opening Fixed-Width Files
<source lang="vb">
Sub TestOpenFixedWidthFile()
Dim myWorkbook As Workbook Dim vFields As Variant vFields = Array( _ Array(0, xlGeneralFormat), _ Array(7, xlGeneralFormat), _ Array(21, xlMDYFormat), _ Array(32, xlGeneralFormat), _ Array(43, xlGeneralFormat)) Set myWorkbook = OpenFixedWidthFile("C:\fixed width orders.txt", 1, vFields) Set myWorkbook = Nothing
End Sub Function OpenFixedWidthFile(sFile As String,lStartRow As Long,vFieldInfo As Variant) As Workbook
On Error GoTo ErrHandler Application.Workbooks.OpenText _ Filename:=sFile, _ StartRow:=lStartRow, _ DataType:=xlFixedWidth, _ FieldInfo:=vFieldInfo Set OpenFixedWidthFile = ActiveWorkbook
ExitPoint:
Exit Function
ErrHandler:
Set OpenFixedWidthFile = Nothing Resume ExitPoint
End Function
</source>