Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charts not recognizing source data if original linked data is changed. | Charts and Charting in Excel | |||
Sort pages? | Excel Discussion (Misc queries) | |||
import external data from changing file name | Excel Worksheet Functions | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |