Linked file returning #VALUE! instead of data
 

I have a spreadhseet that is linked to 4 other spreadsheets.

Three of the links appear to work fine, but for the fourth, the linked
cell shows #VALUE! instead of the number from the linked file.

If I open the linked file, then the correct value will appear. But
once I close the linked file, the cell shows #VALUE! again.

As far as I can tell, the file types are similar (All have been saved
as Excel workbooks) and I am using Excel 2003.

Help?

Thanks.


--
bullwinkle55423
------------------------------------------------------------------------
bullwinkle55423's Profile: http://www.excelforum.com/member.php...o&userid=33574
View this thread: http://www.excelforum.com/showthread...hreadid=547076

Linked file returning #VALUE! instead of data
 

I had this problem a while ago what i was told to try copy the "fourth
"(one that does not work) workbook and change the name of it and then
edir links using the EDITLINKSUPDATE VALUES this i was told would
work however i had no luck.
I ended up using this macro

Private Sub Workbook_Open()
Dim ListItems As Variant, i As Integer
Dim SourceWB As Workbook
With Me.ListBox1
.Clear ' remove existing entries from the listbox
' turn screen updating off,
' prevent the user from seeing the source workbook being
opened
Application.ScreenUpdating = False
' open the source workbook as ReadOnly
Set SourceWB =
Workbooks.Open("C:\FolderName\SourceWorkbook.xls", _
False, True)
ListItems = SourceWB.Worksheets(1).Range("B2:B21").Value
' get the values you want
SourceWB.Close False ' close the source workbook without saving
changes
Set SourceWB = Nothing
Application.ScreenUpdating = True
ListItems = Application.WorksheetFunction.Transpose(ListItems)

' convert values to a vertical array
For i = 1 To UBound(ListItems)
.AddItem ListItems(i) ' populate the listbox
Next i
.ListIndex = -1 ' no items selected, set to 0 to select the
first item
End With
End Sub

give both a go you it might work the first way but this way will
definetly work


--
Zygan
------------------------------------------------------------------------
Zygan's Profile: http://www.excelforum.com/member.php...o&userid=34423
View this thread: http://www.excelforum.com/showthread...hreadid=547076