VBA：自动保存并关闭除宏工作簿之外的所有当前工作簿 [英] VBA: Auto save&close out of all current workbooks except for Macro workbook

问题描述

我正在尝试关闭所有当前打开的工作簿，除了我的宏工作簿，而且.SaveAs是我的路径，但我希望我的路径是我的宏工作簿[D1]中的指定单元格是准确的。我也希望将文件名保存为工作簿中的单元格A1，我正在保存和关闭。现在我被困了我列出了我目前正在使用的代码，而我遇到的这个代码的问题是，它保存为当前选定的工作簿中的单元格A1中的代码与代码当前正在循环的工作簿。我希望这是有道理的。

  Option Explicit 
 Public ThisFile As String 
 Public Path As String 
 
 Sub CloseAndSaveOpenWorkbooks（）
 Dim Wkb As Workbook 
'ThisFile = ActiveWorkbook.Sheets（1）.Range（A1）。值**注释为这段代码是不按预期工作** 
 Path =C：\Users\uuis\Desktop
 
应用程序
 .ScreenUpdating = False 
 
'循环通过工作簿收集
对于每个Wkb在工作簿
 
与Wkb 
 
如果.Name<> ThisWorkbook.Name然后
'如果这本书是只读的
'不保存但关闭
如果不是Wkb.ReadOnly然后
 
 .SaveAs文件名：= （Path&\& ActiveWorkbook.Sheets（1）.Range（A1）。Value&.xls），FileFormat：= xlExcel8 
 
 End If 
 
'我们保存这个工作簿，但是我们不关闭它
'，因为我们将在最后退出Excel 
'关闭这里使应用程序运行，但没有书
 
。关闭
 
结束如果
 
结束
 
下一个Wkb 
 
 
。 ScreenUpdating = True 
 '。退出Excel 
结束
结束子
  

解决方案$ / code $ ActiveWorkbook.Sheets（1）.Range（A1）。值

应该是

  Wkb.Sheets（1）.Range（A1）。价值
  

I'm trying to close all currently open Workbooks except for my Macro workbook, and .SaveAs my path, but I want my path to be a specified cell within my macro workbook [D1] to be precise. I also want the file name to be saved as cell A1 in the Workbook that I'm currently saving and closing out of. Now I'm stuck. I've listed the code that I'm utilizing currently, and the issue I'm running into with this piece of code is that it's saving as the name in cell A1 in the currently selected Workbook vs the Workbook the code is currently cycling on. I hope this makes sense.

            Option Explicit
            Public ThisFile As String
            Public Path As String

            Sub CloseAndSaveOpenWorkbooks()
                Dim Wkb As Workbook
                ' ThisFile = ActiveWorkbook.Sheets(1).Range("A1").Value ** Commented out as this piece of code was not working as intended **
                Path = "C:\Users\uuis\Desktop"

                With Application
                    .ScreenUpdating = False

                     '       Loop through the workbooks collection
                    For Each Wkb In Workbooks

                        With Wkb

                            If .Name <> ThisWorkbook.Name Then
                             '               if the book is read-only
                             '               don't save but close
                            If Not Wkb.ReadOnly Then

                                .SaveAs Filename:=(Path & "\" & ActiveWorkbook.Sheets(1).Range("A1").Value & ".xls"), FileFormat:=xlExcel8

                            End If

                             '               We save this workbook, but we don't close it
                             '               because we will quit Excel at the end,
                             '               Closing here leaves the app running, but no books

                                .Close

                            End If

                        End With

                    Next Wkb


                    .ScreenUpdating = True
                    ' .Quit 'Quit Excel
                End With
            End Sub

解决方案

ActiveWorkbook.Sheets(1).Range("A1").Value

should be

Wkb.Sheets(1).Range("A1").Value

这篇关于VBA：自动保存并关闭除宏工作簿之外的所有当前工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！