Hyperlink to file. Input/Output processing error Reported

I have a speadsheet with hyperlink pointers to relative files,.
Entries and links set up before today all work fine. New ones
added today report an input/output processing error.

What is going on? I assume this is very obvious, but I just
can't figure it out.

Paul

Hi Paul,
Not obvious to me either what are relative files and please
provide examples of what you have, what you want, what
you did and what you see instead.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<[email protected]> wrote in message news:[email protected]...
> I have a speadsheet with hyperlink pointers to relative files,.
> Entries and links set up before today all work fine. New ones
> added today report an input/output processing error.
>
> What is going on? I assume this is very obvious, but I just
> can't figure it out.
>
> Paul

Thanks David,

Excel File is in directory d:\Updated Cemetery Database

Pictures are in a subdirectory d:\Updated Cemetery Database\Brainerd
Image

Image file in question is: 0217.jpg

File is definitely in the directory and if I click on it, it opens and
I see the image.

1. Within the spreadsheet, in a cell, I type the word "Photo".
2. I then right click on "Photo" and select "Hyperlink"
3. In hyperlink, Link to is "Existing file or Web Page", Look in is
"Current Folder", which is set to Updated Cemetery Database. Showing
in the window is the Brainerd Images directory, I select it, then
select the Brainerd Images directory and select the 0217.jpg file
within it.
4. The "Photo" is now an underlined hyperlink. If I click on it, the
following error message appears: Cannot open "D:\Updated Cemetery
Database\0217.jpg"! Input/output failure during processing.
5. Note that the Brainerd Images subdirectory is not part of the
directory name in step 4 above. I don't know why it is missing.

Also, if I try to browse to the file using the browse function in step
3, I get the error message "The folder 'D:\Updated Cemetery
Database\Brainerd%20Images\0217.jpg' isn't accessible. The folder may
be located in an unavailable location, protected with a password, or
the file name contains a / or \.

For some reason, I think the Brainerd Images subdirectory name is
being concatenated with the file name, thus including the "\" symbol
and also creating a non-existent file name.

Any suggestions?

Paul




On Wed, 27 Apr 2005 16:53:25 -0400, "David McRit <[email protected]>
wrote:

>Hi Paul,
>Not obvious to me either what are relative files and please
>provide examples of what you have, what you want, what
>you did and what you see instead.
>---
>HTH,
>David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
>My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
>Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
><[email protected]> wrote in message news:[email protected]...
>> I have a speadsheet with hyperlink pointers to relative files,.
>> Entries and links set up before today all work fine. New ones
>> added today report an input/output processing error.
>>
>> What is going on? I assume this is very obvious, but I just
>> can't figure it out.
>>
>> Paul
>

Hi Paul,

Reference to Absolute and Relative Hyperlinks
see replies by Harlan Grove
http://google.com/groups?threadm=uHC...12@tkmsftngp03
and this one for access hyperlink property
http://google.com/groups?threadm=OjV...68@tkmsftngp04

Also I notice you have spaces in your directory names one of them was replaced by %20 which is a space.I'd suggest testing with
directories without spaces.Some browsers other than IE may not accept backslashes in which case you would have to use
file://d:/Updated%20Cemetary%Data/...
But that is probably not the problem I also tested my use of
HYPERLINK Worksheet Function by removing the hyphen in
my example so it was a space.

What do you see if you right click on the link and use edit hyperlink

You could specify the pathname in a cell on a specific sheet
and then use the HYPERLINK worksheet function
http://www.mvps.org/dmcritchie/excel/sheets.htm

What you are doing is similar to what people producing
catalogs might do.

c:/camera-canong3/25pct/ ( --- also worked with backslashes)
IMG_0028.JPG IMG_0028.JPG =HYPERLINK("file://" & $A$1 & A2,A2)
IMG_0029.JPG IMG_0029.JPG =HYPERLINK("file://" & $A$1 & A3,A3)
also worked without "file://" prefix
IMG_0030.JPG IMG_0030.JPG =HYPERLINK($A$1 & A4,A4)

You can get the pathname of the current workbook into cell A1 with your next directory
A1: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) & "\Brainerd Image\"


or with VBA: [a1] = Application.ActiveWorkbook.Path & "\Brainerd Image\"

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<[email protected]> wrote
> Excel File is in directory d:\Updated Cemetery Database
>
> Pictures are in a subdirectory d:\Updated Cemetery Database\Brainerd
> Image

Hi David,

OK, here is what I have so far.

1. I changed the directory names to removed spaces. No effect.

2. I created a hyperlink the normal way described earlier. When the
link creation window opens, it describes the current directory as
"Updated_Cemetery_Database" and in the window below, I see
the directories in the current directory. I double click on the
"Brainerd_Images" directory to open it and the file list within opens
in the window...but the current directory does not update. It stays
"Updated_Cemetery_Database".
On the file line at the bottom, it reads "Brainerd_Images\0217.jpg"
(after I select the image file).
I select "OK" and the window closes.

The link does not work. Notice that the error message shows the
directory as "Updated_Cemetery_Database\0217.jpg"
It is possible a bug has been introduced to Excel.

Paul



On Wed, 27 Apr 2005 21:47:22 -0400, "David McRitchie"
<[email protected]> wrote:

>Hi Paul,
>
>Reference to Absolute and Relative Hyperlinks
>see replies by Harlan Grove
> http://google.com/groups?threadm=uHC...12@tkmsftngp03
>and this one for access hyperlink property
> http://google.com/groups?threadm=OjV...68@tkmsftngp04
>
>Also I notice you have spaces in your directory names one of them was replaced by %20 which is a space.I'd suggest testing with
>directories without spaces.Some browsers other than IE may not accept backslashes in which case you would have to use
> file://d:/Updated%20Cemetary%Data/...
>But that is probably not the problem I also tested my use of
>HYPERLINK Worksheet Function by removing the hyphen in
>my example so it was a space.
>
>What do you see if you right click on the link and use edit hyperlink
>
>You could specify the pathname in a cell on a specific sheet
>and then use the HYPERLINK worksheet function
> http://www.mvps.org/dmcritchie/excel/sheets.htm
>
>What you are doing is similar to what people producing
>catalogs might do.
>
>c:/camera-canong3/25pct/ ( --- also worked with backslashes)
>IMG_0028.JPG IMG_0028.JPG =HYPERLINK("file://" & $A$1 & A2,A2)
>IMG_0029.JPG IMG_0029.JPG =HYPERLINK("file://" & $A$1 & A3,A3)
>also worked without "file://" prefix
>IMG_0030.JPG IMG_0030.JPG =HYPERLINK($A$1 & A4,A4)
>
>You can get the pathname of the current workbook into cell A1 with your next directory
>A1: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) & "\Brainerd Image\"
>
>
>or with VBA: [a1] = Application.ActiveWorkbook.Path & "\Brainerd Image\"

Hi Paul,
I was asking what you see for the hyperlink in a cell (Ctrl+K)
when you edit the Inserted Hyperlink. etc. not what you
see if you manually go through a directory.

I see that depending on what you choose on the left (Ctrl+K)
the titles on the boxes change: which choice, and
which of the following titles do you have
Type the file or Web page name:
Type the cell address:
Type the cell reference:
Email address:
and what is the content, and what is the text to display value

I was also attempting to show you how you could
manipulate hyperlink by strictly using the HYPERLINK
Worksheet Function. In my example the
pathname with a an ending slash was
in cell A1 the pathname with an ending slash
A1: c:/Updated_Cemetery_Database/Brainerd Image/
in cell A2 and down the filename
A2: img_0028.jpg
A3: img_0029.jpg
in cell B2 and down the formula use fill handle to copy down from B2
B2: =HYPERLINK("file://" & $A$1 & A2,A2)
B2: =HYPERLINK("file://" & $A$1 & A3,A3)


<[email protected]> wrote in message
> OK, here is what I have so far.
>
> 1. I changed the directory names to removed spaces. No effect.
>
> 2. I created a hyperlink the normal way described earlier. When the
> link creation window opens, it describes the current directory as
> "Updated_Cemetery_Database" and in the window below, I see
> the directories in the current directory. I double click on the
> "Brainerd_Images" directory to open it and the file list within opens
> in the window...but the current directory does not update. It stays
> "Updated_Cemetery_Database".
> On the file line at the bottom, it reads "Brainerd_Images\0217.jpg"
> (after I select the image file).
> I select "OK" and the window closes.
>
> The link does not work. Notice that the error message shows the
> directory as "Updated_Cemetery_Database\0217.jpg"
> It is possible a bug has been introduced to Excel.
>
> Paul
>
>
>
> On Wed, 27 Apr 2005 21:47:22 -0400, "David McRitchie"
> <[email protected]> wrote:
>
> >Hi Paul,
> >
> >Reference to Absolute and Relative Hyperlinks
> >see replies by Harlan Grove
> > http://google.com/groups?threadm=uHC...12@tkmsftngp03
> >and this one for access hyperlink property
> > http://google.com/groups?threadm=OjV...68@tkmsftngp04
> >
> >Also I notice you have spaces in your directory names one of them was replaced by %20 which is a space.I'd suggest testing with
> >directories without spaces.Some browsers other than IE may not accept backslashes in which case you would have to use
> > file://d:/Updated%20Cemetary%Data/...
> >But that is probably not the problem I also tested my use of
> >HYPERLINK Worksheet Function by removing the hyphen in
> >my example so it was a space.
> >
> >What do you see if you right click on the link and use edit hyperlink
> >
> >You could specify the pathname in a cell on a specific sheet
> >and then use the HYPERLINK worksheet function
> > http://www.mvps.org/dmcritchie/excel/sheets.htm
> >
> >What you are doing is similar to what people producing
> >catalogs might do.
> >
> >c:/camera-canong3/25pct/ ( --- also worked with backslashes)
> >IMG_0028.JPG IMG_0028.JPG =HYPERLINK("file://" & $A$1 & A2,A2)
> >IMG_0029.JPG IMG_0029.JPG =HYPERLINK("file://" & $A$1 & A3,A3)
> >also worked without "file://" prefix
> >IMG_0030.JPG IMG_0030.JPG =HYPERLINK($A$1 & A4,A4)
> >
> >You can get the pathname of the current workbook into cell A1 with your next directory
> >A1: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) & "\Brainerd Image\"
> >
> >
> >or with VBA: [a1] = Application.ActiveWorkbook.Path & "\Brainerd Image\"
>

David,

Other behaviour I've noted from testing.

From the note prior to this, I created a hyperlink the normal way.
I noticed that the Current Directory field did not change from the
Updated_Cemetery_Database to Brainerd_Images when I opened
the Brainerd_Images directory.

New results:

When creating the link, in the Create or Edit Hyperlink window, if
I back the current directory out to the disk drive level, then tunnel
down to the Brainerd_Images level, the current directory DOES change
to Brainerd_Images. However, I notice that the address field then
reads "Brainerd_Images/0217.jpg". I assume that is because the actual
relative address offset from the current directory of the open
spreadsheet is the one that the hyperlink needs to find the file. I
click on OK and the link does not work. Again, I notice that the
"Brainerd_Images" portion of the address is missing from the error
message, which reads "Cannot Open
"D:\Updated_Cemetery_Database\0217.jpg". Input/Output Error During
Processing!".

Well, if it is ignoring the subdirectory component during the parsing
of the address, of course it can't find the file!.

Lastly, if I manually enter the entire address into the address field
on the Edit/Create Hyperlink window, then click OK. The link works.
If I open the Edit Hyperlink function and close it without doing
anything, the link no longer works.

I really feel that the Create/Edit Hyperlink function has a bug on my
system. All of this worked OK in the past, as I have been adding
links to this database for almost 2 years.

I'm going to uninstall Excell and re-install it and see if the problem
goes away.

Paul








On Wed, 27 Apr 2005 21:47:22 -0400, "David McRitchie"
<[email protected]> wrote:

>Hi Paul,
>
>Reference to Absolute and Relative Hyperlinks
>see replies by Harlan Grove
> http://google.com/groups?threadm=uHC...12@tkmsftngp03
>and this one for access hyperlink property
> http://google.com/groups?threadm=OjV...68@tkmsftngp04
>
>Also I notice you have spaces in your directory names one of them was replaced by %20 which is a space.I'd suggest testing with
>directories without spaces.Some browsers other than IE may not accept backslashes in which case you would have to use
> file://d:/Updated%20Cemetary%Data/...
>But that is probably not the problem I also tested my use of
>HYPERLINK Worksheet Function by removing the hyphen in
>my example so it was a space.
>
>What do you see if you right click on the link and use edit hyperlink
>
>You could specify the pathname in a cell on a specific sheet
>and then use the HYPERLINK worksheet function
> http://www.mvps.org/dmcritchie/excel/sheets.htm
>
>What you are doing is similar to what people producing
>catalogs might do.
>
>c:/camera-canong3/25pct/ ( --- also worked with backslashes)
>IMG_0028.JPG IMG_0028.JPG =HYPERLINK("file://" & $A$1 & A2,A2)
>IMG_0029.JPG IMG_0029.JPG =HYPERLINK("file://" & $A$1 & A3,A3)
>also worked without "file://" prefix
>IMG_0030.JPG IMG_0030.JPG =HYPERLINK($A$1 & A4,A4)
>
>You can get the pathname of the current workbook into cell A1 with your next directory
>A1: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) & "\Brainerd Image\"
>
>
>or with VBA: [a1] = Application.ActiveWorkbook.Path & "\Brainerd Image\"

Hi Paul,
Please don't change the subject within a thread, I put it back
to the original in this reply. I doubt very much if uninstalling
and reinstalling Excel (or Excell for that matter) is going to make
a difference. You could use the HYPERLINK Worksheet
Function as a workaround. What version of Excel do you
have.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<[email protected]> wrote in message news:[email protected]...
> David,
>
> Other behaviour I've noted from testing.
>
> From the note prior to this, I created a hyperlink the normal way.
> I noticed that the Current Directory field did not change from the
> Updated_Cemetery_Database to Brainerd_Images when I opened
> the Brainerd_Images directory.
>
> New results:
>
> When creating the link, in the Create or Edit Hyperlink window, if
> I back the current directory out to the disk drive level, then tunnel
> down to the Brainerd_Images level, the current directory DOES change
> to Brainerd_Images. However, I notice that the address field then
> reads "Brainerd_Images/0217.jpg". I assume that is because the actual
> relative address offset from the current directory of the open
> spreadsheet is the one that the hyperlink needs to find the file. I
> click on OK and the link does not work. Again, I notice that the
> "Brainerd_Images" portion of the address is missing from the error
> message, which reads "Cannot Open
> "D:\Updated_Cemetery_Database\0217.jpg". Input/Output Error During
> Processing!".
>
> Well, if it is ignoring the subdirectory component during the parsing
> of the address, of course it can't find the file!.
>
> Lastly, if I manually enter the entire address into the address field
> on the Edit/Create Hyperlink window, then click OK. The link works.
> If I open the Edit Hyperlink function and close it without doing
> anything, the link no longer works.
>
> I really feel that the Create/Edit Hyperlink function has a bug on my
> system. All of this worked OK in the past, as I have been adding
> links to this database for almost 2 years.
>
> I'm going to uninstall Excell and re-install it and see if the problem
> goes away.
>
> Paul
>
>
>
>
>
>
>
>
> On Wed, 27 Apr 2005 21:47:22 -0400, "David McRitchie"
> <[email protected]> wrote:
>
> >Hi Paul,
> >
> >Reference to Absolute and Relative Hyperlinks
> >see replies by Harlan Grove
> > http://google.com/groups?threadm=uHC...12@tkmsftngp03
> >and this one for access hyperlink property
> > http://google.com/groups?threadm=OjV...68@tkmsftngp04
> >
> >Also I notice you have spaces in your directory names one of them was replaced by %20 which is a space.I'd suggest testing with
> >directories without spaces.Some browsers other than IE may not accept backslashes in which case you would have to use
> > file://d:/Updated%20Cemetary%Data/...
> >But that is probably not the problem I also tested my use of
> >HYPERLINK Worksheet Function by removing the hyphen in
> >my example so it was a space.
> >
> >What do you see if you right click on the link and use edit hyperlink
> >
> >You could specify the pathname in a cell on a specific sheet
> >and then use the HYPERLINK worksheet function
> > http://www.mvps.org/dmcritchie/excel/sheets.htm
> >
> >What you are doing is similar to what people producing
> >catalogs might do.
> >
> >c:/camera-canong3/25pct/ ( --- also worked with backslashes)
> >IMG_0028.JPG IMG_0028.JPG =HYPERLINK("file://" & $A$1 & A2,A2)
> >IMG_0029.JPG IMG_0029.JPG =HYPERLINK("file://" & $A$1 & A3,A3)
> >also worked without "file://" prefix
> >IMG_0030.JPG IMG_0030.JPG =HYPERLINK($A$1 & A4,A4)
> >
> >You can get the pathname of the current workbook into cell A1 with your next directory
> >A1: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) & "\Brainerd Image\"
> >
> >
> >or with VBA: [a1] = Application.ActiveWorkbook.Path & "\Brainerd Image\"
>

Sorry about the subject change. I'm using Office Professional Edition
2003, Excel 2003 (11.6355.6360) SP1

I'm currently putting the links in manually, pasting in the full
directory name and adding each picture file name at the end. This
works as long as I don't use the edit hyperlink function again.
Simply opening that function and closing it corrupts the link.

Paul

On Fri, 29 Apr 2005 13:36:30 -0400, "David McRitchie"
<[email protected]> wrote:

>Hi Paul,
>Please don't change the subject within a thread, I put it back
>to the original in this reply. I doubt very much if uninstalling
>and reinstalling Excel (or Excell for that matter) is going to make
>a difference. You could use the HYPERLINK Worksheet
>Function as a workaround. What version of Excel do you
>have.

Update on manual entry. approach:

I put in a few manually using both the Create and Edit Hyperlink
functions. They work until I save the file and then try them again.
Then, they are broken again.

If I enter the link using the =HYPERLINK function as you suggested,
they work and survive being saved and the file restarted. So, I'll go
with this method, since it seems to work.

I still believe the Create and Edit Hyperlink functions are broken,
at least on my two machines. They both exhibit the same behavior.

Thanks for all the help David.

Paul

Update on manual entry. approach:

I put in a few manually using both the Create and Edit Hyperlink
functions. They work until I save the file and then try them again.
Then, they are broken again.

If I enter the link using the =HYPERLINK function as you suggested,
they work and survive being saved and the file restarted. So, I'll go
with this method, since it seems to work.

I still believe the Create and Edit Hyperlink functions are broken,
at least on my two machines. They both exhibit the same behavior.

Thanks for all the help David.

Paul

Hi Paul,
Actually you repeated your previous update in the thread. I will
assume since the wording is the same that you wanted some
additional reply from me and thought I'd not seen it.

I do see all replies under my replies and under replies to my replies.
Glad the HYPERLINK Worksheet Function method is working for you.

Sorry couldn't help you with your original method. I don't have Excel 2003.
My usual approach is to bypass problems if there is a workaround.
So I figured you would not be looking for additional input from me
after stating what you found/did that worked.

If you want to pursue the original problem then you would have
to create a script (steps on paper) of what one would do to
create directories and files, and then see if it consistently fails
elsewhere. If others experience the same problem, then you
have a reportable problem that can be duplicated and much more
likely to get a fix (2, 3, 5 versions later).
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"[email protected]" <[email protected]> wrote in message news:[email protected]...
> Update on manual entry. approach:
>
> I put in a few manually using both the Create and Edit Hyperlink
> functions. They work until I save the file and then try them again.
> Then, they are broken again.
>
> If I enter the link using the =HYPERLINK function as you suggested,
> they work and survive being saved and the file restarted. So, I'll go
> with this method, since it seems to work.
>
> I still believe the Create and Edit Hyperlink functions are broken,
> at least on my two machines. They both exhibit the same behavior.
>
> Thanks for all the help David.
>
> Paul