How long is the string that points at that UNC path? (Maybe there's a length
limit that's being broken????)

But my guess is that your path is still wrong.

I'd try this:

Dim myPath as string
dim testStr as string
myPath = "\\...." 'that long path
if right(mypath,1) < "\" then
mypath = mypath & "\" 'make sure there's a trailing backslash
end if

teststr = ""
on error resume next
teststr = dir mypath & "nul"
on error goto 0

if teststr = "" then
msgbox "That path wasn't found!"
else
msgbox "it's ok"
end if



TomK76 wrote:

Dave,

Thanks for the reply.

The long path is good, I know becuase I can manually type the formula into
the cell and it works. So I try with a macro. Problem is that the files are
on a server, so using a path that starts with "E:\" won't work because the
server is not mapped as E: on everyon's drive, so I used the \\USPSPS01\.

So I am not sure whats going on...

"TomK76" wrote:

I am attempting to write a macro that will link to a file on a server.
Several portions of the path are variables so that the user can input
specific dates.

The path is quite long:

\\USPSPS01\E-MOMO\...\Line 1\...\[L1 21 2009.xls]1ST SHIFT'!$I$20

If I screwed up the syntax, its because I'm working from memory...

Here's the problem...each time the macro runs, I get a file explorer window
and I have to point to the file. The problem is, I have some 216 links that I
want to write!

If input the formula in the form ='\\USPS.... then I get the data without
pointing, but use the macro to write the formula and I have to point to
it...is there no way to do this in Excel. I know there's a way in Access, but
I am not familiar with Access!

Thanks!

--

Dave Peterson