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


"ker_01" wrote:

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\52620 10.xls"

Could it be something like this?
fldr = "\\collaboration.net\sites\Documents\5262010.x ls"
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

"ryguy7272" wrote:

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\52620 10.xls"

Could it be something like this?
fldr = "\\collaboration.net\sites\Documents\5262010.x ls"
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''.


"ker_01" wrote:


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


"ryguy7272" wrote:

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


"ker_01" wrote:

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

"ryguy7272" wrote:

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


"ryguy7272" wrote:

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

Im 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.x ls") 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 wasnt 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.


Heres 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 Im 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''.


"ker_01" wrote:

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" wrote:

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.