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