我调用下面的函数得到了一个excel文件,现在想通过commondialog选择该文件的保存位置,该怎么做啊??我也做了下,都有问题。怎么得到这个文件的文件名给commondialog.filename?或者还是这个函数的返回值要改下?
Public Function SaveAsExcel(myrs2 As ADODB.Recordset) As Excel.Workbook
Dim rs As New ADODB.Recordset
Dim objExcel As New Excel.Application '定义EXCEL类
Dim objBook As New Excel.Workbook '定义工件簿类
Dim objSheet As New Excel.Worksheet '定义工作表类
Dim rowCount As Long
Dim colCount As Integer
Dim j As Integer
Dim i As Integer
Set objExcel = CreateObject("Excel.Application") '创建EXCEL对象
Set objBook = objExcel.Workbooks.Add
On Error GoTo Error1:
Set objSheet = objBook.Worksheets.Add
Set rs = myrs2
colCount = rs.Fields.Count
rowCount = rs.RecordCount
'保存字段名
For i = 0 To colCount - 1
objSheet.Cells(1, i + 1) = rs.Fields(i).Name '+ Chr(13)
objSheet.Columns(i + 1).Select
Selection.NumberFormatLocal = "@"
Next
'保存数据
For j = 2 To rowCount + 1
For i = 0 To colCount - 1
objSheet.Cells(j, i + 1) = CStr(rs.Fields(i) & "")
Next
rs.MoveNext
Next
rs.MoveFirst
SaveAsExcel = objBook
Set rs = Nothing
objBook.Close
objExcel.Quit
Set objExcel = Nothing
Exit Function
Error1:
MsgBox Error
objBook.Close
objExcel.Quit
Set objExcel = Nothing
End Function
Public Function SaveAsExcel(myrs2 As ADODB.Recordset) As Excel.Workbook
Dim rs As New ADODB.Recordset
Dim objExcel As New Excel.Application '定义EXCEL类
Dim objBook As New Excel.Workbook '定义工件簿类
Dim objSheet As New Excel.Worksheet '定义工作表类
Dim rowCount As Long
Dim colCount As Integer
Dim j As Integer
Dim i As Integer
Set objExcel = CreateObject("Excel.Application") '创建EXCEL对象
Set objBook = objExcel.Workbooks.Add
On Error GoTo Error1:
Set objSheet = objBook.Worksheets.Add
Set rs = myrs2
colCount = rs.Fields.Count
rowCount = rs.RecordCount
'保存字段名
For i = 0 To colCount - 1
objSheet.Cells(1, i + 1) = rs.Fields(i).Name '+ Chr(13)
objSheet.Columns(i + 1).Select
Selection.NumberFormatLocal = "@"
Next
'保存数据
For j = 2 To rowCount + 1
For i = 0 To colCount - 1
objSheet.Cells(j, i + 1) = CStr(rs.Fields(i) & "")
Next
rs.MoveNext
Next
rs.MoveFirst
SaveAsExcel = objBook
Set rs = Nothing
objBook.Close
objExcel.Quit
Set objExcel = Nothing
Exit Function
Error1:
MsgBox Error
objBook.Close
objExcel.Quit
Set objExcel = Nothing
End Function
以commondialog.Filename 作为另存为的路径就行了