DDE ERROR CODE N/A

Hi back again about the DDE

the formula works on my test files. But it doesn’t seem to work on the many real data files I have.

Screen shot 8 is the dialog box for the formula that displays an N/A error code. I can’t find this code in the list of Version 7 error codes.

When I do a straight link with the following formula I get a returned value – the one I am looking for.
=‘file:///C:/Users/ralph/Documents/TAX FILES/TAX BEYOND DISTRIB.xlsx’#$‘2025’.J40

When I use the DDE formula I get an N/A
=DDE(“soffice”,“C:/Users/ralph/Documents/TAX FILES/TAX BEYOND DISTRIB.xlsx”,“2025.J40”,0)

Any ideas what I have messed up?
thanks in advance - remember i am a real novice so specifics will help me.

C:\fakepath\Screenshot (8).png

Hard to tell, when the content of cell 2025.J40 in your DDE source file is not known. If 2025.J40 is an empty cell, you get #N/A (Not Available, No Answer), since the result of DDE is “nothing”.

the cell is not empty. i can’t figure out how to add a screen shot to the comments but j40 has a numeric value. the surrounding cells in other columns and rows have values and the same N/A comes back even if I change j40 to another row or column.

i can’t figure out how to add a screen shot to the comments

Use edit of your original question and mark as Update or something like that. But I believe your statement so I don’t think that a screenshot would shed more light on your issue.

Did you try to paste C:/Users/ralph/Documents/TAX FILES/TAX BEYOND DISTRIB.xlsx into location bar of your File Explorer and does this open the file using LibreOffice?

I used several other functions to see if the value I want to retrieve is blank. Isblank shows a value in the cell…i used “if function” to retrieve the value, both show there is a value. three parameters - file name - item range Mode. if the file name was incorrect, i’d get an X in the dialogue box, range name ???, Mode??
could the mode be incorrect? does the range name need another descriptor to identify it - 2025.j40 is the tab/cell intersection?

You formula looks OK (and tested a similar on my Windows 10, LibreOffice 7.0.1.2 system). The only thing coming to my mind is an invisible white space character in the sheet name 2025

Hi thanks for staying with it. I checked by trying to rename it. nothing changed. I added a new tab, named it TEST with a $ value in J40. changed the DDE formula to try to retrieve it but the same N/a if it is not the tab or the column, I’ll look for a “phantom workbook”. I will also get another pair of eyes here that knows windows 10 better than I - I hate windows 10 it complicates files, folders and directories.
thanks for your efforts thus far.

Please let me know your exact LibreOffice version as per Help -> About LibreOffice. I’ll install that and may be the problem is specific to the version.

Hi It is version 7.0 link
on properties comparability it shows grayed out Windows 8 with no check mark in box “check for compatibility”. i downloaded this version 8/22 the spreadsheet was created in excel years ago and was migrated to LibreOffice from my windows 8 pc to the windows 10 pc and at the time of migration i was using LibreOffice 6.x

Hi It is version 7.0 link

This is not what I’ve requested stating "*as per Help -> About LibreOffice", because it could be 7.0.0.3 or 7.0.1.2 (and forget the compatibility thing).


> the spreadsheet was created in excel years ago

Did you ever perform a simple DDE test using a new sheet from scratch?
Did you ever perform a DDE test, while running in LibreOffice’s Safe Mode?


**Update** Checked my sample on 7.0.0.3 as well and it worked without any modifications compared to test on LibreOffice 7.0.1.2.

Sorry for the mis-communication about version informatin

Version: 7.0.0.3 (x64)
Build ID: 8061b3e9204bef6b321a21033174034a5e2ea88e
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL

Yes, as I described in the original message, I did create two new test files and the DDE function did work.

No did not do it in safe mode. i will read how to do that and attempt it next.
thanks for all your help

i have it running in safe mode. all spreadheets are open but the DDE is still not doing what it is supposed todo. my plan is to next attempt additional safe mode features. I will also examine the xlsx files to see if there is anything in them that might be causing the issue

I start to assume the problem is due to the origin from an Excel spreadsheet.

I created a two new spreadsheets in my TEST FILES folder with just the DDE formula in one spreadsheet and the VALUE inn J40 in the second spreadsheet and attempted the DDE function just changing the references to the new folder and spreadheet names - same results - the direct link " = " works but DDE does not. I will continue to work with this
thanks for all your help to date - if I crack the mystery I will let you all know

So, in these two spreadsheets, after a series of crashes related to the yellow triangle that says can’t update external links. I look at the "update external links. Now in the spreadheet requesting data there are two links I created one - I guess the F9 manual one which uses " = " to the data and the “DDE” automatic link.
The external link dialog box shows both links - one manual - no update function works on the firs
the external link DDE when highlighted shows automatic and allows me to update - when I update the value appears
When I try to do the same thing with external links in the tax files there are a bunch of links - not sure what they are, but the last ones in the list are the DDE’s i created and auto update shows. when I click it Libre office crashes
the questions - how do I get this update to happen all the time - must I go to external links 2nd do it? and how do i keep libre office from crashing finally i must determine these other links - they are not file names just nbr

Hello,

#N/A Message - General

This just means that a value is not available - see also OASIS Open Document Specification - Part 2 - 5.12 Constant Errors.

image description


`#N/A` **Message - DDE Context**

The message appears in the context of DDE under the following conditions

  • The service given as first argument is wrong (server in terms of DDE)
  • The file given as second argument does not exist ( topic in terms of DDE)
  • The cell given as third argument does not exist ( item in terms of DDE)
  • The cell given as third argument is empty (not to be confused with blank ="")
  • … may be others I’m not aware of.

Ref.: OASIS Open Document Specification - Part 2 - 6.11.2 DDE

From your formula, which looks correct, I’d conclude most likely the source cell is empty.

Hope that helps.

Hi all
my use of the DDE function is now resolved. in one of the answers, “ack” suggested single quote around the target sheet within the workbook before identifying the cell itself. This is true if there are more than one tabs in the workbook. If there is only one tab the single quote is not needed. As I found out in my test files, I had only one tab, no single quote and it worked. in the real sheets, needed single quote. Someone - whomever is responsible should update the documentation to reflect this requirement. Turns out this is 90% of the solution. the other 10% is a mystery. If there is any error in the formula one must enter the entire formula nor just correct the error or it won’t work.
5 of my 6 workbooks now have DDE links and I am a happy camper… thanks for all your help and patience especially “ack”

Looks like you missed to single quote the numeric sheet name in the topic/range argument, "'2025'.J40" should work.

Hi - tried the single quote around ‘2025’ as you suggested though yours looks like a different character set. mine looks like an upside down comma yours looks like a short exclamation point - don’t think it matters.

It does matter. My ' is a real U+0027 APOSTROPHE, if your’s is something different then it doesn’t work. Maybe you got beaten by AutoCorrection, under Tools-> AutoCorrect Options…, tab Localized Options, disable the Single Quotes (and Double Quotes) replacements, or otherwise make sure you enter an actual apostrophe.