Accessing a file on a remote computer

[BOB.G]

I am trying to read the contents of an excel file located on another computer.
Could someone please help me to achieve this. I have been looking on the
Internet, but without any good example.
below is what I have got so far....

Thanks in advance


Private Sub Form_Load()
Dim excelApp As Excel.Application
Dim excelWS As Excel.Worksheet


Set excelApp = CreateObject("Excel.Application")
Set excelWB = excelApp.Workbooks.Open("\\ncccurr04\c:\Users\Desktop\documents\staff.xlsx")
Set excelWS = excelWB.Worksheets(1)
excelApp.Visible = True

End Sub

[DataMiser]

Is the drive shared?
If so what is the share name and what is shared?
Are you sure your path is correct, usually the desktop folder would be in a sub folder of users, that sub folder being the users account folder

c:\Users\SomeUser\desktop

If the computer is on your Lan and the location is shared then it is a simple thing but you must use the proper share name.
If the computer is not on your Lan then that is another story.

[BOB.G]

Ok... the drive is not shared and the remote computer is on the same lan.

[DataMiser]

The file must be in a shared location that you have access to from the pc and user account in question.

[BOB.G]

The file must be in a shared location that you have access to from the pc and user account in question.

ok... I'll try this and see if it works....

Thanks

[BOB.G]

Still didn't work.... can someone please help me?

[DataMiser]

What did you actually try?

[BOB.G]

What did you actually try?

I have ensured that the file is in a shared location and that the program have access to it.
I have also run the program with administrator credentials.
Both computers are on the same LAN

Remote file location is set in the following line:-
Set excelWB = excelApp.Workbooks.Open("\\ncccurr04\c:\Users\Desktop\documents\staff.xlsx")

[DataMiser]

That is an odd path. Normally the desktop folder is contained in a sub folder within the users folder.

Of course I can't really say what your problem is at this point as I have no idea what you shared, how you shared it nor what error you are now getting.

Did you shared the drive? Did you share it as C? Or did you share a folder and/or did you give the share a name? What error are you getting now?

[2kaud]

"\\ncccurr04\c:\Users\Desktop\documents\staff.xlsx"

That is not a correct reference to a share. The format to connect via a share using Universal Naming Convention is

\\computername\sharename\<folders and/or filename relative to the share>

See https://support.microsoft.com/en-us/kb/323386

On the computer to which you want to connect, what folder have you shared and what name did you give to this share? What permissions did you set for the share?

[2kaud]

To determine what shares are available for ncccurr04 computer, from a command prompt do

net view \\ncccurr04

You should see a list of shares of type disk. If you don't or if you get an error then there is a problem with the network and/or the shares on ncccurr04.

If an error does occur, first try

net view /domain

That will show the names of all the available domains and workgroups. Then for the domain/workgroup in which computer ncccurr04 is a member (say eg workgroup) then try

net view /domain:workgroup

If that completes successfully, then the issue is with the share. If any of these don't then the issue is a networking one.

[BOB.G]

Thanks for that... all commands ran successfully without any errors but,

because there was a significant time lap in getting back any response, I redesigned the program looking at things from another perspective in an effort to move forward.
So instead of trying to open the excel file on the remote machine Set excelWB = excelApp.Workbooks.Open("\\ncccurr04\c:\Users\Desktop\documents\staff.xlsx")
I am now thinking it might be easier to copy the remote file to the local machine then open it.
So I ended up with the new code below which again doesn't work due to network issues

Private Sub Form_Load()
 Dim FSO
 Dim sFile As String
 Dim sSFolder As String
 Dim sDFolder As String

 'source folder path on remote machine.
 sSFolder = "\\ncccurr04\c:\Users\grossett.adm\Desktop\documents"
 sFile = "staff.xlsx"

 'destination path on local machine.
 sDFolder = "c:\"
 
 'Create Object for File System
 Set FSO = CreateObject("Scripting.FileSystemObject")
 
 'Checking If File Is Located in the Source Folder
 If Not FSO.FileExists(sSFolder & sFile) Then
   MsgBox "Specified File Not Found in Source Folder", vbInformation, "Not Found"
 'Copying If the Same File is Not Located in the Destination Folder
 ElseIf Not FSO.FileExists(sDFolder & sFile) Then
 
 FSO.CopyFile (sSFolder & sFile), sDFolder, True
  MsgBox "Specified File Copied to Destination Folder Successfully", vbInformation, "Done!"
 Else
  MsgBox "Specified File Already Exists In The Destination Folder", vbExclamation, "File Already Exists"
 End If
 
End Sub

This display the following message.... Specified File Not Found in Source Folder even though the file is there.

[2kaud]

sSFolder = "\\ncccurr04\c:\Users\grossett.adm\Desktop\documents"

As I explained in post #10, that is not a correct path reference to a share on a remote computer. As all the commands worked OK, what is the display from

net view \\ncccurr04

[BOB.G]

Yes... I kinda figure something wrong with my/file path...(thanks!). First thing first,

When I executed the command net view \\ncccurr04 the following was displayed

Share name Type Used as Comment
------------------------------------------------
documents Disk
Users Disk
The command completed successfully

When I executed the command net view /domain the following was displayed
NEWMANCC
WORKGROUP
ZPRESENT
The command completed successfully

When I executed the command net view /domain:workgroup the following was displayed

Server Name Remark
------------------------------------------------
\\ITTECHLENOVO
\\M46 M46
The command completed successfully

From these results... can you explain how to resolve my problem andor what I am doing wrong?

Thanks

[2kaud]

For the computer \\ncccurr04 there are 2 shares present documents and Users. From this I would suggest

sSFolder = "\\ncccurr04\Users\grossett.adm\Desktop\documents"

However, depending upon the location to where the share documents points, you may be able to have

sSFolder = "\\ncccurr04\documents"

However, the problem you then might have are the permissions for the folder/file you want to access. These will need to allow read access to these folders/files on computer ncccurr04 from the user/computer you are trying to access from.