I'm not sure I understand, but I bet that the dialog that you're seeing is excel
saying that the formula you just entered refered to a location (either workbook
or worksheet) that didn't exist where you said it did.

The only solution I know is to be more careful--you could even test to see if
the file existed before you plop the formula into a cell.

dim TestStr as string
dim myPath as string
dim myFileName as string

mypath = "\\that long path\" 'with the trailing backslash
myfilename = "l1 21 2009.xls"

teststr = ""
on error resume next
teststr = dir(mypath & myfilename)
on error goto 0

if teststr = "" then
'no file with that name exists at that location
'what should happen
else
'the file exists
'plop the formula into the cell
end if

You could even open the file to see if there was a worksheet inside that
workbook with that name. Then do the plopping of the formula into the cell.



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