Using Macros to write Links
 

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!

Using Macros to write Links
 

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

Using Macros to write Links
 

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!

Using Macros to write Links
 

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