Count Cells in another File without opening the file

[ManUBlueJay]

I have a table (Table1) in one file called File1 that is linked to the data in another file we will call "File2. The Data is not in a table. It starts in A3, will always be 6 Cols wide, but will have different number of Rows
Is there a way to update the size of table1 in File1 without opening file 2.
This can either be using VBA or a function that counts the rows in File2.

The only way I can manage is opening the file which I would like to avoid.

Cheers in advance

 

[Jeffrey Mahoney]

I used to have some code to read closed workbooks; can't seem to find it right now. If you open a file as read only, it's pretty fast and you can read the file even if somebody else has it open.

Dim MELWB as Worksheet
Set MELWB = Workbooks.Open(FileName:=A, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

 

[ManUBlueJay]

That is an interesting take.
I Don't like to hard code VBA.
Can I name the path in excel Named Ranges and then call the name?

 

[ManUBlueJay]

Also is there a way to check if it is open already

 

[Jeffrey Mahoney]

So, given the code below, you could get a filename from a cell and see if it's open already. Remember, if you're opening a file as read only, it doesn't matter if somebody has it open except they may have made significant changes since opening it.

This tests if a WB is open by you

Function IsWBOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
    On Error Resume Next
    IsWBOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

This tests if a file is open by anybody and locked for read only

'Checks to see if file is open by another user or by current user
Function IsFileOpen(Filename As String) As Integer
    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 = 0

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            If IsWBOpen(GetFileName(Filename)) = True Then
              IsFileOpen = -1
            Else
              IsFileOpen = 1
            End If

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

You can call that code something like this

   Dim E as boolean
      E = IsFileOpen(FilePath)
      If E = False Then Exit Do

Another helpful SUB to return just the path of the string containing both the path and filename

Function GetPath(PathFile As String) As String

  Dim X As Long
  Dim A As String
  Dim BS As Integer
  
  A = PathFile
  For X = 1 To Len(A)
    If Mid(A, X, 1) = "\" Then
      BS = X
    End If
  Next X
  If BS > 0 Then
    GetPath = Left(A, BS)
  Else
    GetPath = A & "\"
  End If
End Function