VP import "do not update links"

4dv18.2, Mac Mojave

I’m trying to import data from an Excel file using VP IMPORT DOCUMENT. All the numeric fields are defined as “=VLOOKUP…”. I don’t have access to any of the original lookup fields.

I can open the document using Excel, clicking on “don’t update links” in the alert that appears, and all the numeric fields are fine, and I can use AppleScript

tell Excel to open workbook … update links do not update links

In View Pro, however, I don’t see an equivalent option, and while the static text fields look fine, all the numeric fields show as #REF!

Can I use VP IMPORT DOCUMENT? Is there a solution?

Jeremy

Nobody?

Jeremy

Have you tried with updating the links?

I can’t, David. I don’t have any access to the source or any possibility of getting it. The spreadsheets are produced centrally. That’s the issue.

Jeremy

VLOOKUP 4D View Pro
VLOOKUP excel

VLOOKUP exists in both Excel and 4D View Pro and accepts the same arguments, so the conversion from Excel should be correct. I do not understand what are the links that need to be updated in your Excel document? They seems not related to the VLOOKUP function?

François,

The spreadsheets I receive are produced by other people, using data to which I have no access. They contain lots of cells with VLOOKUP formulae. If I open the sheet in Excel, I am asked whether I want to update the links, and when I say no, I get the numbers calculated when there was access to the main data.

I don’t have that option using VP IMPORT DOCUMENT: the links just fail, because there’s no access to the underlying data. I need an option to say “ignore links when importing”, as exists in Excel. Without it, I have to revert to extracting data from the sheets using AppleScript, Excel and LEP. It works, but it’s clunky and visually untidy, as the user sees Excel’s windows flash up and disappear.

Jeremy

Hello Jeremy, as I told you in my previous message, I can’t help you because I have no idea of the links that needs to be updated in your spreadsheet, I guess it’s probably some reference to an external data access like ODBC or a link to another spreadsheet file, those are those references that we need to investigate. Maybe it’s one of the parameters to the VLOOKUP command that performs such action, and that is what we need to investigate. Is it possible that you send us a sample of one of your excel file?

François,

The links look like this:

=VLOOKUP($B$8,‘C:/Users/sm/Dropbox/Accounts information/Expenses Spreadsheets 2020/[CALCULATIONS 2020 (May due June).xls]Member Data’!$A$6:$C$153,2,"false”)

Here’s a Dropbox link to one of the files:

[https://www.dropbox.com/s/vkefoc36f03f7hq/20%2005.xlsx?dl=1](https://www.dropbox.com/s/vkefoc36f03f7hq/20 05.xlsx?dl=1)

Jeremy

Hello Jeremy, just an update to tell you thar our QA department is investigating.

Thanks, François.

Jeremy