'Add reference to closed workbook and be able to open it
In the sub workbook_open
I'm opening 4 workbooks in the background to be able to have references to them without having them "open". (they are not visible)
But I also want to be able to open these workbooks, is that possible? If I double click on one of them it won't open. Not strange perhaps since it's are already open (and not visible). The only way to open the workbooks is to trigger the workbook_close
event which closes them. Then I can open them and see them.
Is there a better way to add a reference to a closed workbook and still be able to open it?
Sub workbook_open()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim FilePath1 As String
Dim FilePath2 As String
Dim FilePath3 As String
Dim FilePath4 As String
FilePath1 = "C:\Excelfiles\Excelfile1.xlsx"
FilePath2 = "C:\Excelfiles\Excelfile2.xlsx"
FilePath3 = "C:\Excelfiles\Excelfile3.xlsx"
FilePath4 = "C:\Excelfiles\Excelfile4.xlsx"
Dim Arbetsbok1 As Workbook
Dim Arbetsbok2 As Workbook
Dim Arbetsbok3 As Workbook
Dim Arbetsbok4 As Workbook
Set Arbetsbok1 = Application.Workbooks.Open(FilePath1, ReadOnly:=True)
Arbetsbok1.Windows(1).Visible = False
Set Arbetsbok2 = Application.Workbooks.Open(FilePath2, ReadOnly:=True)
Arbetsbok2.Windows(1).Visible = False
Set Arbetsbok3 = Application.Workbooks.Open(FilePath3, ReadOnly:=True)
Arbetsbok3.Windows(1).Visible = False
Set Arbetsbok4 = Application.Workbooks.Open(FilePath4, ReadOnly:=True)
Arbetsbok4.Windows(1).Visible = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Sub workbook_close()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim FilePath1 As String
Dim FilePath2 As String
Dim FilePath3 As String
Dim FilePath4 As String
FilePath1 = "Excelfile1.xlsx"
FilePath2 = "Excelfile2.xlsx"
FilePath3 = "Excelfile3.xlsx"
FilePath4 = "Excelfile4.xlsx"
Dim Arbetsbok1 As Workbook
Dim Arbetsbok2 As Workbook
Dim Arbetsbok3 As Workbook
Dim Arbetsbok4 As Workbook
Set Arbetsbok1 = Application.Workbooks(FilePath1)
Set Arbetsbok2 = Application.Workbooks(FilePath2)
Set Arbetsbok3 = Application.Workbooks(FilePath3)
Set Arbetsbok4 = Application.Workbooks(FilePath4)
Arbetsbok1.Close False
Arbetsbok2.Close False
Arbetsbok3.Close False
Arbetsbok4.Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|