Using excel to Determine if a File Exists in SharePoint

[ryguy7272]

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.

The regular Dir() doesn't seem to work, or I'm not using it right. Does
anyone know how to do this? If so, please share your code.

Thanks so much!
Ryan---

 

[ker_01]

Were my code snippets posted to your earlier thread on this same topic not
helpful? If you had trouble with them, please post your current code and
where you got stuck implementing those snippets, and I'll see if I can be of
further assistance.

Best,
Keith

 

[ker_01]

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

 

[ryguy7272]

Ok, thanks Keith. So basically, I have some code that pops up an InputBox
and asks for an account number, then asks again for the account number (to
make sure the user input it correctly). Then looks in SP to see if the file
is there (the name of the Excel file is the account number, so it will be
something like 552255.xls).

I’m trying to check for the existence of the file like this:
Sub CheckIfexists()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr =
"\\collaboration.net\sites\Documents\5262010.xls") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If

I figured it had to be something like a UNC path, but wasn’t sure. Nothing
I tried has worked yet. Also, not sure how to handles the https part. Maybe
this:
\\https:\\collaboration . . . etc
Now, all slashes are going the opposite way from the URL slashes.


Here’s another concept that I was experimenting with:
Sub TestIfExists()
Dim sPath As String
sPath = "https://collaboration.net/sites/Documents/5262010.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo,
"Create File?")
If MB = vbYes Then
Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
iTemp = GetAttr(PathName)

Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function

I like this concept quite a bit because it lets a user create a new file on
the fly, if the account does not already exist.

Sub PostToSharepoint()
Dim buildSaveDest As String
Dim striName As String

striName = InputBox(Prompt:="Please enter your client's account
number.", _
Title:="ENTER ACCOUNT NUMBER", Default:="")

buildSaveDest = "https://collaboration.net/sites/Documents/" & striName
& ".xls" ' & ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest
Exit Sub
End Sub

This code works fine . . .

So, where do I go from here? Controlling SP from Excel is a tad bit outside
of my normal routine, but I’m eager to learn this stuff!

BTW, I'm not sure about the stuff you posted the other day; get file path
from file name and get file information. I'm going to fiddle around with
that stuff right now and see how it works.

Thanks!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.

The regular Dir() doesn't seem to work, or I'm not using it right. Does
anyone know how to do this? If so, please share your code.

Thanks so much!
Ryan---

 

[ryguy7272]

Ok, just got a chance to try your code from the other day. I wrapped
everything in a Sub . . . End Sub and added a Next to close out the loop, and
got an error on this line:
For ii = LBound(asd) To UBound(asd)

Run time error 13: type mismatch

Here is the code now:
Sub RunThis()
For ii = LBound(asd) To UBound(asd)

Debug.Print Dir(asd(ii))
'open the file
Application.Workbooks.Open (asd(ii)), False, True

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

fil = asd(ii)

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = Array(Dir(fil), LocName,
RowsOfData, Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z + 1)), fil
'.FoundFiles(i)
End With
End If
End If
Next
End Sub

What am I doing wrong here???

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Ok, thanks Keith. So basically, I have some code that pops up an InputBox
and asks for an account number, then asks again for the account number (to
make sure the user input it correctly). Then looks in SP to see if the file
is there (the name of the Excel file is the account number, so it will be
something like 552255.xls).

I’m trying to check for the existence of the file like this:
Sub CheckIfexists()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr =
"\\collaboration.net\sites\Documents\5262010.xls") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If

I figured it had to be something like a UNC path, but wasn’t sure. Nothing
I tried has worked yet. Also, not sure how to handles the https part. Maybe
this:
\\https:\\collaboration . . . etc
Now, all slashes are going the opposite way from the URL slashes.


Here’s another concept that I was experimenting with:
Sub TestIfExists()
Dim sPath As String
sPath = "https://collaboration.net/sites/Documents/5262010.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo,
"Create File?")
If MB = vbYes Then
Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
iTemp = GetAttr(PathName)

Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function

I like this concept quite a bit because it lets a user create a new file on
the fly, if the account does not already exist.

Sub PostToSharepoint()
Dim buildSaveDest As String
Dim striName As String

striName = InputBox(Prompt:="Please enter your client's account
number.", _
Title:="ENTER ACCOUNT NUMBER", Default:="")

buildSaveDest = "https://collaboration.net/sites/Documents/" & striName
& ".xls" ' & ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest
Exit Sub
End Sub

This code works fine . . .

So, where do I go from here? Controlling SP from Excel is a tad bit outside
of my normal routine, but I’m eager to learn this stuff!

BTW, I'm not sure about the stuff you posted the other day; get file path
from file name and get file information. I'm going to fiddle around with
that stuff right now and see how it works.

Thanks!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.

The regular Dir() doesn't seem to work, or I'm not using it right. Does
anyone know how to do this? If so, please share your code.

Thanks so much!
Ryan---

 

[ker_01]

I had posted snippets that seemed relevant to your query; here is my full sub
for your review; I didn't clean it up, but some of the things I have
commented out might also give you some ideas. Just to keep the train of
thought separate, I'll reply a second time to discuss a potential issue to
watch out for when creating SP files on the fly, which may or may not affect
your implementation.

Option Explicit
Global asd 'variant 1-D array

Sub SrchForFiles()
' Searches the selected folders and sub folders for files with the
specified (xls) extension.
' Data pushed to worksheet called "FileSearch Results".

Dim i As Long, z As Long, Rw As Long, ii As Long
Dim ws As Worksheet, dd As Worksheet
Dim y As Variant
Dim fldr As String, fil As String, FPath As String
Dim LocName As String
Dim FString As String
Dim SummaryWB As Workbook
Dim SummaryWS As Worksheet
Dim Raw_WS As Worksheet
Dim LastRow As Long, FirstRow As Long, RowsOfData As Long
Dim UseData As Boolean
Dim FirstBlankRow As Long

'grab current location for later reference, for where to paste final data
Set SummaryWB = Application.ActiveWorkbook
Set SummaryWS = Application.ActiveWorkbook.ActiveSheet

y = "xls"
fldr = "\\share.ourcompany.com\finance\bcs\"
FirstBlankRow = 2

'Application.ScreenUpdating = False

'asd is a 1-D array of files returned
asd = ListFiles(fldr, True)

' With Application.FileSearch
' .NewSearch
' .LookIn = fldr
' .SearchSubFolders = True
' .Filename = y

Set dd = Excel.ThisWorkbook.Worksheets(3) 'destination for data
Set ws = Excel.ThisWorkbook.Worksheets(1) 'list of files
dd.Activate
dd.Range("A1:AZ1000").Clear
ws.Activate
ws.Range("A1:Z100").Select
Selection.Clear


On Error GoTo 0
' If .Execute() > 0 Then
For ii = LBound(asd) To UBound(asd)
Debug.Print Dir(asd(ii))
'Debug.Print "* " & (asd(ii))
'Next

'For i = 1 To .FoundFiles.Count
'fil = .FoundFiles(i)
fil = asd(ii)
'screen for target file names ("Multi*.xls")
If UCase(Left(Dir(fil), 5)) = "MULTI" Then
'Exclude the template file that has no unique file ID,
and is therefore only 34 chars long
If Len(Dir(fil)) > 34 Then
'Remove the company standard naming convention (left
31 characters of filename)
LocName = Right(Dir(fil), Len(Dir(fil)) - 31)
'Remove the ".xls" from the end of the filename
'Remainder of the filename (LocName) is the unique
file ID
LocName = Left(LocName, Len(LocName) - 4)

'open the file and grab the data
Application.Workbooks.Open (fil), False, True

FirstRow = 4 'standard template setup - first
'available' row for real data
'workaround for variable start row number in the
template
If
UCase(Left((Workbooks(Dir(fil)).Sheets(1).Range("C3").Value), 7)) <>
"EXAMPLE" Then FirstRow = 3
Workbooks(Dir(fil)).Sheets(1).Cells(65536, 1).Select
Selection.End(xlUp).Select
LastRow = Selection.Row

If LastRow <= FirstRow Then 'no data
RowsOfData = 0
'do nothing
Else
RowsOfData = (LastRow - FirstRow) + 1
Set Raw_WS = Excel.ActiveWorkbook.Worksheets(1)
'list of files
Debug.Print
Excel.ActiveWorkbook.Worksheets(1).Name

'copy the data
'-- here--

'copy without clipboard sample code line
'Sheet1.Range("A1:A200").Copy
Destination:=Sheet2.Range("B1")

'
Workbooks(Dir(fil)).Sheets(1).Rows(CStr(FirstRow) & ":" & CStr(LastRow)).Copy
_
' Destination:=

'' Workbooks(Dir(fil)).Sheets(1).Select
'' Rows(CStr(FirstRow) & ":" &
CStr(LastRow)).Select
'' Selection.Copy

Raw_WS.Range("A" & CStr(FirstRow) & ":AO" &
CStr(LastRow)).Copy _
Destination:=dd.Range("A" & CStr(FirstBlankRow))

'' Workbooks(Dir(fil)).Sheets(1).Select
'' Workbooks(Dir(fil)).Sheets(1).Range("A" &
CStr(FirstRow) & ":AO" & CStr(LastRow)).Select
'' SummaryWB.Activate
''' SummaryWS.Select
'' dd.Select
'' Range("A" & CStr(FirstBlankRow)).Select
'' ActiveSheet.Paste
FirstBlankRow = FirstBlankRow + RowsOfData
End If

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil,
"\")(UBound(Split(fil, "\")))) - 1)
'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = _
Array(Dir(fil), LocName, RowsOfData,
Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z +
1)), fil '.FoundFiles(i)
End With
End If
End If

'Workbooks.Close 'Fil
Application.CutCopyMode = False 'Clear Clipboard
Workbooks(Dir(fil)).Close SaveChanges:=False
End If
End If
'Next i
Next ii
'End If
' End With

With ws
Rw = .Cells.Rows.Count
With .[A1:F1]
.Value = [{"Full Name","Location","Rows of Data",
"Kilobytes","Last Modified", "Path"}]
.Font.Underline = xlUnderlineStyleSingle
.EntireColumn.AutoFit
.HorizontalAlignment = xlCenter
End With
.[G1:IV1 ].EntireColumn.Hidden = True
On Error Resume Next
'Range(Cells(Rw, "A").End(3)(2), Cells(Rw, "A")).EntireRow.Hidden =
True
Range(.[A2 ], Cells(Rw, "C")).Sort [A2 ], xlAscending, Header:=xlNo
End With

End Sub


'
' list all the files in a directory
' if NESTEDDIRS = True it lists a whole directory tree
'
' returns a 1-based array containing all the listed files


Function ListFiles(ByVal Path As String, Optional ByVal NestedDirs As
Boolean) _
As String()
Dim fso As New Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fileList As String

' get the starting folder
Set fld = fso.GetFolder(Path)
' let the private subroutine do all the work
fileList = ListFilesPriv(fld, NestedDirs)
' (the first element will be a null string unless the first ";" is
removed)
fileList = Right(fileList, Len(fileList) - 1)
' convert to a string array
ListFiles = Split(fileList, ";")

End Function

' private procedure that returns a file list
' as a comma-delimited list of files

Function ListFilesPriv(ByVal fld As Scripting.Folder, _
ByVal NestedDirs As Boolean) As String
Dim fil As Scripting.File
Dim subfld As Scripting.Folder

' list all the files in this directory
For Each fil In fld.Files
'If UCase(Left(Dir(fil), 5)) = "MULTI" And fil.Type = "Microsoft
Excel Worksheet" Then
If fil.Type = "Microsoft Excel Worksheet" Then
ListFilesPriv = ListFilesPriv & ";" & fil.Path
Debug.Print fil.Path
End If
Next

' if requested, search also subdirectories
If NestedDirs Then
For Each subfld In fld.SubFolders
ListFilesPriv = ListFilesPriv & ListFilesPriv(subfld, NestedDirs)
Next
End If

End Function

Ok, just got a chance to try your code from the other day. I wrapped
everything in a Sub . . . End Sub and added a Next to close out the loop, and
got an error on this line:
For ii = LBound(asd) To UBound(asd)

Run time error 13: type mismatch

Here is the code now:
Sub RunThis()
For ii = LBound(asd) To UBound(asd)

Debug.Print Dir(asd(ii))
'open the file
Application.Workbooks.Open (asd(ii)), False, True

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

fil = asd(ii)

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = Array(Dir(fil), LocName,
RowsOfData, Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z + 1)), fil
'.FoundFiles(i)
End With
End If
End If
Next
End Sub

What am I doing wrong here???

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Ok, thanks Keith. So basically, I have some code that pops up an InputBox
and asks for an account number, then asks again for the account number (to
make sure the user input it correctly). Then looks in SP to see if the file
is there (the name of the Excel file is the account number, so it will be
something like 552255.xls).

I’m trying to check for the existence of the file like this:
Sub CheckIfexists()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr =
"\\collaboration.net\sites\Documents\5262010.xls") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If

I figured it had to be something like a UNC path, but wasn’t sure. Nothing
I tried has worked yet. Also, not sure how to handles the https part. Maybe
this:
\\https:\\collaboration . . . etc
Now, all slashes are going the opposite way from the URL slashes.


Here’s another concept that I was experimenting with:
Sub TestIfExists()
Dim sPath As String
sPath = "https://collaboration.net/sites/Documents/5262010.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo,
"Create File?")
If MB = vbYes Then
Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
iTemp = GetAttr(PathName)

Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function

I like this concept quite a bit because it lets a user create a new file on
the fly, if the account does not already exist.

Sub PostToSharepoint()
Dim buildSaveDest As String
Dim striName As String

striName = InputBox(Prompt:="Please enter your client's account
number.", _
Title:="ENTER ACCOUNT NUMBER", Default:="")

buildSaveDest = "https://collaboration.net/sites/Documents/" & striName
& ".xls" ' & ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest
Exit Sub
End Sub

This code works fine . . .

So, where do I go from here? Controlling SP from Excel is a tad bit outside
of my normal routine, but I’m eager to learn this stuff!

BTW, I'm not sure about the stuff you posted the other day; get file path
from file name and get file information. I'm going to fiddle around with
that stuff right now and see how it works.

Thanks!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

:

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.

The regular Dir() doesn't seem to work, or I'm not using it right. Does
anyone know how to do this? If so, please share your code.

Thanks so much!
Ryan---

 

[ker_01]

....and now, my observations on creating files.

At least how our SP is set up, if you add a file (directly through the SP
interface) without specifying the "global repository" or classification, then
the file is automatically loaded into the first global repository (alpha
order) and is placed under a default classification. It has been a while, but
I think that if you load (or create) a file directly using the LAN path
(using Excel VBA), the same thing will occur... there may be a workaround, if
SP uses custom document properties to identify the file locations (I've never
looked into it).

Best,
Keith

Ok, just got a chance to try your code from the other day. I wrapped
everything in a Sub . . . End Sub and added a Next to close out the loop, and
got an error on this line:
For ii = LBound(asd) To UBound(asd)

Run time error 13: type mismatch

Here is the code now:
Sub RunThis()
For ii = LBound(asd) To UBound(asd)

Debug.Print Dir(asd(ii))
'open the file
Application.Workbooks.Open (asd(ii)), False, True

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

fil = asd(ii)

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = Array(Dir(fil), LocName,
RowsOfData, Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z + 1)), fil
'.FoundFiles(i)
End With
End If
End If
Next
End Sub

What am I doing wrong here???

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Ok, thanks Keith. So basically, I have some code that pops up an InputBox
and asks for an account number, then asks again for the account number (to
make sure the user input it correctly). Then looks in SP to see if the file
is there (the name of the Excel file is the account number, so it will be
something like 552255.xls).

I’m trying to check for the existence of the file like this:
Sub CheckIfexists()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr =
"\\collaboration.net\sites\Documents\5262010.xls") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If

I figured it had to be something like a UNC path, but wasn’t sure. Nothing
I tried has worked yet. Also, not sure how to handles the https part. Maybe
this:
\\https:\\collaboration . . . etc
Now, all slashes are going the opposite way from the URL slashes.


Here’s another concept that I was experimenting with:
Sub TestIfExists()
Dim sPath As String
sPath = "https://collaboration.net/sites/Documents/5262010.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo,
"Create File?")
If MB = vbYes Then
Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
iTemp = GetAttr(PathName)

Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function

I like this concept quite a bit because it lets a user create a new file on
the fly, if the account does not already exist.

Sub PostToSharepoint()
Dim buildSaveDest As String
Dim striName As String

striName = InputBox(Prompt:="Please enter your client's account
number.", _
Title:="ENTER ACCOUNT NUMBER", Default:="")

buildSaveDest = "https://collaboration.net/sites/Documents/" & striName
& ".xls" ' & ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest
Exit Sub
End Sub

This code works fine . . .

So, where do I go from here? Controlling SP from Excel is a tad bit outside
of my normal routine, but I’m eager to learn this stuff!

BTW, I'm not sure about the stuff you posted the other day; get file path
from file name and get file information. I'm going to fiddle around with
that stuff right now and see how it works.

Thanks!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

:

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.

The regular Dir() doesn't seem to work, or I'm not using it right. Does
anyone know how to do this? If so, please share your code.

Thanks so much!
Ryan---

 

[ryguy7272]

Thanks again Keith. I pasted your code into a module, but didn't make much
progress after that. I'm getting an error on this line:
Function ListFilesPriv(ByVal fld As Scripting.Folder, ByVal NestedDirs As
Boolean) As String

Error reads: 'User defined type not defined'

The only other thing I did was change fldr =
"\\share.ourcompany.com\finance\bcs\"
to my actual folder.

This may be a little harder than I initially thought . . .

Is there a reference I need to add?


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

...and now, my observations on creating files.

At least how our SP is set up, if you add a file (directly through the SP
interface) without specifying the "global repository" or classification, then
the file is automatically loaded into the first global repository (alpha
order) and is placed under a default classification. It has been a while, but
I think that if you load (or create) a file directly using the LAN path
(using Excel VBA), the same thing will occur... there may be a workaround, if
SP uses custom document properties to identify the file locations (I've never
looked into it).

Best,
Keith

Ok, just got a chance to try your code from the other day. I wrapped
everything in a Sub . . . End Sub and added a Next to close out the loop, and
got an error on this line:
For ii = LBound(asd) To UBound(asd)

Run time error 13: type mismatch

Here is the code now:
Sub RunThis()
For ii = LBound(asd) To UBound(asd)

Debug.Print Dir(asd(ii))
'open the file
Application.Workbooks.Open (asd(ii)), False, True

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

fil = asd(ii)

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = Array(Dir(fil), LocName,
RowsOfData, Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z + 1)), fil
'.FoundFiles(i)
End With
End If
End If
Next
End Sub

What am I doing wrong here???

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Ok, thanks Keith. So basically, I have some code that pops up an InputBox
and asks for an account number, then asks again for the account number (to
make sure the user input it correctly). Then looks in SP to see if the file
is there (the name of the Excel file is the account number, so it will be
something like 552255.xls).

I’m trying to check for the existence of the file like this:
Sub CheckIfexists()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr =
"\\collaboration.net\sites\Documents\5262010.xls") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If

I figured it had to be something like a UNC path, but wasn’t sure. Nothing
I tried has worked yet. Also, not sure how to handles the https part. Maybe
this:
\\https:\\collaboration . . . etc
Now, all slashes are going the opposite way from the URL slashes.


Here’s another concept that I was experimenting with:
Sub TestIfExists()
Dim sPath As String
sPath = "https://collaboration.net/sites/Documents/5262010.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo,
"Create File?")
If MB = vbYes Then
Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
iTemp = GetAttr(PathName)

Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function

I like this concept quite a bit because it lets a user create a new file on
the fly, if the account does not already exist.

Sub PostToSharepoint()
Dim buildSaveDest As String
Dim striName As String

striName = InputBox(Prompt:="Please enter your client's account
number.", _
Title:="ENTER ACCOUNT NUMBER", Default:="")

buildSaveDest = "https://collaboration.net/sites/Documents/" & striName
& ".xls" ' & ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest
Exit Sub
End Sub

This code works fine . . .

So, where do I go from here? Controlling SP from Excel is a tad bit outside
of my normal routine, but I’m eager to learn this stuff!

BTW, I'm not sure about the stuff you posted the other day; get file path
from file name and get file information. I'm going to fiddle around with
that stuff right now and see how it works.

Thanks!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

:

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.

The regular Dir() doesn't seem to work, or I'm not using it right. Does
anyone know how to do this? If so, please share your code.

Thanks so much!
Ryan---

 

[ker_01]

The references I have checked in this particular workbook are listed below;
I can't confirm offhand which might be needed for this code, vs other parts
of the code in this workbook, but give them a try (maybe add/remove one at a
time, so you can figure out which ones are critical):

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Scripting Runtime

Given the line it stopped on- the function ListFilesPriv has a parameter of
"Scripting.Folder"... so I'd start with the scripting runtime first, that is
probably the culprit.

My apologies for not including these references with my other post, it
totally skipped my mind...

HTH,
Keith

Thanks again Keith. I pasted your code into a module, but didn't make much
progress after that. I'm getting an error on this line:
Function ListFilesPriv(ByVal fld As Scripting.Folder, ByVal NestedDirs As
Boolean) As String

Error reads: 'User defined type not defined'

The only other thing I did was change fldr =
"\\share.ourcompany.com\finance\bcs\"
to my actual folder.

This may be a little harder than I initially thought . . .

Is there a reference I need to add?


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

...and now, my observations on creating files.

At least how our SP is set up, if you add a file (directly through the SP
interface) without specifying the "global repository" or classification, then
the file is automatically loaded into the first global repository (alpha
order) and is placed under a default classification. It has been a while, but
I think that if you load (or create) a file directly using the LAN path
(using Excel VBA), the same thing will occur... there may be a workaround, if
SP uses custom document properties to identify the file locations (I've never
looked into it).

Best,
Keith

Ok, just got a chance to try your code from the other day. I wrapped
everything in a Sub . . . End Sub and added a Next to close out the loop, and
got an error on this line:
For ii = LBound(asd) To UBound(asd)

Run time error 13: type mismatch

Here is the code now:
Sub RunThis()
For ii = LBound(asd) To UBound(asd)

Debug.Print Dir(asd(ii))
'open the file
Application.Workbooks.Open (asd(ii)), False, True

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

fil = asd(ii)

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = Array(Dir(fil), LocName,
RowsOfData, Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z + 1)), fil
'.FoundFiles(i)
End With
End If
End If
Next
End Sub

What am I doing wrong here???

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Ok, thanks Keith. So basically, I have some code that pops up an InputBox
and asks for an account number, then asks again for the account number (to
make sure the user input it correctly). Then looks in SP to see if the file
is there (the name of the Excel file is the account number, so it will be
something like 552255.xls).

I’m trying to check for the existence of the file like this:
Sub CheckIfexists()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr =
"\\collaboration.net\sites\Documents\5262010.xls") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If

I figured it had to be something like a UNC path, but wasn’t sure. Nothing
I tried has worked yet. Also, not sure how to handles the https part. Maybe
this:
\\https:\\collaboration . . . etc
Now, all slashes are going the opposite way from the URL slashes.


Here’s another concept that I was experimenting with:
Sub TestIfExists()
Dim sPath As String
sPath = "https://collaboration.net/sites/Documents/5262010.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo,
"Create File?")
If MB = vbYes Then
Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
iTemp = GetAttr(PathName)

Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function

I like this concept quite a bit because it lets a user create a new file on
the fly, if the account does not already exist.

Sub PostToSharepoint()
Dim buildSaveDest As String
Dim striName As String

striName = InputBox(Prompt:="Please enter your client's account
number.", _
Title:="ENTER ACCOUNT NUMBER", Default:="")

buildSaveDest = "https://collaboration.net/sites/Documents/" & striName
& ".xls" ' & ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest
Exit Sub
End Sub

This code works fine . . .

So, where do I go from here? Controlling SP from Excel is a tad bit outside
of my normal routine, but I’m eager to learn this stuff!

BTW, I'm not sure about the stuff you posted the other day; get file path
from file name and get file information. I'm going to fiddle around with
that stuff right now and see how it works.

Thanks!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

:

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.

The regular Dir() doesn't seem to work, or I'm not using it right. Does
anyone know how to do this? If so, please share your code.

Thanks so much!
Ryan---

 

[ryguy7272]

I was missing a reference to this:
Microsoft Scripting Runtime

Ok, I think I'm getting close now. I F8 through the code and get down to
here in the ListFiles function:
Set fld = fso.GetFolder(Path)

Then, I get a Run time errro 76. Path not found.
fld = Nothing

What is Scripting.Folder?

Also, does this like correct?
fldr = "\\https:\\collaboration.net\sites\Documents\5262010.xls"

Could it be something like this?
fldr = "\\collaboration.net\sites\Documents\5262010.xls"
fldr = "\\collaboration.net/sites/Documents/5262010.xls"

I tried these things, and some other combinations; nothing has worked out so
far.
Actuially, I can't seem to get anything working here. Do you have any
thoughts as to what I could do to get this up and running?

Thanks for all the help so far.
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

The references I have checked in this particular workbook are listed below;
I can't confirm offhand which might be needed for this code, vs other parts
of the code in this workbook, but give them a try (maybe add/remove one at a
time, so you can figure out which ones are critical):

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Scripting Runtime

Given the line it stopped on- the function ListFilesPriv has a parameter of
"Scripting.Folder"... so I'd start with the scripting runtime first, that is
probably the culprit.

My apologies for not including these references with my other post, it
totally skipped my mind...

HTH,
Keith

Thanks again Keith. I pasted your code into a module, but didn't make much
progress after that. I'm getting an error on this line:
Function ListFilesPriv(ByVal fld As Scripting.Folder, ByVal NestedDirs As
Boolean) As String

Error reads: 'User defined type not defined'

The only other thing I did was change fldr =
"\\share.ourcompany.com\finance\bcs\"
to my actual folder.

This may be a little harder than I initially thought . . .

Is there a reference I need to add?


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

...and now, my observations on creating files.

At least how our SP is set up, if you add a file (directly through the SP
interface) without specifying the "global repository" or classification, then
the file is automatically loaded into the first global repository (alpha
order) and is placed under a default classification. It has been a while, but
I think that if you load (or create) a file directly using the LAN path
(using Excel VBA), the same thing will occur... there may be a workaround, if
SP uses custom document properties to identify the file locations (I've never
looked into it).

Best,
Keith

:

Ok, just got a chance to try your code from the other day. I wrapped
everything in a Sub . . . End Sub and added a Next to close out the loop, and
got an error on this line:
For ii = LBound(asd) To UBound(asd)

Run time error 13: type mismatch

Here is the code now:
Sub RunThis()
For ii = LBound(asd) To UBound(asd)

Debug.Print Dir(asd(ii))
'open the file
Application.Workbooks.Open (asd(ii)), False, True

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

fil = asd(ii)

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = Array(Dir(fil), LocName,
RowsOfData, Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z + 1)), fil
'.FoundFiles(i)
End With
End If
End If
Next
End Sub

What am I doing wrong here???

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Ok, thanks Keith. So basically, I have some code that pops up an InputBox
and asks for an account number, then asks again for the account number (to
make sure the user input it correctly). Then looks in SP to see if the file
is there (the name of the Excel file is the account number, so it will be
something like 552255.xls).

I’m trying to check for the existence of the file like this:
Sub CheckIfexists()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr =
"\\collaboration.net\sites\Documents\5262010.xls") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If

I figured it had to be something like a UNC path, but wasn’t sure. Nothing
I tried has worked yet. Also, not sure how to handles the https part. Maybe
this:
\\https:\\collaboration . . . etc
Now, all slashes are going the opposite way from the URL slashes.


Here’s another concept that I was experimenting with:
Sub TestIfExists()
Dim sPath As String
sPath = "https://collaboration.net/sites/Documents/5262010.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo,
"Create File?")
If MB = vbYes Then
Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
iTemp = GetAttr(PathName)

Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function

I like this concept quite a bit because it lets a user create a new file on
the fly, if the account does not already exist.

Sub PostToSharepoint()
Dim buildSaveDest As String
Dim striName As String

striName = InputBox(Prompt:="Please enter your client's account
number.", _
Title:="ENTER ACCOUNT NUMBER", Default:="")

buildSaveDest = "https://collaboration.net/sites/Documents/" & striName
& ".xls" ' & ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest
Exit Sub
End Sub

This code works fine . . .

So, where do I go from here? Controlling SP from Excel is a tad bit outside
of my normal routine, but I’m eager to learn this stuff!

BTW, I'm not sure about the stuff you posted the other day; get file path
from file name and get file information. I'm going to fiddle around with
that stuff right now and see how it works.

Thanks!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

:

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.

The regular Dir() doesn't seem to work, or I'm not using it right. Does
anyone know how to do this? If so, please share your code.

Thanks so much!
Ryan---

 

[ker_01]

My apologies for the delayed response; I was offline for the US holiday
weekend.

Also, does this like correct?
fldr = "\\https:\\collaboration.net\sites\Documents\5262010.xls"

Could it be something like this?
fldr = "\\collaboration.net\sites\Documents\5262010.xls"
fldr = "\\collaboration.net/sites/Documents/5262010.xls"

The latter two seem "better"; I wouldn't expect an http/https in a standard
LAN path.

What path is shown when you manually checked a file for the filepath?

... You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path.

Best,
Keith

I was missing a reference to this:
Microsoft Scripting Runtime

Ok, I think I'm getting close now. I F8 through the code and get down to
here in the ListFiles function:
Set fld = fso.GetFolder(Path)

Then, I get a Run time errro 76. Path not found.
fld = Nothing

What is Scripting.Folder?

Also, does this like correct?
fldr = "\\https:\\collaboration.net\sites\Documents\5262010.xls"

Could it be something like this?
fldr = "\\collaboration.net\sites\Documents\5262010.xls"
fldr = "\\collaboration.net/sites/Documents/5262010.xls"

I tried these things, and some other combinations; nothing has worked out so
far.
Actuially, I can't seem to get anything working here. Do you have any
thoughts as to what I could do to get this up and running?

Thanks for all the help so far.
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

The references I have checked in this particular workbook are listed below;
I can't confirm offhand which might be needed for this code, vs other parts
of the code in this workbook, but give them a try (maybe add/remove one at a
time, so you can figure out which ones are critical):

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Scripting Runtime

Given the line it stopped on- the function ListFilesPriv has a parameter of
"Scripting.Folder"... so I'd start with the scripting runtime first, that is
probably the culprit.

My apologies for not including these references with my other post, it
totally skipped my mind...

HTH,
Keith

Thanks again Keith. I pasted your code into a module, but didn't make much
progress after that. I'm getting an error on this line:
Function ListFilesPriv(ByVal fld As Scripting.Folder, ByVal NestedDirs As
Boolean) As String

Error reads: 'User defined type not defined'

The only other thing I did was change fldr =
"\\share.ourcompany.com\finance\bcs\"
to my actual folder.

This may be a little harder than I initially thought . . .

Is there a reference I need to add?


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

...and now, my observations on creating files.

At least how our SP is set up, if you add a file (directly through the SP
interface) without specifying the "global repository" or classification, then
the file is automatically loaded into the first global repository (alpha
order) and is placed under a default classification. It has been a while, but
I think that if you load (or create) a file directly using the LAN path
(using Excel VBA), the same thing will occur... there may be a workaround, if
SP uses custom document properties to identify the file locations (I've never
looked into it).

Best,
Keith

:

Ok, just got a chance to try your code from the other day. I wrapped
everything in a Sub . . . End Sub and added a Next to close out the loop, and
got an error on this line:
For ii = LBound(asd) To UBound(asd)

Run time error 13: type mismatch

Here is the code now:
Sub RunThis()
For ii = LBound(asd) To UBound(asd)

Debug.Print Dir(asd(ii))
'open the file
Application.Workbooks.Open (asd(ii)), False, True

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

fil = asd(ii)

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = Array(Dir(fil), LocName,
RowsOfData, Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z + 1)), fil
'.FoundFiles(i)
End With
End If
End If
Next
End Sub

What am I doing wrong here???

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Ok, thanks Keith. So basically, I have some code that pops up an InputBox
and asks for an account number, then asks again for the account number (to
make sure the user input it correctly). Then looks in SP to see if the file
is there (the name of the Excel file is the account number, so it will be
something like 552255.xls).

I’m trying to check for the existence of the file like this:
Sub CheckIfexists()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr =
"\\collaboration.net\sites\Documents\5262010.xls") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If

I figured it had to be something like a UNC path, but wasn’t sure. Nothing
I tried has worked yet. Also, not sure how to handles the https part. Maybe
this:
\\https:\\collaboration . . . etc
Now, all slashes are going the opposite way from the URL slashes.


Here’s another concept that I was experimenting with:
Sub TestIfExists()
Dim sPath As String
sPath = "https://collaboration.net/sites/Documents/5262010.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo,
"Create File?")
If MB = vbYes Then
Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
iTemp = GetAttr(PathName)

Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function

I like this concept quite a bit because it lets a user create a new file on
the fly, if the account does not already exist.

Sub PostToSharepoint()
Dim buildSaveDest As String
Dim striName As String

striName = InputBox(Prompt:="Please enter your client's account
number.", _
Title:="ENTER ACCOUNT NUMBER", Default:="")

buildSaveDest = "https://collaboration.net/sites/Documents/" & striName
& ".xls" ' & ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest
Exit Sub
End Sub

This code works fine . . .

So, where do I go from here? Controlling SP from Excel is a tad bit outside
of my normal routine, but I’m eager to learn this stuff!

BTW, I'm not sure about the stuff you posted the other day; get file path
from file name and get file information. I'm going to fiddle around with
that stuff right now and see how it works.

Thanks!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

:

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.

The regular Dir() doesn't seem to work, or I'm not using it right. Does
anyone know how to do this? If so, please share your code.

Thanks so much!
Ryan---

 

[ryguy7272]

Solution below:
Sub TestIfExists()
Dim sPath As String
sPath = " https://....xls "
'Test if directory or file exists
If SharepointFileExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo, "Create File?")
If MB = vbYes Then
'Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function SharepointFileExists(filnam) As Boolean
sstring = Replace(filnam, " ", "%20")
'
SharepointFileExists = False
For Each c In ThisWorkbook.SharedWorkspace.Files
If InStr(1, UCase(c.URL), UCase(sstring)) <> 0 Then SharepointFileExists =
True
Next c

End Function

NOTICE: the file must be saved on SharePoint, and run from there.

Big thanks to Simon Page and Sebastien Mistouflet!!


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

My apologies for the delayed response; I was offline for the US holiday
weekend.

Also, does this like correct?
fldr = "\\https:\\collaboration.net\sites\Documents\5262010.xls"

Could it be something like this?
fldr = "\\collaboration.net\sites\Documents\5262010.xls"
fldr = "\\collaboration.net/sites/Documents/5262010.xls"

The latter two seem "better"; I wouldn't expect an http/https in a standard
LAN path.

What path is shown when you manually checked a file for the filepath?

... You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path.

Best,
Keith

I was missing a reference to this:
Microsoft Scripting Runtime

Ok, I think I'm getting close now. I F8 through the code and get down to
here in the ListFiles function:
Set fld = fso.GetFolder(Path)

Then, I get a Run time errro 76. Path not found.
fld = Nothing

What is Scripting.Folder?

Also, does this like correct?
fldr = "\\https:\\collaboration.net\sites\Documents\5262010.xls"

Could it be something like this?
fldr = "\\collaboration.net\sites\Documents\5262010.xls"
fldr = "\\collaboration.net/sites/Documents/5262010.xls"

I tried these things, and some other combinations; nothing has worked out so
far.
Actuially, I can't seem to get anything working here. Do you have any
thoughts as to what I could do to get this up and running?

Thanks for all the help so far.
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

The references I have checked in this particular workbook are listed below;
I can't confirm offhand which might be needed for this code, vs other parts
of the code in this workbook, but give them a try (maybe add/remove one at a
time, so you can figure out which ones are critical):

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Scripting Runtime

Given the line it stopped on- the function ListFilesPriv has a parameter of
"Scripting.Folder"... so I'd start with the scripting runtime first, that is
probably the culprit.

My apologies for not including these references with my other post, it
totally skipped my mind...

HTH,
Keith


:

Thanks again Keith. I pasted your code into a module, but didn't make much
progress after that. I'm getting an error on this line:
Function ListFilesPriv(ByVal fld As Scripting.Folder, ByVal NestedDirs As
Boolean) As String

Error reads: 'User defined type not defined'

The only other thing I did was change fldr =
"\\share.ourcompany.com\finance\bcs\"
to my actual folder.

This may be a little harder than I initially thought . . .

Is there a reference I need to add?


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

...and now, my observations on creating files.

At least how our SP is set up, if you add a file (directly through the SP
interface) without specifying the "global repository" or classification, then
the file is automatically loaded into the first global repository (alpha
order) and is placed under a default classification. It has been a while, but
I think that if you load (or create) a file directly using the LAN path
(using Excel VBA), the same thing will occur... there may be a workaround, if
SP uses custom document properties to identify the file locations (I've never
looked into it).

Best,
Keith

:

Ok, just got a chance to try your code from the other day. I wrapped
everything in a Sub . . . End Sub and added a Next to close out the loop, and
got an error on this line:
For ii = LBound(asd) To UBound(asd)

Run time error 13: type mismatch

Here is the code now:
Sub RunThis()
For ii = LBound(asd) To UBound(asd)

Debug.Print Dir(asd(ii))
'open the file
Application.Workbooks.Open (asd(ii)), False, True

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

fil = asd(ii)

'Get file path from file name
FPath = Left(fil, Len(fil) - Len(Split(fil, "\")(UBound(Split(fil,
"\")))) - 1)

'Get file information
If Left$(fil, 1) = Left$(fldr, 1) Then
If CBool(Len(Dir(fil))) Then
z = z + 1
ws.Cells(z + 1, 1).Resize(, 6) = Array(Dir(fil), LocName,
RowsOfData, Round((FileLen(fil) / 1000), 0), FileDateTime(fil), FPath)
DoEvents
With ws
.Hyperlinks.Add .Range("A" & CStr(z + 1)), fil
'.FoundFiles(i)
End With
End If
End If
Next
End Sub

What am I doing wrong here???

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Ok, thanks Keith. So basically, I have some code that pops up an InputBox
and asks for an account number, then asks again for the account number (to
make sure the user input it correctly). Then looks in SP to see if the file
is there (the name of the Excel file is the account number, so it will be
something like 552255.xls).

I’m trying to check for the existence of the file like this:
Sub CheckIfexists()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(fldr =
"\\collaboration.net\sites\Documents\5262010.xls") Then
MsgBox "File is there!"
Exit Sub
Else
MsgBox "No File!!"
End If

I figured it had to be something like a UNC path, but wasn’t sure. Nothing
I tried has worked yet. Also, not sure how to handles the https part. Maybe
this:
\\https:\\collaboration . . . etc
Now, all slashes are going the opposite way from the URL slashes.


Here’s another concept that I was experimenting with:
Sub TestIfExists()
Dim sPath As String
sPath = "https://collaboration.net/sites/Documents/5262010.xls"
'Test if directory or file exists
If FileOrDirExists(sPath) Then
MsgBox sPath & " exists!"
Else
MsgBox sPath & " does not exist."
MB = MsgBox("Would you like to create a new file?", vbYesNo,
"Create File?")
If MB = vbYes Then
Call PostToSharepoint
Else
MsgBox "Goodbye!!"
End If
Exit Sub
End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
Dim iTemp As Integer
On Error Resume Next
iTemp = GetAttr(PathName)

Select Case Err.Number
Case Is = 0
FileOrDirExists = True
Case Else
FileOrDirExists = False
End Select
On Error GoTo 0
End Function

I like this concept quite a bit because it lets a user create a new file on
the fly, if the account does not already exist.

Sub PostToSharepoint()
Dim buildSaveDest As String
Dim striName As String

striName = InputBox(Prompt:="Please enter your client's account
number.", _
Title:="ENTER ACCOUNT NUMBER", Default:="")

buildSaveDest = "https://collaboration.net/sites/Documents/" & striName
& ".xls" ' & ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest
Exit Sub
End Sub

This code works fine . . .

So, where do I go from here? Controlling SP from Excel is a tad bit outside
of my normal routine, but I’m eager to learn this stuff!

BTW, I'm not sure about the stuff you posted the other day; get file path
from file name and get file information. I'm going to fiddle around with
that stuff right now and see how it works.

Thanks!!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


:

Hrm, my earlier response to this post seems to not have shown up.

The key difference (and what I was trying to describe in my responses to
your post yesterday) is that unlike HTTP calls to regular websites, you can
access sharepoint files directly, the same way you access a LAN drive- no
need for complicated web code. You just need to know your local sharepoint
filepath. You might be able to find this yourself, if you have the option in
sharepoint to [view/explorer view], then right click a file in explorer view
and check properties- the Location item will show you the path. If explorer
view is disabled for you as a user, just check with your IT department for
the filepath.

If you are just loading/editing files, and not pushing brand new files to
sharepoint, I recommend using the filepath- I think it is a lot easier than
the URL approach. There are some drawbacks to pushing brand new files to
sharepoint, but I imagine those are related to custom file properties or
something like that, and could be worked around. I didn't spend a lot of time
on it when I was working on sharepoint, because I was only working with
existing files.

For my project, I had a master workbook that made a list of every file on a
specific sharepoint site, used criteria to match the names of some of those
files and open them, extracted information into my master workbook, then
closed those files. Worked like a charm. If you have trouble with the code
snippets I posted yesterday, re-post your code where you are still having
trouble, and I'll do my best to help out.

HTH,
Keith

:

I'm trying to find some code that will check if a file exists in SharePoint.
I've looked on the web for a bit; coming up empty here.