☰

电子表格加倍

tag-icon • tag-icon microsoft-excel
电子表格加倍

我在下面的 excel 中创建了一个宏，用于处理相关表单，该表单应该隐藏选项卡，将文件作为 xlsx 复制到桌面，然后重新打开原始 xlsm，取消隐藏选项卡，然后清除表单。我的问题是它会打开第二个 Xlsm 副本并将其也放在桌面上。它已经完美地运行了一个月，要么是我搞砸了，要么是别人搞砸了。下面的脚本中有什么你能看到的吗？谢谢

Sub SaveAs()                ATW spreadsheet
‘SaveAs
‘Hide Tabs– add ATW -authority number then contractor company to file title Save file as XLSX to Desktop. Reopen XLSM, and unhide tabs.
Application.DisplayAlerts = False       ‘ 
    Sheets(Array("Contractor info", "PTW", "DataBase")).Select
    Sheets("DataBase").Activate
    ActiveWindow.SelectedSheets.Visible = False
        ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\U0154179\Desktop\ATW " & ActiveSheet.Range("B21").Value & "-" & ActiveSheet.Range("I3").Value & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\U0154179\Desktop\ATW Template.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Sheets("ATW").Select
    Sheets("Contractor info").Visible = True
    Sheets("ATW").Select
    Sheets("PTW").Visible = True
    Sheets("ATW").Select
    Sheets("DataBase").Visible = True
    Sheets("ATW").Select
    Application.DisplayAlerts = True

答案1

将两个副本保存到桌面的原因是，两个 SaveAs 调用都指向桌面。

此外，您几乎不需要选择任何东西就可以使用它。

快速修改代码可得到以下结果：

Sub SaveAs()
'SaveAs
'Hide Tabs– add ATW -authority number then contractor company to file title Save file as XLSX to Desktop. Reopen XLSM, and unhide tabs.
Dim OrignalFilename As String

Application.DisplayAlerts = False

Sheets(Array("Contractor info", "PTW", "DataBase")).Visible = xlSheetHidden

OriginalFileName = ActiveWorkbook.FullName

ActiveWorkbook.SaveAs _
    Filename:="C:\Users\U0154179\Desktop\ATW " & ActiveSheet.Range("B21").Value & "-" & ActiveSheet.Range("I3").Value & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook

Sheets("Contractor info").Visible = xlSheetVisible
Sheets("PTW").Visible = xlSheetVisible
Sheets("DataBase").Visible = xlSheetVisible

Sheets("ATW").Select

ActiveWorkbook.SaveAs _
    Filename:=OriginalFileName, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled

Application.DisplayAlerts = True

相关内容