Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
Having a tough time working with a linked file. I created a summary report
that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
Brett,
In your linked work book, have you tried Edit Links Update Values HTH Michael "BretJacobs" wrote in message ... Having a tough time working with a linked file. I created a summary report that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
If the calculation under Tools | Options is already set to automatic, try
going to Edit | Links and make sure Update is selected as Automatic. Also check the Startup Prompt there to make sure it's not selected as Don't display the altert and don't update automatic links. -Simon "BretJacobs" wrote: Having a tough time working with a linked file. I created a summary report that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
Yes I have, and to the other reply, yes the Update dialog box shows when the
file is first opened. If I try "Edit Links Update Values", with the source files unopened, all of the values go to #Ref, and from the "Edit, Links" menu, the status shows "Error: Source not found". Opening the source workbooks then causes all of the references to update, automatically. The source workbooks are Exports from Crystal Reports, could that have anything to do with it? "Michael" wrote: Brett, In your linked work book, have you tried Edit Links Update Values HTH Michael "BretJacobs" wrote in message ... Having a tough time working with a linked file. I created a summary report that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
The export file from Crystal Reports, is it an Excel file? or is it like csv
file and such? I think if it's not an Excel file, it wouldn't recognize the cell structure until opened. -Simon "BretJacobs" wrote: Yes I have, and to the other reply, yes the Update dialog box shows when the file is first opened. If I try "Edit Links Update Values", with the source files unopened, all of the values go to #Ref, and from the "Edit, Links" menu, the status shows "Error: Source not found". Opening the source workbooks then causes all of the references to update, automatically. The source workbooks are Exports from Crystal Reports, could that have anything to do with it? "Michael" wrote: Brett, In your linked work book, have you tried Edit Links Update Values HTH Michael "BretJacobs" wrote in message ... Having a tough time working with a linked file. I created a summary report that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
The export file is an excel worsksheet (the result of a Crystal Reports
export to excel), and it has an .xls extension. "SimonCC" wrote: The export file from Crystal Reports, is it an Excel file? or is it like csv file and such? I think if it's not an Excel file, it wouldn't recognize the cell structure until opened. -Simon "BretJacobs" wrote: Yes I have, and to the other reply, yes the Update dialog box shows when the file is first opened. If I try "Edit Links Update Values", with the source files unopened, all of the values go to #Ref, and from the "Edit, Links" menu, the status shows "Error: Source not found". Opening the source workbooks then causes all of the references to update, automatically. The source workbooks are Exports from Crystal Reports, could that have anything to do with it? "Michael" wrote: Brett, In your linked work book, have you tried Edit Links Update Values HTH Michael "BretJacobs" wrote in message ... Having a tough time working with a linked file. I created a summary report that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
My only guess is that the export file is not the exact proper excel format
even though it has an .xls extension. How about opening one of the exported files, and save it over as type Microsoft Office Excel Workbook with the same name. Then close that export file and open your summary report file to see if it would update. If that doesn't work I don't really know what else to recommend. -Simon "BretJacobs" wrote: The export file is an excel worsksheet (the result of a Crystal Reports export to excel), and it has an .xls extension. "SimonCC" wrote: The export file from Crystal Reports, is it an Excel file? or is it like csv file and such? I think if it's not an Excel file, it wouldn't recognize the cell structure until opened. -Simon "BretJacobs" wrote: Yes I have, and to the other reply, yes the Update dialog box shows when the file is first opened. If I try "Edit Links Update Values", with the source files unopened, all of the values go to #Ref, and from the "Edit, Links" menu, the status shows "Error: Source not found". Opening the source workbooks then causes all of the references to update, automatically. The source workbooks are Exports from Crystal Reports, could that have anything to do with it? "Michael" wrote: Brett, In your linked work book, have you tried Edit Links Update Values HTH Michael "BretJacobs" wrote in message ... Having a tough time working with a linked file. I created a summary report that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
Simon,
Believe it or not, simply opening the exported file then saving it (without renaming or otherwise manipulating anything) yields the expected result. It must be something in the export process. I'll try and discover exactly what the export options are on the Crystal Export, to see if we can nail down the exact problem. Who knew? Unfortunately there are 52 input files, so that's not an option to open them all each time we do an update. Oh well, thanks for your help!!! "SimonCC" wrote: My only guess is that the export file is not the exact proper excel format even though it has an .xls extension. How about opening one of the exported files, and save it over as type Microsoft Office Excel Workbook with the same name. Then close that export file and open your summary report file to see if it would update. If that doesn't work I don't really know what else to recommend. -Simon "BretJacobs" wrote: The export file is an excel worsksheet (the result of a Crystal Reports export to excel), and it has an .xls extension. "SimonCC" wrote: The export file from Crystal Reports, is it an Excel file? or is it like csv file and such? I think if it's not an Excel file, it wouldn't recognize the cell structure until opened. -Simon "BretJacobs" wrote: Yes I have, and to the other reply, yes the Update dialog box shows when the file is first opened. If I try "Edit Links Update Values", with the source files unopened, all of the values go to #Ref, and from the "Edit, Links" menu, the status shows "Error: Source not found". Opening the source workbooks then causes all of the references to update, automatically. The source workbooks are Exports from Crystal Reports, could that have anything to do with it? "Michael" wrote: Brett, In your linked work book, have you tried Edit Links Update Values HTH Michael "BretJacobs" wrote in message ... Having a tough time working with a linked file. I created a summary report that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
I haven't used Crystal Report before so I don't know how exactly the export
process goes. You can try to look for an option where it specifies what version of Excel to export to. If there is such a thing, hopefully your version of Excel is on the list. Otherwise you might have to look for upgrades to your Crystal Report for compatibility to the latest version of Excel (or up to the Excel version you're using). An alternate way is to write a macro that opens and saves each of your many export files. -Simon "BretJacobs" wrote: Simon, Believe it or not, simply opening the exported file then saving it (without renaming or otherwise manipulating anything) yields the expected result. It must be something in the export process. I'll try and discover exactly what the export options are on the Crystal Export, to see if we can nail down the exact problem. Who knew? Unfortunately there are 52 input files, so that's not an option to open them all each time we do an update. Oh well, thanks for your help!!! "SimonCC" wrote: My only guess is that the export file is not the exact proper excel format even though it has an .xls extension. How about opening one of the exported files, and save it over as type Microsoft Office Excel Workbook with the same name. Then close that export file and open your summary report file to see if it would update. If that doesn't work I don't really know what else to recommend. -Simon "BretJacobs" wrote: The export file is an excel worsksheet (the result of a Crystal Reports export to excel), and it has an .xls extension. "SimonCC" wrote: The export file from Crystal Reports, is it an Excel file? or is it like csv file and such? I think if it's not an Excel file, it wouldn't recognize the cell structure until opened. -Simon "BretJacobs" wrote: Yes I have, and to the other reply, yes the Update dialog box shows when the file is first opened. If I try "Edit Links Update Values", with the source files unopened, all of the values go to #Ref, and from the "Edit, Links" menu, the status shows "Error: Source not found". Opening the source workbooks then causes all of the references to update, automatically. The source workbooks are Exports from Crystal Reports, could that have anything to do with it? "Michael" wrote: Brett, In your linked work book, have you tried Edit Links Update Values HTH Michael "BretJacobs" wrote in message ... Having a tough time working with a linked file. I created a summary report that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Links won't update unless files are actually opened
Bret,
I'm working on an "Update Links" issue that sounds similar to what you're doing. It will effectively open each workbook in a designated path (allowing them to update) then close them. Here's the macro code I'm working with: Put this in a general module of a workbook sub UpdateAllLinks() Dim vLinkSources Dim iLinkSource As Integer Dim AnySheet As Worksheet sPath = "C:\MyDummy\" sName = Dir(sPath & "*.xls") do while sName < "" set bk = Workbook.Open(sPath & sName) For Each AnySheet In ActiveWorkbook.Worksheets ActiveWorkbook.Worksheets(AnySheet.Name) _ .Unprotect Password:="mypassword" Next vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(vLinkSources) Then For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources) ActiveWorkbook.UpdateLink _ vLinkSources(iLinkSource), xlExcelLinks Next End If For Each AnySheet In ActiveWorkbook.Worksheets ActiveWorkbook.Worksheets(AnySheet.Name) _ .Protect Password:="mypassword" Next bk.Close Savechanges:=True sName = Dir() Loop End Sub Put a command button on a worksheet in that workbook. Double click on it to get to the code Private Sub CommandButton1_click() UpdateAllLinks End sub NOTE: If you're workbooks aren't password protected, you can do away with the "Unprotect/Protect" portion of the code. I'm still trying to iron-out a couple of problems, but I'll let you know when I get it to work right. HTH "SimonCC" wrote in message ... I haven't used Crystal Report before so I don't know how exactly the export process goes. You can try to look for an option where it specifies what version of Excel to export to. If there is such a thing, hopefully your version of Excel is on the list. Otherwise you might have to look for upgrades to your Crystal Report for compatibility to the latest version of Excel (or up to the Excel version you're using). An alternate way is to write a macro that opens and saves each of your many export files. -Simon "BretJacobs" wrote: Simon, Believe it or not, simply opening the exported file then saving it (without renaming or otherwise manipulating anything) yields the expected result. It must be something in the export process. I'll try and discover exactly what the export options are on the Crystal Export, to see if we can nail down the exact problem. Who knew? Unfortunately there are 52 input files, so that's not an option to open them all each time we do an update. Oh well, thanks for your help!!! "SimonCC" wrote: My only guess is that the export file is not the exact proper excel format even though it has an .xls extension. How about opening one of the exported files, and save it over as type Microsoft Office Excel Workbook with the same name. Then close that export file and open your summary report file to see if it would update. If that doesn't work I don't really know what else to recommend. -Simon "BretJacobs" wrote: The export file is an excel worsksheet (the result of a Crystal Reports export to excel), and it has an .xls extension. "SimonCC" wrote: The export file from Crystal Reports, is it an Excel file? or is it like csv file and such? I think if it's not an Excel file, it wouldn't recognize the cell structure until opened. -Simon "BretJacobs" wrote: Yes I have, and to the other reply, yes the Update dialog box shows when the file is first opened. If I try "Edit Links Update Values", with the source files unopened, all of the values go to #Ref, and from the "Edit, Links" menu, the status shows "Error: Source not found". Opening the source workbooks then causes all of the references to update, automatically. The source workbooks are Exports from Crystal Reports, could that have anything to do with it? "Michael" wrote: Brett, In your linked work book, have you tried Edit Links Update Values HTH Michael "BretJacobs" wrote in message ... Having a tough time working with a linked file. I created a summary report that links to multiple unique separate worksheets. Links will not update (even if all varieties of F9 are tried [shift, ctrl, etc]) unless the individual files are open also. All files are stored on my laptop's hard drive, and are not mapped to any network drive. I know that is NOT the way the links are supposed to work, as I have used links many many times before. However, is there some trick to Office 2003 that I am missing? |
#11
|
|||
|
|||
Dear Bret
This is a common Problem in Excel and I know 2 reasons for that: 1) Whenever you integrate Links into formulas (e.g. Sum('C:\[test.xls]Sheet1A1:A10') you need to open the other excel file for an update. 2) You have too many formulas in your excel file. Sometimes Excel just doesn't calculate although calculation settings are on automatic calculation. The tool below provides you with an extended calculation feature, that fixes that problem: MTools: Excel Add-In that offers an extended calculation in the case that excel doesn't calculate even in automatic calculation mode Regards Marc Last edited by Marc2010 : September 17th 10 at 08:40 AM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Suppress Update Links Message | Excel Discussion (Misc queries) | |||
Startup prompt - Update links | Excel Discussion (Misc queries) | |||
Automatically update links when server names change | Excel Worksheet Functions | |||
Edit Links: make update manual | Excel Discussion (Misc queries) | |||
Update Automatic Links Not Working in 2003 | Excel Discussion (Misc queries) |