这是操作excel的例子可以参考一下
Private Sub Command1_Click()
Dim Irow, Icol As Integer
Dim IrowCount, IcolCount As Integer
Dim Fieldlen1 As Integer
'存字段长度值
Dim Fieldlen()
'Dim xlApp As Excel.Application
'Dim xlBook As Excel.Workbook
'Dim xlSheet As Excel.Worksheet
' Set xlApp = CreateObject("Excel.Application")
' Set xlBook = xlApp.Workbooks.Add
' Set xlSheet = xlBook.Worksheets(1)
Dim strSource, strDestination As String
strDestination = App.path & "\Excels\TempExcel.xls"
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
xlApp.Workbooks.Open (strDestination)
Set xlBook = xlApp.Workbooks(1)
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.ScreenUpdating = False 'On Error GoTo excle
With Rs_temp If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Sub
End If '记录总数
IrowCount = .RecordCount
'字段总数
IcolCount = .Fields.Count ReDim Fieldlen(IcolCount)
.MoveFirst
For Irow = 1 To IrowCount + 1
For Icol = 1 To IcolCount
Select Case Irow
'在Excel中的第一行加标题
Case 1
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1).Name
'将数组FIELDLEN()存为第一条记录的字段长
Case 2
If IsNull(.Fields(Icol - 1)) = True Then
Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name)
'如果字段值为NULL,则将数组Filelen(Icol)的值设为标题名的宽度
Else
Fieldlen(Icol) = LenB(.Fields(Icol - 1))
End If xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
'Excel列宽等于字段长
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
'向Excel的CellS中写入字段值
Case Else
If IsNull(.Fields(Icol - 1)) Then
Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name)
Else
Fieldlen1 = LenB(.Fields(Icol - 1))
End If If Fieldlen(Icol) < Fieldlen1 Then
xlSheet.Columns(Icol).ColumnWidth = IIf(Fieldlen1 > 255, 255, Fieldlen1)
'表格列宽等于较长字段长
Fieldlen(Icol) = IIf(Fieldlen1 > 255, 255, Fieldlen1)
'数组Fieldlen(Icol)中存放最大字段长度值
Else
xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
End If xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
End Select
Next If Irow <> 1 Then
If Not .EOF Then .MoveNext
End If
Next
xlSheet.Columns(1).ColumnWidth = 15
xlSheet.Columns(2).ColumnWidth = 15
xlSheet.Columns(3).ColumnWidth = 15
xlSheet.Columns(4).ColumnWidth = 15
xlSheet.Columns(5).ColumnWidth = 15
'网格线
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Name = "黑体"
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(Irow, Icol - 1)).Borders.LineStyle = xlContinuous
'设表格边框样式
End With
'*!* 页眉、填报单位、报表时间、单位
With xlSheet.PageSetup
.LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称:" ' & Gsmc
.CenterHeader = "&""楷体_GB2312,常规""公司人员情况表&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期:"
.RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位:"
.LeftFooter = "&""楷体_GB2312,常规""&10制表人:"
.CenterFooter = "&""楷体_GB2312,常规""&10制表日期:"
.RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"
End With '显示表格
' Dim ExclFileName As String
' ExclFileName = App.path & "\公司人员情况表.xls"
' If Dir(ExclFileName) <> "" Then
' Kill ExclFileName
' End If
' xlSheet.SaveAs (ExclFileName)
xlApp.Application.Visible = True
xlApp.ScreenUpdating = True '交还控制给Excel
' xlsheet.PrintPreview
Set xlApp = Nothing '"交还控制给Excel
Set xlBook = Nothing
Set xlSheet = Nothing
' xlApp.Quit
End With
'excle:
' MsgBox ("您没有安装 Excle2000,请先安装 Excel2000 !")End Sub
Private Sub Command1_Click()
Dim Irow, Icol As Integer
Dim IrowCount, IcolCount As Integer
Dim Fieldlen1 As Integer
'存字段长度值
Dim Fieldlen()
'Dim xlApp As Excel.Application
'Dim xlBook As Excel.Workbook
'Dim xlSheet As Excel.Worksheet
' Set xlApp = CreateObject("Excel.Application")
' Set xlBook = xlApp.Workbooks.Add
' Set xlSheet = xlBook.Worksheets(1)
Dim strSource, strDestination As String
strDestination = App.path & "\Excels\TempExcel.xls"
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
xlApp.Workbooks.Open (strDestination)
Set xlBook = xlApp.Workbooks(1)
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.ScreenUpdating = False 'On Error GoTo excle
With Rs_temp If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Sub
End If '记录总数
IrowCount = .RecordCount
'字段总数
IcolCount = .Fields.Count ReDim Fieldlen(IcolCount)
.MoveFirst
For Irow = 1 To IrowCount + 1
For Icol = 1 To IcolCount
Select Case Irow
'在Excel中的第一行加标题
Case 1
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1).Name
'将数组FIELDLEN()存为第一条记录的字段长
Case 2
If IsNull(.Fields(Icol - 1)) = True Then
Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name)
'如果字段值为NULL,则将数组Filelen(Icol)的值设为标题名的宽度
Else
Fieldlen(Icol) = LenB(.Fields(Icol - 1))
End If xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
'Excel列宽等于字段长
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
'向Excel的CellS中写入字段值
Case Else
If IsNull(.Fields(Icol - 1)) Then
Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name)
Else
Fieldlen1 = LenB(.Fields(Icol - 1))
End If If Fieldlen(Icol) < Fieldlen1 Then
xlSheet.Columns(Icol).ColumnWidth = IIf(Fieldlen1 > 255, 255, Fieldlen1)
'表格列宽等于较长字段长
Fieldlen(Icol) = IIf(Fieldlen1 > 255, 255, Fieldlen1)
'数组Fieldlen(Icol)中存放最大字段长度值
Else
xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
End If xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
End Select
Next If Irow <> 1 Then
If Not .EOF Then .MoveNext
End If
Next
xlSheet.Columns(1).ColumnWidth = 15
xlSheet.Columns(2).ColumnWidth = 15
xlSheet.Columns(3).ColumnWidth = 15
xlSheet.Columns(4).ColumnWidth = 15
xlSheet.Columns(5).ColumnWidth = 15
'网格线
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Name = "黑体"
'设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Bold = True
'标题字体加粗
.Range(.Cells(1, 1), .Cells(Irow, Icol - 1)).Borders.LineStyle = xlContinuous
'设表格边框样式
End With
'*!* 页眉、填报单位、报表时间、单位
With xlSheet.PageSetup
.LeftHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10公司名称:" ' & Gsmc
.CenterHeader = "&""楷体_GB2312,常规""公司人员情况表&""宋体,常规""" & Chr(10) & "&""楷体_GB2312,常规""&10日 期:"
.RightHeader = "" & Chr(10) & "&""楷体_GB2312,常规""&10单位:"
.LeftFooter = "&""楷体_GB2312,常规""&10制表人:"
.CenterFooter = "&""楷体_GB2312,常规""&10制表日期:"
.RightFooter = "&""楷体_GB2312,常规""&10第&P页 共&N页"
End With '显示表格
' Dim ExclFileName As String
' ExclFileName = App.path & "\公司人员情况表.xls"
' If Dir(ExclFileName) <> "" Then
' Kill ExclFileName
' End If
' xlSheet.SaveAs (ExclFileName)
xlApp.Application.Visible = True
xlApp.ScreenUpdating = True '交还控制给Excel
' xlsheet.PrintPreview
Set xlApp = Nothing '"交还控制给Excel
Set xlBook = Nothing
Set xlSheet = Nothing
' xlApp.Quit
End With
'excle:
' MsgBox ("您没有安装 Excle2000,请先安装 Excel2000 !")End Sub
想不出来
不解
等待
我怎么这么弱
一步一步生成连接字符串就可以了!
感謝大家對我的支持,希望你們多多幫忙。幫我盡早解決這個問題!