open workbook via hyperlink using vba if already open dont close once finished code

[B-Man]

I have some vba code that opens a workbook, runs some code and closes the workbook.

if I already have the workbook open I don't want it to close the workbook. is there any way to stop it closing the workbook if it was already open?

 

[Muhammad_Usman]

Yes, you can modify your VBA code to check whether the workbook is already open and only close it if it was opened by the code. You can use the `Workbooks` collection to check if the workbook is open. Here's an example of how to do it:

Sub OpenAndCloseWorkbook()
    Dim wb As Workbook
    Dim wbName As String
    Dim wbPath As String


    ' Set the workbook name and path
    wbName = "YourWorkbook.xlsx"
    wbPath = "C:\YourFolderPath\" ' Update with the actual path


    ' Check if the workbook is already open
    On Error Resume Next
    Set wb = Workbooks(wbName)
    On Error GoTo 0


    ' If the workbook is not open, open it
    If wb Is Nothing Then
        Set wb = Workbooks.Open(wbPath & wbName)
    End If


    ' Your code to manipulate the workbook goes here


    ' Close the workbook if it was opened by the code
    If Not wb Is Nothing Then
        If wb.Name = wbName Then
            wb.Close SaveChanges:=False
        End If
    End If
End Sub

```

In this code:

1. It first checks if the workbook is already open using the `Workbooks` collection and `On Error Resume Next`.

2. If the workbook is not already open, it opens it.

3. After you're done with your code to manipulate the workbook, it checks again if the workbook exists and has the expected name before closing it. This way, it only closes the workbook if it was opened by the code.

Make sure to replace `"YourWorkbook.xlsx"` and `"C:\YourFolderPath\"` with the actual file name and path you are working with.

 

[B-Man]

I made a small alteration as I was using a hyperlink to open the file and its tied up in google drive with an absolute pathfile so it works on any of my pcs.
but got it working

the only thing I have a problem with now is since I can close the workbook its not remembering the data to put into the user form textbox when the workbook closes

so I have alot of lines of code like this and if I close the workbook it doesn't show the values but if I show the user form before I close the workbook it hangs in the background.

Giftcards.TextBox1 = Format(wb.Sheets("Giftcards").Range("T2").Value, "$   #,###")