使用如下代码时,只能将数据导出到已经存在的excel文件,如果在弹出的对话框里输入磁盘没有的文件点击保存按钮后,提示指定路径的XX.xls文件不存在。现在要求可以任意指定要生成的全新excel的名字,点击保存后生成新的excel文件。
请提供尽可能详细的代码,本人比较菜,谢谢帮助。
[code]
Sub exportcmd_Click()
Dim mExcelFile As String
CommonDialog1.Filter = "Excel File|*.xls"
CommonDialog1.ShowSave
mExcelFile = CommonDialog1.FileName
CommonDialog1.FileName = ""
If mExcelFile = "" Then
Exit Sub
Else
ExportAccessToExcelSheet "sheet1", mExcelFile, "fileinfo", App.Path & "\FileManager.mdb"
MsgBox "Data has been exported to Excel file."
End If
Exit Sub
End Sub
[/code]
请提供尽可能详细的代码,本人比较菜,谢谢帮助。
[code]
Sub exportcmd_Click()
Dim mExcelFile As String
CommonDialog1.Filter = "Excel File|*.xls"
CommonDialog1.ShowSave
mExcelFile = CommonDialog1.FileName
CommonDialog1.FileName = ""
If mExcelFile = "" Then
Exit Sub
Else
ExportAccessToExcelSheet "sheet1", mExcelFile, "fileinfo", App.Path & "\FileManager.mdb"
MsgBox "Data has been exported to Excel file."
End If
Exit Sub
End Sub
[/code]
Dim mExcelFile As String
CommonDialog1.Filter = "Excel File|*.xls"
CommonDialog1.ShowSave
mExcelFile = CommonDialog1.FileName
CommonDialog1.FileName = ""
If mExcelFile = "" Then
Exit Sub
Else
ExportAccessToExcelSheet "sheet1", mExcelFile, "fileinfo", App.Path & "\FileManager.mdb"
MsgBox "Data has been exported to Excel file."
End If
Exit Sub
End Sub
Dim mExcelFile As String
CommonDialog1.Filter = "Excel File|*.xls"
CommonDialog1.ShowSave
mExcelFile = CommonDialog1.FileName
CommonDialog1.FileName = ""
If mExcelFile = "" Then
Exit Sub
Else
If Dir(mExcelFile) <> "" Then
MsgBox mExcelFile & " have"
Else
MsgBox mExcelFile & " not have"
End If
End If
Exit Sub
End Sub
'文件存在返回1,不存在返回0
Dim objSysFile As FileSystemObject
Set objSysFile = CreateObject("scripting.filesystemobject")
If blnFolder Then
If objSysFile.FolderExists(FullPathFileNameOrFolder) Then
intExistFolderOrFile = 1
Else
intExistFolderOrFile = 0
End If
Else
If objSysFile.FileExists(FullPathFileNameOrFolder) Then
intExistFolderOrFile = 1
Else
intExistFolderOrFile = 0
End If
End If
Set objSysFile = Nothing
End FunctionPublic Function SaveFile(SourceFileName As String, DestinationFileName, Optional blnOverwrite As Boolean = True) As String '保存文件使用程序
'将某文件另存为指定文件,如果成功返回另存文件的全路径文件名,否则返回空值。
Dim objSysFile As FileSystemObject
Dim strDestFolder As String
On Error GoTo errout
Set objSysFile = CreateObject("scripting.filesystemobject")
strDestFolder = Left(DestinationFileName, InStrRev(DestinationFileName, "\") - 1)
If Not objSysFile.FileExists(SourceFileName) Then
MsgBox "源文档无法找到,请查找拼写或重新指定!", vbOKOnly + vbInformation, "提示"
Set objSysFile = Nothing
Exit Function
ElseIf Not objSysFile.FolderExists(strDestFolder) Then
If MsgBox("目的文件夹不存在,是否要创建?", vbYesNo + vbQuestion, "提示") = vbYes Then
objSysFile.CreateFolder strDestFolder
Else
Set objSysFile = Nothing
Exit Function
End If
End If
If Not blnOverwrite Then '如果没有明显指定覆盖原有文件,给出提示
If Not objSysFile.FileExists(DestinationFileName) Then
If MsgBox("是否使用文件:" & Chr(13) & SourceFileName & Chr(13) & Chr(13) & "覆盖现有文件:" & DestinationFileName, vbYesNo + vbInformation, "提示") = vbNo Then
Set objSysFile = Nothing
Exit Function
End If
End If
End If
objSysFile.CopyFile SourceFileName, DestinationFileName, True
SaveFile = DestinationFileName
errout: Set objSysFile = NothingEnd Function
'声明excel对象变量
dim oXlapp as excel.application
dim oxlwbook as excel.workbook
dim oxlwsheet as excel.worksheet
'对象赋值(由根到枝叶)
set oxlapp=new excel.application
set oxlwbook=oxlapp.workbooks.add
set oxlwsheet=oxlapp.worksheets.add'工作薄
oxlwsheet.cells(1,1).value=...
...
'保存
oxlwsheet.saveas "指定文件名.xls"
'退出
oxlapp.quit
'恢复环境(由枝叶到根)
set oxlwsheet=nothing
set oxlwbook=nothing
set oxlapp=nothing