в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:44Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/Text to Excel - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия