Laurence,
a while back we had a huge thread going back and forth concerning references and copying of sheets. By that time you have introduced support for references containing sheet names that include spaces. However what was still missing is the support for external file references.
Any chance of getting support for external file references anytime (soon)?
Thanks in advance
Regards
Kai
Hi Kai,
In the latest version ,external file reference should be supported. Please download it at www.aspose.com/downloads.
If it still doesn’t work, could you please elaborate your problem? Thank you.
Laurence,
great stuff. I will check it out. I didn’t realize it was there. The history for 3.3.1. did not mention this addition.
Regards
Kai
I didn't mention it because I think it's a complex feature and need more testing. Anyway, you can try it and act as our first on-site user for this feature.
If you find any problem, please let me know. I will fix it ASAP.
Laurence,
finally managed to take the time to test it. Unfortunately the resulting Excel file causes Excel to crash when opened. I have attached the resulting Workbook. However due to the data contained, I simply can’t attach the original Workbook the sheet was copied from.
The sheet in question is the second sheet in the workbook. It should contain references of the sort
=’[SourceWorkbook.xls]Source Sheet with Spaces’!O9
My copy of Excel (Excel 2002) was not able to correctly open the resulting workbook.
Regards
Kai
Hi Kai,
Could you please remove all sensitive data in your template and just keep the reference? This file can help me to find the cause of problem. Thank you.
Laurence,
please find attached fresh copies of the two workbooks in question. The last sheet of Envelope.xls is copied to CheckReport.xls
Please let me know if you need further information
Thanks
Regards
Kai
Hi Kai,
I don’t find any problem in my machine. The following is my sample code:
Excel excel = new Excel();
excel.Open(“d:\Haspa_T9_20050624_Envelope.xls”);
excel.Worksheets[1].Cells[“A19”].Formula = “=’[SourceWorkbook.xls]Source Sheet with Spaces’!O9”;
excel.Save(“d:\book1.xls”);
Please try this attached dll.
Laurence,
unfortunately this does not work.
I might have missed to make myself clear (again) 
This is what I do:
- I populate the Envelope.xls Workbook
- Prepare the last sheet (Check Sheet) in Envelope.xls
- Construct the first Sheet within CheckReport.xls
- Copy over the last sheet of Envelope.xls to a new sheet on CheckReport.xls
The result is the CheckReport.xls in my previous attachment
Maybe it has anything to do with Worksheet.Copy()?
Regards
Kai
Hi Kai,
Yes. I think the Worksheet.Copy method caused this problem. Copying formula over different workbooks is really complex. I don’t know why MS design this feature with so indirect way.
Could you please try to use Cell.Formula to directly populate the first sheet in checkreport.xls?
Laurence,
I am getting close, however there are issues with setting the formulae. I read the formula from the source sheet and patch the formula to include the [] name of the source workbook. Next I set the “patched” formula on the target sheet.
However when I have the following “patched” formulae and set them on the target sheet I get strange results:
“=’[Haspa_T9_20050624_Envelope.xls]Balance Sheet - Assets’!O7”Spaces are stripped off
becomes
“=[Haspa_T9_20050624_Envelope.xls]BALANCE!O7”
“=[Haspa_T9_20050624_Envelope.xls]EK-Ver?nderungsrechnung!C25”the part EK is treated as being a function and the german umlaut isn’t correctly translated (UTF / Unicode / ANSI issue here?)
becomes
“”=[Haspa_T9_20050624_Envelope.xls]EK()-Ver?nderungsrechnung!C25"
Please find attached the latest results.
BTW: Copying conditional formatting between workbooks isn’t supported?
Regards
Kai
Hi Kai,
I found and fixed the first formula bug. Please try this attached fix.
About the second formula, I think you don't set a correct an extern formula. The following is my sample code:
Excel excel = new Excel();
excel.Open("c:\\Haspa_T9_20050624_Envelope.xls");
Cells cells = excel.Worksheets[excel.Worksheets.Count - 1].Cells;
string formula1 = cells["d6"].Formula;
string formula2 = cells["e6"].Formula;
formula1 = formula1.Insert(2, "[Haspa_T9_20050624_Envelope.xls]");
formula2 = formula2.Insert(1, "'[Haspa_T9_20050624_Envelope.xls]");
formula2 = formula2.Insert(formula2.IndexOf("!"), "'");
Excel excel2 = new Excel();
excel2.Worksheets[0].Cells["A1"].Formula = formula1;
excel2.Worksheets[0].Cells["A2"].Formula = formula2;
excel2.Save("c:\\book1.xls");
Laurence,
thanks for the fix. This one does it. You are right, of course, I haven’t noticed the missing single quotes on the false formulae containing the german umlauts. I have updated my “formula patching”.
However, one last question:
- I create the Envelope.xls file on D:<br /> - Next I start creating CheckReport.xls and save it to D:\ as well.
- I reopen it to add the external references
- I save CheckReport.xls back to D:<br />
However when I now open the CheckReport.xls it first gives me the “Update References” dialog which is fine, but then it displays a dialog telling me that one or more links are invalid and need updating. When I do this (manually select Envelope.xls) all references are fine.
Anything I can do about the “Invalid Links” dialog?
Regards
Kai
Why I don’t like external file links is that it depends on current folder settings. If you double-click the checkreport.xls file, the current folder is “My Documents”, not “d:”. You can verfy it by choosing “File->Open”.
If you want to remove this invalid links dialog, please start MS Excel first, then open the checkreport.xls. In the “Update References” dialog, choose “Update”. Then you will find all work fine.
Laurence,
thanks for explaining this to me. Now I know the reason why it happend and have a proper work-around.
If only I could copy over formatting / conditional formatting from the source sheet to the target sheet
Regards
Kai
About the copying conditional formatting issue, I will check this issue. I think it will be available within about one week.
Are all other formattings copied correctly?
Yes, formats are being copied over fine now.
However, sorry about that, but I just noticed that something was broken in the releases between 3.2.2.5 (our current production version) and your latest release:
When you open Result.xls from the attached archive you will notice a lot of images on cell A1 of the first and second sheet. What I do to create Result.xls is to create individual files (FK1 - FK3 - also in archive) and save them. Next I open Envelope.xls and copy some of the FK1/FK2/FK3 sheets over to Envelope.xls. Please note that the source sheets define headers / footers including images.
My asumption is that the images are being copied from header / footer to A1
Regards
Kai
I did just find and fix a small bug related to images. Please try the attached fix.
Following is my sample test code and it works fine:
Aspose.Excel.License license = new Aspose.Excel.License();
license.SetLicense("d:\\aspose.excel.ent.lic");
Excel excel = new Excel();
excel.Open("d:\\envelope.xls");
Excel excel2 = new Excel();
for(int j = 1; j < 4; j ++)
{
excel2.Open("d:\\fk"+ j.ToString() + ".xls");
for(int i = 0; i < excel2.Worksheets.Count; i ++)
{
string name = excel2.Worksheets
.Name;
try
{
excel.Worksheets[name].Copy(excel2.Worksheets);
}
catch
{
}
}
}
excel.Save("d:\\result.xls");
Laurence,
try saving using Excel97 format. Then you will notice the issue. Unfortunately we’re currently forced to deliver Excel97 files but I am discussing this with our business unit.
Regards
Kai
Laurence,
sorry, never mind my last post. Works fine.
Regards
Kai