楼上的方法可行,但有些也还是要改一改的,否则就不通用了. 下面是一个将vsflexgrid里的内容导出到excel里的函数,你可以参考一下 '将vsFlexGrid中的内容导出成execlPublic Sub CreateExcelFile(ByRef mf As Object) Dim n As Integer Dim objExcel As Object Dim e As Excel.Application Dim objRange As Excel.Range Dim DataArray() As Variant Dim r As Long, c As Long
ReDim DataArray(mf.Rows, mf.Cols)
Dim DataFormat() As Variant ReDim DataFormat(mf.Cols)
Dim iFormatNum As Integer
CelToArray mf, DataArray, r, c
iFormatNum = GetCelFormat(mf, DataFormat)
If c > 175 Then Call Xtxxts("报表导出为Excel的显示列溢出!", 0, 4) Exit Sub End If
On Error Resume Next Set objExcel = GetObject(, "Excel.Application") If objExcel Is Nothing Then Set objExcel = CreateObject("Excel.Application") End If objExcel.Workbooks.Add Set objRange = objExcel.Range(objExcel.Cells(1, 1), objExcel.Cells(mf.Rows, c))
Dim sNum As String Dim iNum As Integer
For n = 0 To iFormatNum - 1 If DataFormat(n) = 6 Then objRange.Columns(n + 1).NumberFormat = "0" ElseIf DataFormat(n) = 5 Then objRange.Columns(n + 1).NumberFormat = "@" Else objRange.Columns(n + 1).NumberFormat = "@"
End If Next n
objRange.Value = DataArray
For n = 0 To mf.Cols - 1 objRange.Columns(n).AutoFit Next n
其中引用到了两个其他函数,我就不全部帖出来了. Private Sub CelToArray(ByRef mf As Object, ByRef tmpArray() As Variant, ByRef Rows As Long, ByRef Cols As Long) Dim i As Long, j As Long, k As Long, l As Long With mf l = 0 For i = 0 To .Rows - 1 If mf.RowHidden(i) = False Then k = 0 For j = 0 To .Cols - 1 If mf.ColHidden(j) = False Then tmpArray(l, k) = mf.TextMatrix(i, j) k = k + 1 End If Next j l = l + 1 End If Next i End With Cols = k End Sub
是不是VB里不能像操纵表一样操纵Excel文件?如果可以应该怎样做?
写记录到EXCEL表里,只要把记录查询出来,然后用VBA把记录字段一个个填充到EXCEL的表格里
用VB操作Excel,其实就是用VBA来操作Excel,只不过用VBA的代码写在VB程序内而已。
Sorry,不是很明白,能否举例说明?
xlssheet.Cells(1, 2) = "xffyttt" '这就是对单元格操作的例子.如果是想把整个excel当做一个数据源来操作的话,可以这样.Dim DATASOURCE As VariantDATASOURCE=MySheet.RANGE(“A1:B200")然后就可以把整个当成一个数组来操作,如果想当成记录集,那就如下操作Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordsetcn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Book1.xls;" & _ "Extended Properties=""Excel 8.0;""" rs.Open "Select * from [Sheet1$A1:B200]", cn, adOpenStatic
下面是一个将vsflexgrid里的内容导出到excel里的函数,你可以参考一下
'将vsFlexGrid中的内容导出成execlPublic Sub CreateExcelFile(ByRef mf As Object)
Dim n As Integer
Dim objExcel As Object
Dim e As Excel.Application
Dim objRange As Excel.Range
Dim DataArray() As Variant
Dim r As Long, c As Long
ReDim DataArray(mf.Rows, mf.Cols)
Dim DataFormat() As Variant
ReDim DataFormat(mf.Cols)
Dim iFormatNum As Integer
CelToArray mf, DataArray, r, c
iFormatNum = GetCelFormat(mf, DataFormat)
If c > 175 Then
Call Xtxxts("报表导出为Excel的显示列溢出!", 0, 4)
Exit Sub
End If
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If objExcel Is Nothing Then
Set objExcel = CreateObject("Excel.Application")
End If
objExcel.Workbooks.Add Set objRange = objExcel.Range(objExcel.Cells(1, 1), objExcel.Cells(mf.Rows, c))
Dim sNum As String
Dim iNum As Integer
For n = 0 To iFormatNum - 1
If DataFormat(n) = 6 Then
objRange.Columns(n + 1).NumberFormat = "0"
ElseIf DataFormat(n) = 5 Then
objRange.Columns(n + 1).NumberFormat = "@"
Else
objRange.Columns(n + 1).NumberFormat = "@"
End If
Next n
objRange.Value = DataArray
For n = 0 To mf.Cols - 1
objRange.Columns(n).AutoFit
Next n
Set objRange = objExcel.Range(objExcel.Cells(1, 1), objExcel.Cells(mf.FixedRows, c))
objRange.Font.name = "宋体"
objRange.Font.Size = 12
objRange.Font.Bold = True
objRange.RowHeight = 24
objRange.VerticalAlignment = 2
objRange.HorizontalAlignment = 3
Set objRange = objExcel.Range(objExcel.Cells(mf.FixedRows + 1, 1), objExcel.Cells(mf.Rows, c))
objRange.Font.name = "宋体"
objRange.Font.Size = 10
objRange.Borders.LineStyle = 7
objRange.Borders.Weight = 2
objRange.BorderAround 7, xlThick
objExcel.Visible = True
End Sub
Private Sub CelToArray(ByRef mf As Object, ByRef tmpArray() As Variant, ByRef Rows As Long, ByRef Cols As Long)
Dim i As Long, j As Long, k As Long, l As Long
With mf
l = 0
For i = 0 To .Rows - 1
If mf.RowHidden(i) = False Then
k = 0
For j = 0 To .Cols - 1
If mf.ColHidden(j) = False Then
tmpArray(l, k) = mf.TextMatrix(i, j)
k = k + 1
End If
Next j
l = l + 1
End If
Next i
End With
Cols = k
End Sub
Dim rs As New ADODB.Recordsetcn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
rs.Open "Select * from [Sheet1$A1:B200]", cn, adOpenStatic