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
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