What is the alternative for DIR when using VBA to call Sharepoint?

jwandmrsquared
jwandmrsquared used Ask the Experts™
on
I am redesigning a few features on my Access database to be able to function in citrix.  One of those is the repointing from a share drive (local client machine) to using sharepoint mapping.  The script below worked fine from a mapped drive to determine if a file already existed, however, it generates 2 different errors now:

Me.File_Path.DefaultValue = """http://techsp/sites/techven/Shared%20Documents/Product%20Analysis%20Forms%20-%20People%27s%20Court/2010%20Product%20Analysis%20Responses/DB%20Testing/"""

filepath = me.file_path


Dim dlg As Word.Dialog
Options.DefaultFilePath(wdDocumentsPath) = filepath -->This generates error 5417...essentially that filepath is not valid.
If Dir$(filepath & pplcMonth & digyear & "_" & vndr & prodId & ".doc") = "" Then --->if I comment out the options.default command, then error Runtime Error 52, bad file name or number appears.
 doc.SaveAs (filepath & pplcMonth & digyear & "_" & vndr & prodId & ".doc")
'if it exists then prompt for save
 Else
    Set dlg = appWord.Dialogs(wdDialogFileSaveAs)
    dlg.Name = pplcMonth & digyear & "_" & vndr & prodId & ".doc"
    dlg.Show
'if not exists, then save it (not as template)
 End If

rst.MoveNext
docs.Close

What is another way I can check for existing file either from Access or from Word?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ted BouskillSenior Software Developer
Top Expert 2009

Commented:
Sharepoint uses WebDAV so you can use UNC paths to access the files.

Try this instead:
\\techsp\sites\techven\Shared Documents\Product Analysis Forms - People's Court\2010 Product Analysis Responses\DB Testing\

Author

Commented:
I am finding that the issue is no longer Sharepoint but Citrix.  I can run the path you suggest above on my hard drive from LAN share, but it bombs out in citrix.  I am thinking there is a service similar to web services that needs to run on Citrix that would enable the UNC mapping to work.  I may post a new question about that since DIR is now working on sharepoint UNC.
Ted BouskillSenior Software Developer
Top Expert 2009

Commented:
The UNC path won't work in Sharepoint if the 'WebClient' service is disabled.  So if the Citrix box is a server and the WebClient is disabled, WebDAV won't work.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I'll have that checked out Ted...our citrix team is kind of swamped right now, so as soon as we check it out, I'll let you know.  Quick question:  does the file server need to have this service enabled (where the db resides), or is that irrelevant?
Ted BouskillSenior Software Developer
Top Expert 2009

Commented:
Only the client needs the service running.  So if the client is another server (like Citrix) then it needs it activated.  You don't have to change anything on the Sharepoint or DB servers.

Author

Commented:
Ok, sharepoint web services are running.  What is the syntax (UNC is not working) to now get the DIR function to work? Or is there an alternative? The goal is to see if a file already exists, if it doesn't, I want word to save the file; if it does, the user needs to be prompted.  Here is my existing code; the only path variable bombing out is associated to the DIR$ function; the other paths seem to be working.

I have tried:
http:// with %20 for space
http:// with space for space
\\ with spaces
\\ with %20
Leaving off the last / or \



'Check to see if file already exists
Dim testpath As String
testpath = "\\techsp\sites\techven\Shared Documents\Product Analysis Forms - People's Court\2010 Product Analysis Responses\JulTest\"
Dim dlg As Word.Dialog
Options.DefaultFilePath(wdDocumentsPath) = "http://techsp/sites/techven/Shared%20Documents/Product%20Analysis Forms%20-%20People%27s%20Court/2010%20Product%20Analysis%20Responses/JulTest"

If Dir$(testpath & pplcMonth & digyear & "_" & vndr & prodId & ".doc", vbNormal) = "" Then
'Debug.Print Dir$(testpath & pplcMonth & digyear & "_" & vndr & prodId & ".doc", vbDirectory)
doc.SaveAs (filepath & "/" & pplcMonth & digyear & "_" & vndr & prodId & ".doc")
'if it exists then prompt for save
 Else
    Set dlg = appWord.Dialogs(wdDialogFileSaveAs)
    dlg.Name = pplcMonth & digyear & "_" & vndr & prodId & ".doc"
    dlg.Show
'if not exists, then save it (not as template)
 End If

rst.MoveNext
docs.Close

Ted BouskillSenior Software Developer
Top Expert 2009

Commented:
Well from the command line try the UNC path using ''\\techsp\sites\techven\Shared Documents\Product Analysis Forms - People's Court\2010 Product Analysis Responses\JulTest\" if that works then the problem is with VBA.

Author

Commented:
The UNC path works from my machine but NOT from the citrix instance.  Even though I am able to add the sharepoint site from the "My Network Places" but NOT from "My Computer".  I'm thinking the UNC HAS to be able to be listed or mapped under Network Places, but the sharepoint path is not being accepted.  When I try to map to it, I get the "The drive cannot be mapped because no network was found" error message.  Does this mean that Web Client services are not turned on for the Citrix server?
Senior Software Developer
Top Expert 2009
Commented:
OK, if the UNC path doesn't work from the Citrix instance and the WebClient is running on the Citrix instance then something on Citrix is blocking the request.

If you can't access the path using Explorer on the Citrix instance then mapping a drive will likely fail as well.

To be clear I was talking about the Citrix instance in comment http:#33156864  If you want to access the UNC path from a computer then the computer that is making the request MUST be running the WebClient service.

Please turn on the WebClient service on the Citrix instance.

Author

Commented:
Our last citrix expert just left, so as soon as I can get the guy who is doing Citrix now, I will see if the service is turned on.

Author

Commented:
Since we no longer have a citrix admin (trying to hire one now), the fill-in person was unsure how to turn on webservices for the entire farm or for that "instance". We still plan to go citrix, but we have a workaround in place until we get a citrix person.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial