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
Re: Gathering Data from a NetWork File
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
Re: Gathering Data from a NetWork File
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
Re: Gathering Data from a NetWork File
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)