Question

vbs将文本文件内容输入到excel中

我的代码如下，将数据源按，进行分割，分别放到2a，2b，3a，3b……单元格中，但是有问题请费心看一下。谢谢
数据格式为：
2876-mo-45,100.000*100.000*100.000
2876-mo-250,216.778*168.922*136.603

Dim a
set fso=CreateObject("Scripting.FileSystemObject")
set fr=fso.opentextfile("d:\block.txt",1)

do

data=fr.readline
if instr(data,",") then

a=split(data,",")
msgbox a(0)
msgbox a(1)
end if
Dim xlsApp,xlsWorkBook,xlsSheet
Dim Row
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = false
Set xlsWorkBook = xlsApp.Workbooks.Open ("d:\1.xls")
Set xlsSheet = xlsWorkBook.Sheets("sheet1")
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = FALSE
objExcel.Workbooks.Add
Row=2
objExcel.Cells(Row,1).Value = a(0)
objExcel.Cells(Row,2).Value = a(1)
Row=Row+1
xlsWorkBook.Save
xlsWorkBook.Close
xlsApp.Quit
Set xlsApp = Nothing
Set xlsWorkBook = Nothing
Set xlsApp = Nothing
loop until fr.atendofstream

Answer 1

'''一,将文本文件内容拆分为二维数组:
dim a()
set fso=CreateObject("Scripting.FileSystemObject")
set fr=fso.opentextfile("G:\data.txt",1)
s=fr.readall
'msgbox s
a1=split(s,chr(13))
cRow=ubound(a1)
a2=split(a1(0),",")
cCol=ubound(a2)
redim a(cRow,cCol)
for i=0 to cRow
a2=split(a1(i),",")
for j=0 to cCol
a(i,j)=a2(j)
next
next
'''建立工作簿,写入数据,保存后退出应用程序:
Dim xlsApp,xlsWorkBook,xlsSheet
Dim Row

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = FALSE
Set xlsWorkBook = objExcel.Workbooks.Add

for i=0 to cRow
for j=0 to cCol
objExcel.Cells(i+2,j+1).Value = a(i,j)
next
next

xlsWorkBook.SaveAs "G:\data.xls"
xlsWorkBook.Close
objExcel.Quit
Set xlsApp = Nothing
Set xlsWorkBook = Nothing
Set objExcel = Nothing
'''============== By daode1212 ,QQ:1501488900 ================

追问

应该是cRow-1,cCol-1 吧，运行结果在第一列，第二列没有数据

msgbox a(0,1)

msgbox a(1,1) 为什么是空，没有内容？

追答

'''文本文件data.txt内容形如:
'2876-mo-45,100.000*100.000*100.000
'2876-mo-250,216.778*168.922*136.603
'''一,将文本文件内容拆分为二维数组:
dim a()
set fso=CreateObject("Scripting.FileSystemObject")
set fr=fso.opentextfile("G:\data.txt",1)
s=fr.readall
'msgbox s
a1=split(s,chr(13))
cRow=ubound(a1)
a2=split(a1(0),",")
cCol=ubound(a2)
redim a(cRow,cCol)
for i=0 to cRow
a2=split(a1(i),",")
for j=0 to cCol
a(i,j)=a2(j)
next
next
msgbox a(0,0)
msgbox a(1,1)
'''建立工作簿,写入数据,保存后退出应用程序:
Dim xlsApp,xlsWorkBook,xlsSheet
Dim Row

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = FALSE
Set xlsWorkBook = objExcel.Workbooks.Add

for i=0 to cRow
for j=0 to cCol
objExcel.Cells(i+2,j+1).Value = a(i,j)
next
next

xlsWorkBook.SaveAs "G:\data.xls"
xlsWorkBook.Close
objExcel.Quit
Set xlsApp = Nothing
Set xlsWorkBook = Nothing
Set objExcel = Nothing
'''============== By daode1212 ,QQ:1501488900 ================
文本文件编码是:ANSI,再试试.