Gathering Data from a NetWork File

HI all,
I need your experiance to overcome what I originaly thought was a simple issue.
I need to Pull Data from an open workbook on a network PC, without opening the workbook on my PC. I have the following code which deterimins if the Workbook is Open on the Target Network Drive, but I'm stuck on how to upload / copy in essence Data from it?


Sub Network_File_Open()
Const strFileToOpen As String = "Q:\PathName\Filename1.xlsm"

If IsFileOpen(strFileToOpen) Then
MsgBox strFileToOpen & " is already Open"
Else
MsgBox strFileToOpen & " is not open", vbInformation
End If

End Sub

Function IsFileOpen(strFullPathFileName As String) As Boolean
Dim hdlFile As Long

'Error is generated if you try
'opening a File for ReadWrite lock >> MUST BE OPEN!
On Error GoTo FileIsOpen:
hdlFile = FreeFile
Open strFullPathFileName For Random Access Read Write Lock Read Write As hdlFile
IsFileOpen = False
Close hdlFile
Exit Function

FileIsOpen:
'Someone has it open!
IsFileOpen = True
Close hdlFile

End Function



The above function works fine how do I now pull data from say Sheet7.Range("A1:G75") , Could be smaller or larger though: and then copy it into my workbook. or enter values into an array on my workbook.

Hoping you can assist

Lee

you can try this, i have done some of this in the past with .xls

vb Code:

dim myarr as variant
set wb = getobject(strFileToOpen)
myarr = wb.sheets("sheet7").range("a1:g75")
set wb = nothing
thisworkbook.sheets("sheet1").range("c6").resize(75,7).value = myarr

change destination sheet /range etc to suit
i would not recommend editing the remote workbook, using this method, as i am not sure what the results would be

Many thanks for a quick reply.
I have entered the snippet that you supplied & Dim Wb as Workbook, as variant and as Object

However these all produce an Unspecified Error , Automation Error

any thoughts

Rgds Lee

it may require a unc path, though i do not know why it should, but that is what i was using at the time as the drive was not mapped

it was quite some time ago i messed around with this, and i am not, currently, in a position to be able to test this at all (no network here)