vb计算出的数据存在 05.024 文件中,文件内容如下:
111 23 14 256 222 257
125 28 54 456 578 557
...............
等若干行我建立了一个excle 模版 rpf.xlt,内容如下(只有表头的空表格):
A B C D E F
1 日数 大小 高度 宽度 长 压
2
3
...........我想在VB中用excel调入 05.024 的内容并显示出来,因从未在VB中用过Excel,查了一些资料也没搞清楚,故求各位高手指点,最好能详细一些,谢了!
111 23 14 256 222 257
125 28 54 456 578 557
...............
等若干行我建立了一个excle 模版 rpf.xlt,内容如下(只有表头的空表格):
A B C D E F
1 日数 大小 高度 宽度 长 压
2
3
...........我想在VB中用excel调入 05.024 的内容并显示出来,因从未在VB中用过Excel,查了一些资料也没搞清楚,故求各位高手指点,最好能详细一些,谢了!
1 同时打开这两个文件
2 循环在05.024中读每一行
2.1 分解某一行
2.2 向excel中写分解后结果
2.3 控制变量+1
3 关闭05.024文件
4 显示excel结果
引用micsosoft Excel x.0 object LibraryDim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
FileCopy App.Path + "\rpf.xlt", App.Path + "\tmp.xlt" FileName = App.Path + "\tmp.xls"
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(FileName)
xlApp.Visible = TrueSet xlsheet = xlBook.Sheets("sheet1")
with xlsheet .Cells(1, 1) =变量
end with
.Cells(1, 1) =变量 中的变量是怎样的啊?我的数据要一个一个的读还是用数组变量读?
.cells(1,1) 就是excel 一个格子呀!
Dim xlsheet As Excel.Worksheet
Dim nrowcount, ncolcount As Integer
Set objexcel = New Excel.Application
objexcel.Visible = True
objexcel.SheetsInNewWorkbook = 1
objexcel.Workbooks.Add
With objexcel.ActiveSheet
.Cells(1, 1).Value = "NO"
.Cells(1, 2).Value = "BOM数据导出日期:"
.Cells(1, 3).Value = Date
.Columns(1).ColumnWidth = 3
.Cells(3, 1).Value = ""
.Columns(2).ColumnWidth = 20
.Cells(3, 2).Value = "阶层"
.Columns(3).ColumnWidth = 15
.Cells(3, 3).Value = "物料编号"
.Columns(4).ColumnWidth = 10
.Cells(3, 4).Value = "物料描述"
.Columns(5).ColumnWidth = 10
.Cells(3, 5).Value = "单位用量"
.Columns(6).ColumnWidth = 10
.Cells(3, 6).Value = "单位"
.Columns(7).ColumnWidth = 10
.Cells(3, 7).Value = "类别"
.Columns(8).ColumnWidth = 10
.Cells(3, 8).Value = "单位成本"
.Columns(9).ColumnWidth = 10
.Cells(3, 9).Value = "材料成本" .Columns(10).ColumnWidth = 10
.Cells(3, 10).Value = "人工成本"
.Columns(11).ColumnWidth = 10
.Cells(3, 11).Value = "固定费用"
.Columns(12).ColumnWidth = 10
.Cells(3, 12).Value = "变动费用"
.Columns(13).ColumnWidth = 10
.Cells(3, 13).Value = "外协费用"
.Columns(14).ColumnWidth = 10
.Cells(3, 14).Value = "合计"
.Range(.Cells(3, 1), .Cells(3, 14)).Font.Bold = True
.Range(.Cells(3, 1), .Cells(grdBOM.Rows + 3, 14)).Font.Size = 10
.Range(.Cells(3, 1), .Cells(grdBOM.Rows + 3, 14)).NumberFormat = "@"
For nrowcount = 1 To grdBOM.Rows - 1
.Cells(3 + nrowcount, 1).Value = grdBOM.TextMatrix(nrowcount, 0)
.Cells(3 + nrowcount, 2).Value = grdBOM.TextMatrix(nrowcount, 1)
.Cells(3 + nrowcount, 3).Value = grdBOM.TextMatrix(nrowcount, 2)
.Cells(3 + nrowcount, 4).Value = grdBOM.TextMatrix(nrowcount, 3)
.Cells(3 + nrowcount, 5).Value = grdBOM.TextMatrix(nrowcount, 4)
.Cells(3 + nrowcount, 6).Value = grdBOM.TextMatrix(nrowcount, 5)
.Cells(3 + nrowcount, 7).Value = grdBOM.TextMatrix(nrowcount, 6)
.Cells(3 + nrowcount, 8).Value = grdBOM.TextMatrix(nrowcount, 7)
.Cells(3 + nrowcount, 9).Value = grdBOM.TextMatrix(nrowcount, 8)
.Cells(3 + nrowcount, 10).Value = grdBOM.TextMatrix(nrowcount, 9)
.Cells(3 + nrowcount, 11).Value = grdBOM.TextMatrix(nrowcount, 10)
.Cells(3 + nrowcount, 12).Value = grdBOM.TextMatrix(nrowcount, 11)
.Cells(3 + nrowcount, 13).Value = grdBOM.TextMatrix(nrowcount, 12)
.Cells(3 + nrowcount, 14).Value = grdBOM.TextMatrix(nrowcount, 13)
Next nrowcount
.Cells(1, 1).Value = "OK"
End With
End Sub这个是我为公司ERP写的辅助软件的导出数据到excel上
Dim strLine As String
Dim strData() As stringcnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"Open "05.024" For Input As #1
Do Until EOF(1)
Line Input #1, strLine
strData = Split(strLine, Space(1))
If Ubound(strData) = 5 Then
cnExcel.Execute "Insert Into SheetName(日数,大小,高度,宽度,长,压) Values(" & _
strData(0) & "," & strData(1) & "," & strData(2) & "," & strData(3) & _
"," & strData(4) & "," & strData(5) & ")"
End If
Loop
Close #1
cnExcel.Close