(求助各位高手)如何将文本文件中的一行存入EXCEL中的一列
我有个程序是文本输出数据,如下;
1.2 1.5 1.8 1.9 9.0
.....
.....
每行的数据的个数是随着不同的操作变化的,而且有字符穿插其中,请高手指点一下,不胜感激,当然了EXCEL能直接导入文本数据,不过我这个要的不是从EXCEL导入
[解决办法]
文本内容中按行插入EXCEL有列:
Option Explicit
Private Sub Command1_Click()
'一次读出文本内容
Dim pFile As String
Dim hFile As Integer
Dim sFile As String
pFile = "C:\test.txt "
hFile = FreeFile()
Open pFile For Binary As hFile
sFile = Space(LOF(hFile))
Get hFile, , sFile
Close hFile
'处理读取的数据: 内容行列转后换放入一个2维数组中
Dim fLine() As String
Dim sTmp() As String
Dim sExcel() As String '插入Excel的内容
Dim i As Integer, j As Integer
Dim row As Integer, col As Integer
'定义数组,很郁闷的是多维数组只能改变末维大小,不得不多此循环
fLine = Split(sFile, vbCrLf)
col = UBound(fLine)
For i = 0 To col
If UBound(Split(fLine(i), Chr(32))) > row Then
row = UBound(Split(fLine(i), Chr(32)))
End If
Next i
ReDim sExcel(row + 1, col + 1)
For i = 0 To UBound(fLine) '按行处理
sTmp = Split(fLine(i), Chr(32))
'内容行列转换
For j = 0 To UBound(sTmp)
sExcel(j, i) = sTmp(j)
Next j
Next i
'导入Excel
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Set oExcel = CreateObject( "Excel.Application ")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oSheet.Range( "A1 ").Resize(row + 1, col + 1).Value = sExcel
oExcel.Visible = True
Set oSheet = Nothing
Set oBook = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub