2008年5月10日

VB.NET與EXCEL整合

EXCEL的ActiveWorkbook.SaveAs如果已有文件存在,如何不要示提示框,直接覆?application.displayalerts=false
'保存
application.displayalerts=true



要達成你的要求應該不難
1.利用Sheets集合
2.每個Sheet要存成一個檔案,則需要將檔案存成Excel 4.0模式
以下是程式碼
Sub test()
Dim rSheet As Object
For Each rSheet In Sheets
rSheet.Select
ChDir "C:\test" '假設檔案要存檔的地方
ActiveWorkbook.SaveAs Filename:=rSheet.Name, FileFormat:=xlExcel4
Next rSheet
End Sub

要在檔案要存檔的地方建立如C:\test的目錄

********************************************
Sub test()
Dim rSheet As Object
Dim OldWorkBook As String, rDir As String
Application.ScreenUpdating = False
On Error Resume Next
ActiveWorkbook.Save
OldWorkBook = ActiveWorkbook.Name
rDir = ActiveWorkbook.Path
For Each rSheet In Sheets
rSheet.Select
ChDir "C:\test"
ActiveWorkbook.SaveAs Filename:=rSheet.Name, FileFormat:=xlExcel4
Next rSheet
ThisWorkbook.Saved = True
Application.OnTime Now + Time("00:00:1"), "closewin"
Workbooks.Open Filename:=rDir & "\" & OldWorkBook
Application.ScreenUpdating = True
End Sub

Sub closewin()
ThisWorkbook.Close
End Sub

0 意見: