- 帖子
- 215
- 主題
- 70
- 精華
- 0
- 積分
- 290
- 點名
- 0
- 作業系統
- window xp
- 軟體版本
- 2007
- 閱讀權限
- 20
- 性別
- 女
- 註冊時間
- 2012-6-27
- 最後登錄
- 2024-1-29
|
本帖最後由 missbb 於 2024-1-7 23:11 編輯
下列CODE, 我將工作表另存新檔, 但當存檔時有相同名稱的檔案, 我選擇不存檔, 程式仍會存檔在ACTIVE WORKBOOK的PATH, 和出現已"已經新增檔案".
我想如果選擇不重覆存檔, 直接退出程式, 顯示訊息"已經取消重覆存檔", 是如何改CODE呢? 謝謝!
 TEST FILE SAVE.zip (18.08 KB)
- Sub saveactivesheet2()
- '目前工作表另存指定位置和檔按名稱
- Application.ScreenUpdating = False
- 'Application.DisplayAlerts = False
- Dim currentworkbook As Workbook
- Dim newworkbook As Workbook
- Dim currentworksheet As Worksheet
- Dim newworkbookname As String
- Dim fPath As String
- Set currentworkbook = ThisWorkbook
- Set currentworksheet = ActiveSheet
- newworkbookname = ActiveSheet.Range("S2").Value
- fPath = ActiveSheet.Range("S3").Value
-
- Set newworkbook = Workbooks.Add
- currentworksheet.Copy before:=newworkbook.Sheets(1)
- Range("A1:G100").Select
- Selection.Copy
- Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
- xlNone, SkipBlanks:=False, Transpose:=False
- Columns("H:Y").Select
- Application.CutCopyMode = False
- Selection.Delete Shift:=xlToLeft
- Range("A1").Select
- ActiveWorkbook.Save
- On Error Resume Next
- newworkbook.SaveAs Filename:=fPath & "\" & newworkbookname
- newworkbook.Close
- 'Since nothing changed on sheet, provide feedback to user
- MsgBox "已經新增檔案"
- End Sub
-
-
|
|
發表於 2024-1-7 23:10
| 
