PDA

View Full Version : vba accessing file on server??



lostinvb
03-08-2019, 09:17 AM
I am opening a file on the server. the code works fine when I run in a remote window but I need it to run on my desktop via shortcut. I just get file not found:

I've tried the following as well.

fPath = "\\SERVER1-PC\FileServer\CompanyDocs\My Excel\cost auctions"
fPath = "\\SERVER1-PC\FileServer\CompanyDocs\My Excel\cost auctions"




Private Sub Worksheet_Activate()
'
' openandcopy Macro
'
' Keyboard Shortcut: Ctrl+h
'
Dim fName As String, fPath As String, srch As String, sh As Worksheet

ActiveSheet.Columns("A:K").ClearContents


Set sh = ActiveSheet
fPath = "C:\FileServer\CompanyDocs\My Excel\cost auctions\"
srch = InputBox("Enter search string for filename", "SEARCH")
fName = Dir(fPath & "*.xl*")



Columns("A:N").Select
Selection.ClearContents
Range("A1").Select

Do While fName <> ""
If InStr(fName, srch) > 0 Then
Workbooks.Open fPath & fName
ActiveSheet.UsedRange.Copy sh.Range("A1")
ActiveWorkbook.Close False
Exit Do
End If
fName = Dir
Loop

If InStr(fName, srch) = 0 Then
MsgBox "Invoice not found"
End If

End Sub

Kenneth Hobs
03-08-2019, 04:32 PM
Dir() was not meant to handle UNC paths. Use fso methods. If you don't know what that means, I can show an example. Of course permissions have to be set to allow access.

lostinvb
03-08-2019, 05:27 PM
Yes, please show me an example




Dir() was not meant to handle UNC paths. Use fso methods. If you don't know what that means, I can show an example. Of course permissions have to be set to allow access.

Kenneth Hobs
03-08-2019, 06:10 PM
I did not use UNC paths but regular paths works fine for testing.

Sub FSOTests()
Dim p1$, fso As Object, f As Object
Set fso = CreateObject("Scripting.FileSystemObject")
p1 = "C:\Users\lenovo1\Dropbox (Personal)\_Excel\t"
'p1 = "C:/Users/lenovo1/Dropbox (Personal)/_Excel/t" '/\ makes no difference.

For Each f In fso.getfolder(p1).Files
Debug.Print f.Name
Debug.Print f.Path
Debug.Print fso.fileexists(f.Path)
Next f
End Sub

Since you are using a wildcard search, we could us Like() or check file extensions using fso. e.g.

'jindon, http://www.ozgrid.com/forum/showthread.php?t=157939
Private Function SearchFiles(myDir As String _
, myFileName As String, n As Long, myList() _
, Optional SearchSub As Boolean = False) As Variant
Dim fso As Object, myFolder As Object, myFile As Object
Set fso = CreateObject("Scripting.FileSystemObject")
For Each myFile In fso.GetFolder(myDir).Files
Select Case myFile.Attributes
Case 2, 4, 6, 34
Case Else
If (Not myFile.Name Like "~$*") _
* (myFile.Path & "\" & myFile.Name <> ThisWorkbook.FullName) _
* (UCase(myFile.Name) Like UCase(myFileName)) Then
n = n + 1
ReDim Preserve myList(1 To 2, 1 To n)
myList(1, n) = myDir
myList(2, n) = myFile.Name
End If
End Select
Next
If SearchSub Then
For Each myFolder In fso.GetFolder(myDir).SubFolders
SearchFiles = SearchFiles(myFolder.Path, myFileName, _
n, myList, SearchSub)
Next
End If
SearchFiles = IIf(n > 0, myList, CVErr(xlErrRef))
End Function





If your goal is to get a list of files, here is a quick way. Iterate the elements of array a after running this sort of routine to act on each element of the array. e.g.

Sub TFiles()
Dim a, e, p$
p = """" & "C:\Users\lenovo1\Dropbox (Personal)\_Excel\t\*.xl*" & """"
a = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & p & " /b").stdout.readall, vbCrLf), ".")
For Each e In a
Debug.Print e
Next e
End Sub