VBA code for opening and closing excel file provided not already open

nomissmith

New Member
Joined
Mar 3, 2016
Messages
2
I have two excel spreadsheets that are linked to show data from one in the other. Due to the fact that I am using COUNTIFS, SUMIFS and a MAX(IF) in order to check multiple criteria the worksheet that displays the linked data requires the spreadsheet containing the data to be opened to update. The file does not need to stay open so I have put a macro in to open, save and close the required file. Unfortunately it appears that if the file is already open it will save the opened file to another location and this changes all the data links on the second spreadsheet.

Is there a way of checking whether the file is open and doing the following depending on found condition;

1. If open - display a message saying unable to update as file already open.
2. If closed - complete open, save, close macro

I currently have the macro linked to a button.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

Do you really need to save the file ?
What happens if you just open it, do whatever you need to do with it, and then close it again without saving ?
 

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
I have two excel spreadsheets that are linked to show data from one in the other. Due to the fact that I am using COUNTIFS, SUMIFS and a MAX(IF) in order to check multiple criteria the worksheet that displays the linked data requires the spreadsheet containing the data to be opened to update. The file does not need to stay open so I have put a macro in to open, save and close the required file. Unfortunately it appears that if the file is already open it will save the opened file to another location and this changes all the data links on the second spreadsheet.

Is there a way of checking whether the file is open and doing the following depending on found condition;

1. If open - display a message saying unable to update as file already open.
2. If closed - complete open, save, close macro

I currently have the macro linked to a button.
Hi nomissmith, welcome to the boards.

You can use a combination of the following codes found directly from the Microsoft support pages.

Firstly you will need to add this User Defined Function to a standard module:

Rich (BB code):
Function IsFileOpen(filename As String)
' Defines variables
    Dim filenum As Integer, errnum As Integer




    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.




' Check to see which error occurred.
    Select Case errnum




' No error occurred.
' File is NOT already open by another user.
        Case 0
            IsFileOpen = False




' Error number for "Permission Denied."
' File is already opened by another user.
        Case 70
            IsFileOpen = True




' Another error occurred.
        Case Else
            Error errnum
    End Select
End Function

Once the UDF has been added to your document you can modify this macro to suit your requirements. In effect this macro calls the UDF and tests if a file is already open or not. If the file is open it can give you a message and exit the sub, however if the file is not open then it will do something else. That something else can basically be your existing code. Remember to change the bold red parts to suit the correct filepath / filename. You can put the rest of your code where the purple comment is.

Rich (BB code):
Sub TestFileOpened()


    ' Test to see if the file is open.
    If IsFileOpen("C:\testfolder\Opened.xlsm") Then
        ' Display a message stating the file in use.
        MsgBox "File already in use!"
            Exit Sub
    Else
        ' Display a message stating the file is not in use.
        MsgBox "File not in use!"
        ' Open the file
        Workbooks.Open "C:\testfolder\Opened.xlsm"
            ' The rest of your code goes here
            '
            '
    End If


End Sub
 

nomissmith

New Member
Joined
Mar 3, 2016
Messages
2
Hi nomissmith, welcome to the boards.

You can use a combination of the following codes found directly from the Microsoft support pages.

Firstly you will need to add this User Defined Function to a standard module:

Rich (BB code):
Function IsFileOpen(filename As String)
' Defines variables
    Dim filenum As Integer, errnum As Integer




    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.




' Check to see which error occurred.
    Select Case errnum




' No error occurred.
' File is NOT already open by another user.
        Case 0
            IsFileOpen = False




' Error number for "Permission Denied."
' File is already opened by another user.
        Case 70
            IsFileOpen = True




' Another error occurred.
        Case Else
            Error errnum
    End Select
End Function

Once the UDF has been added to your document you can modify this macro to suit your requirements. In effect this macro calls the UDF and tests if a file is already open or not. If the file is open it can give you a message and exit the sub, however if the file is not open then it will do something else. That something else can basically be your existing code. Remember to change the bold red parts to suit the correct filepath / filename. You can put the rest of your code where the purple comment is.

Rich (BB code):
Sub TestFileOpened()


    ' Test to see if the file is open.
    If IsFileOpen("C:\testfolder\Opened.xlsm") Then
        ' Display a message stating the file in use.
        MsgBox "File already in use!"
            Exit Sub
    Else
        ' Display a message stating the file is not in use.
        MsgBox "File not in use!"
        ' Open the file
        Workbooks.Open "C:\testfolder\Opened.xlsm"
            ' The rest of your code goes here
            '
            '
    End If


End Sub

Thank you so much - it worked perfectly
 

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
Thank you so much - it worked perfectly
Whilst I can't take credit for the code (it was just on the Microsoft support site), I will gladly take credit for my Google-fu :)

Happy to hear you got it working as required, and thanks for the positive feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top