[Solved] #REF error in Calc

The Application Programming Interface and the OASIS Open Document Format

[Solved] #REF error in Calc

Postby Manikandan » Sat Nov 05, 2011 6:23 am

Dear all,

I am importing data from calc file to Access db and Exporting data from Access db to Calc file using Visual Basic Application.

While exporting data to calc file,some cells are linked to cells from another calc file.

if the cell from another calc file contains value or formula , it is populated correctly with the formula.

But, If the cell from another calc file contains a reffered cell , it does not populate the value with the formula.
It populates only the formula, the value is populated as #REF. I had to open and set update linked values.

How to solve it?


Any help would be appreciated.

Thanks,

Manikandan.
:crazy:
Last edited by Manikandan on Sat Nov 12, 2011 12:29 pm, edited 3 times in total.
Openoffice.org 2.3, Windows XP
Manikandan
 
Posts: 44
Joined: Tue Jun 21, 2011 11:40 am

Re: #REF ERROR IN CALC

Postby Charlie Young » Sat Nov 05, 2011 6:32 pm

Manikandan wrote:Dear all,

I am importing data from calc file to Access db and Exporting data from Access db to Calc file using Visual Basic Application.

While exporting data to calc file,some cells are linked to cells from another calc file.

if the cell from another calc file contains value or formula , it is populated correctly with the formula.

But, If the cell from another calc file contains a reffered cell , it does not populate the value with the formula.
It populates only the formula, the value is populated as #REF. I had to open and set update linked values.

How to solve it?


Any help would be appreciated.

Thanks,

Manikandan.
:crazy:


Unless someone else has some direct experience with this problem, I think we'll need more details. There are, for example, at least three ways of linking cells between Calc files: interdocument cell references, links to external data through named ranges, and DDE links (the most unreliable, in my experience). Could you perhaps give an example of what's causing the problem, and maybe some VB code?
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: #REF error in Calc

Postby Manikandan » Sun Nov 06, 2011 12:20 pm

Dear charlie Young,

Many thanks for your reply. Actually, I am exporting data from excel to Calc using VB code. The excel file contains links from other excel files. While exporting this sheet to Calc, some links are not updated. they are showing '#REF'.
When i click Edit -> Links -> Update. Then, these cell values are updated.

I want to do this update programmatically using VB Code. If you give an example to update these links automatically by code or VBA or any API reference, It will be helpful.

I am attaching the calc image for your reference.

the link contains the formula as below:-

='file:///F:/1999/MONTH/EXPENSES.xls'#$TODAY.$D$11

It is urgent please.

Regards,

Manikandan
:crazy:
Attachments
Sample.JPG
Sample calc file
Openoffice.org 2.3, Windows XP
Manikandan
 
Posts: 44
Joined: Tue Jun 21, 2011 11:40 am

Re: #REF error in Calc

Postby Charlie Young » Mon Nov 07, 2011 2:54 am

As you've probably noticed, you are dealing with ExternalDocLinks, which irksomely don't have Refresh or Update methods. I presume in your VB you're using loadComponentFromURL. You might try including in the MediaDescriptor a PropertyValue of Name = "UpdateDocMode" and Value = 3 (3 = FULL_UPDATE). That should force the file to reload the links.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: #REF error in Calc

Postby Manikandan » Sat Nov 12, 2011 12:27 pm

Dear All,

I found the solution for this problem. I wrote the below code before saving the calc file. It works fine.

Code: Select all   Expand viewCollapse view
  '#Link-Error
        Dim oLink As Object, Intlinks As Integer
        For Intlinks = 0 To oDoc.Sheetlinks.getCount() - 1
            Set oLink = oDoc.Sheetlinks.getByIndex(Intlinks)
            Call oLink.Refresh
        Next



Regards,

Manikandan
Openoffice.org 2.3, Windows XP
Manikandan
 
Posts: 44
Joined: Tue Jun 21, 2011 11:40 am


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest