重新打开最近关闭的Excel实例 [英] Reopening recently closed instances of Excel

问题描述

如果我使用下面的代码关闭当前打开的所有Excel实例，我需要使用什么来重新打开刚刚关闭的所有Excel实例？我知道我将不得不改变下面的一个文件路径，但不知道实际代码应该是什么。

If I use the below code to close all instances of Excel that are currently open what would I need to use to reopen all the instances of Excel that were just closed? I know I'll have to change the below to save a filepath somewhere but just not sure what the actual code should be.

Public Sub CloseAllExcel()
On Error GoTo handler

   Dim xl As Excel.Application
   Dim wb As Excel.Workbook

   Do While xl Is Nothing
      Set xl = GetObject(, "Excel.Application")
      For Each wb In xl.Workbooks
         wb.Save
         wb.Close
      Next
      xl.Quit
      Set xl = Nothing
   Loop
   Exit Sub

handler:
   If Err <> 429 Then 'ActiveX component can't create object
      MsgBox Err.Description, vbInformation
   End If

End Sub

推荐答案

将工作簿的文件路径存储到文本文件中。如果以 False 作为输入运行此宏，则会打开所有最近关闭的文件。 （未测试）

This stores file paths of the workbooks to a text file. If you run this macro with False as the input, this will open all of the recently closed files. (Not tested)

Public Sub CloseAllExcel(Closing As Boolean)
On Error GoTo handler

   Dim xl As Excel.Application
   Dim wb As Excel.Workbook

   Dim strPath As String
   strPath = "C:\path.txt"


   If Close Then

   Dim fso as Object
   Set fso = CreateObject("Scripting.FileSystemObject")

   Dim oFile as Object
   Set oFile = FSO.CreateTextFile(strPath)



   Do While xl Is Nothing
      Set xl = GetObject(, "Excel.Application")
      For Each wb In xl.Workbooks

       oFile.WriteLine Application.ActiveWorkbook.FullName 
         wb.Save
         wb.Close
      Next
       oFile.Close
       Set fso = Nothing
       Set oFile = Nothing
      xl.Quit
      Set xl = Nothing
   Loop

   Exit Sub

   Else
   Dim FileNum As Integer
   Dim DataLine As String

   FileNum = FreeFile()
   Open strPath For Input As #FileNum

     While Not EOF(FileNum)
        Line Input #FileNum, DataLine 
        Workbooks.Open DataLine
     Wend
   Exit Sub
   End If

handler:
   If Err <> 429 Then 'ActiveX component can't create object
      MsgBox Err.Description, vbInformation
   End If

End Sub

这篇关于重新打开最近关闭的Excel实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！