PDA

View Full Version : Running macros on files on SharePoint Server



Nbisgaard
07-30-2015, 04:16 AM
I am having some trouble with the macro linked at the bottom.
When i use the macro on the path:
\\CPH-FILE-03\Projects$\106121\BYGGEFELT 8\Beregninger\KON\Myndighedsprojekt\Bilag\Lodret_Lastnedføring\

It works flawlessly. It opens up all the files in the folder and retrieves the Data i want.
However if i use the filepath below,
MyPath = "\\project.XXXXX.com\DavWWWRoot\cases\PRJ226\PRJ-2015-00678\Documents\09. Working Documents\Konstruktioner\test\"
The macro doesn't work. The content of the two folders are identical.

At the moment it is unable to find any files and triggers the
MsgBox "Der blev ikke fundet nogle excel-ark i den angivne mappe."
Code.
I have verified that the files are indeed in the folder.
Is it not possible to run macros as these on Sharepoint servers or are there other issues that could be the problem?
I considered that the dots and spaces in the pathname could be an issue, but i don't really have authority to change these so at the moment i can't test this.

Any help is greatly appreciated!

Edit: Some info about the drives. The drive that is working is a physical drive that can be navigated to in this computer
The path that is not working is a Sharepoint Server run by another company. I can use the path in the Windows navigation bar to locate the files in question and i can use other macros
and cross linking between files on the Sharepoint server.


Sub hent()Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim diaFolder As FileDialog


'MyPath = "\\project.XXXXX.com\DavWWWRoot\cases\PRJ226\PRJ-2015-00678\Documents\09. Working Documents\Konstruktioner\test\"
MyPath = "\\CPH-FILE-03\Projects$\106121\BYGGEFELT 8\Beregninger\KON\Myndighedsprojekt\Bilag\Lodret_Lastnedføring\"

Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker) ' Application.ActiveWorkbook.Path
diaFolder.AllowMultiSelect = False
diaFolder.InitialFileName = Application.ActiveWorkbook.Path ' Starter i samme mappe som excel-arket ligger
diaFolder.Show


MyPath = diaFolder.SelectedItems(1) '
End If


If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If


FilesInPath = Dir(MyPath & "*.xls")


If FilesInPath = "" Then
MsgBox "Der blev ikke fundet nogle excel-ark i den angivne mappe."
Exit Sub
End If


Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop


If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)


Workbooks.Open Filename:=MyPath & MyFiles(Fnum), _
UpdateLinks:=True


For i = 1 To ActiveWorkbook.Worksheets.Count
If ActiveWorkbook.Worksheets(i).Name = "Kold" Then
kontrol = 1
End If
Next i


Workbooks(MyFiles(Fnum)).Close SaveChanges:=False

If kontrol > 0 Then
str_link = "='" & MyPath & "[" & MyFiles(Fnum) & "]Kold'!D4"
Cells(10 + Fnum, 2).Formula = str_link


str_link = "='" & MyPath & "[" & MyFiles(Fnum) & "]Kold'!N41"
Cells(10 + Fnum, 1).Formula = str_link


End If


On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0


kontrol = 0


Next Fnum
End If


End Sub

JKwan
07-30-2015, 06:48 AM
a stab in the dark....
check your path (Application.ActiveWorkbook.Path), that may return something that you don't want.
When I connect to SharePoint, I do the below to convert to UNC, maybe that is causing your undesirable error.



FullWorkBookName = Replace(ThisWorkbook.FullName, "http://", "\\")
FullWorkBookName = Replace(FullWorkBookName, "/", "\")

Nbisgaard
08-03-2015, 02:04 AM
Your suggestion got me the path

ht tp://project.XXXXX.com/cases/PRJ224/PRJ-2015-00394/Documents/09.%20Working%20Documents/Konstruktioner/Bilag/Lodret_Lastnedføring/

using the two strings you posted sadly didn't work.
I still got the Run time Error 52
bad file name or number

JKwan
08-03-2015, 05:36 AM
another shot in the dark, try replacing your "%20" into a " " and see if it works

Nbisgaard
08-05-2015, 11:30 PM
Sorry i was slow to get back,
Didn't get me closer unfortunantly, the last suggestion.
I have yet to be able to have someone create me a folder on the sharepoint server with no dots or spaces to check if that could be causing the issues, but hopefully will be able to test it next week.