Excel formula link update

[maniootek]

I am looking for a way to update cell with formula which contains link to value in other sheet.

Let me explain with example code:

#include <Excel.au3>

ExcelLinksExample()

Func ExcelLinksExample()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook = _Excel_BookNew($oExcel)
    Local $aData[][] = [ ["Petrol", "Price"], ["Gasoline", 0.624], ["Diesel", 0.6], ["LPG", 0.423], ["Electricity", 0.037], ["Natural gas", 0.045] ]
;~  _ArrayDisplay($aData, "$aData")
    _Excel_RangeWrite($oWorkBook, $oWorkBook.Activesheet, $aData)
    Local $oWorksheet = _Excel_SheetAdd($oWorkBook)
    _Excel_RangeWrite($oWorkBook, $oWorksheet, "=VLOOKUP(""Gasoline"";'PetrolPriceSheet'!A2:B6;2;FALSE)", "A1", False)
;~  _Excel_RangeWrite($oWorkBook, $oWorksheet, "=WYSZUKAJ.PIONOWO(""Gasoline"";'PetrolPriceSheet'!A2:B6;2;FAŁSZ)", "A1", False) ;polish version
    $oWorkbook.Sheets(2).Name = 'PetrolPriceSheet'
EndFunc

This is the result:

A1 formula value in sheet 'Arkusz2' has error "#N/A!" because formula contains the not found sheet name. When I edit this formula (F2) and hit manually ENTER key, the fromula will be updated and start to work.

I am looking for way to do it programatically.

[Nine]

Naming the sheet before you refer to it should solve your issue...

[maniootek]

Yes i know, but anyway. I am looking for build in function to fix it. Something like LinkUpdate

[seadoggie01]

You can re-apply the formula that is in the cell and it will resolve itself

Local $sFormula = _Excel_RangeRead($oWorkBook, $oWorkSheet, "A1", 2) ; Note: 2 means get the formula
_Excel_RangeWrite($oWorkBook, $oWorkSheet, $sFormula, "A1")

[jugador]

@mandriospo When coding you can't put line haphazardly
have to maintained order for code to run...... 

#include <Excel.au3>
#include <Array.au3>

__Method1()
Func __Method1()
    Local $o_Excel = _Excel_Open()
    If @error Then Exit

    Local $o_Workbook = _Excel_BookNew($o_Excel, 1)
    If @error Then
        _Excel_Close($o_Excel)
        Exit
    EndIf

    Local $o_Data[][] = [["Petrol", "Price"], ["Gasoline", 0.624], ["Diesel", 0.6], ["LPG", 0.423], ["Electricity", 0.037], ["Natural gas", 0.045]]
    $o_Workbook.Sheets(1).Name = 'PetrolPriceSheet'
    _Excel_RangeWrite($o_Workbook, 'PetrolPriceSheet', $o_Data)

    Local $o_WSheet2 = _Excel_SheetAdd($o_Workbook, Default, False, 1, "Arkusz2")
    _Excel_RangeWrite($o_Workbook, $o_WSheet2, '=VLOOKUP(' & '"LPG"' & ',PetrolPriceSheet!A2:B6, 2,0)', "A1")

    MsgBox(0, "", "Click to continue.....")
    _Excel_BookClose($o_Workbook, False)
    _Excel_Close($o_Excel)
    Exit
EndFunc

 

Edited March 12, 2022 by jugador

[maniootek]

This is still not what I am looking for. I am looking for a way to update cell. My original problem needs it. This is only quick example.

I found this method Workbook.UpdateLink

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.updatelink

but the code doesn't work

#include <Excel.au3>
#include <Array.au3>

ExcelLinksExample()

Func ExcelLinksExample()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook = _Excel_BookNew($oExcel)

    Local $aData[][] = [ ["Petrol", "Price"], ["Gasoline", 0.624], ["Diesel", 0.6], ["LPG", 0.423], ["Electricity", 0.037], ["Natural gas", 0.045] ]
;~  _ArrayDisplay($aData, "$aData")
    Local $oPetrolPriceWorksheet = $oWorkBook.Activesheet
    _Excel_RangeWrite($oWorkBook, $oPetrolPriceWorksheet, $aData)
    Local $oNewlyAddedWorksheet = _Excel_SheetAdd($oWorkBook)

    Local $PetrolPriceWorksheetName = 'PetrolPriceSheet'
;~  _Excel_RangeWrite($oWorkBook, $oNewlyAddedWorksheet, '=WYSZUKAJ.PIONOWO("' & $aData[1][0] & '";' & "'" & $PetrolPriceWorksheetName & "'!A2:B6;2;0)", "A1", False) ;polish version
    _Excel_RangeWrite($oWorkBook, $oNewlyAddedWorksheet, '=VLOOKUP("' & $aData[1][0] & '",' & "'" & $PetrolPriceWorksheetName & "'!A2:B6,2,0)", "A1", False)
    $oPetrolPriceWorksheet.Name = $PetrolPriceWorksheetName
    Local $aLinks = $oWorkBook.LinkSources
    _ArrayDisplay($aLinks)
    $oWorkBook.UpdateLink($aLinks, 1)

EndFunc

 

[water]

UpdateLink only updates external links (DDE and OLE):
https://docs.microsoft.com/en-gb/office/vba/api/excel.workbook.updatelink

To re-calculate a Range, Worksheet or the Workbook use the Calculate method:
https://docs.microsoft.com/en-gb/office/vba/api/excel.range.calculate

[maniootek]

13 minutes ago, water said:

UpdateLink only updates external links (DDE and OLE):
https://docs.microsoft.com/en-gb/office/vba/api/excel.workbook.updatelink

To re-calculate a Range, Worksheet or the Workbook use the Calculate method:
https://docs.microsoft.com/en-gb/office/vba/api/excel.range.calculate

Ok, thank you for info but it still does not work

#include <Excel.au3>
#include <Array.au3>

ExcelLinksExample()

Func ExcelLinksExample()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook = _Excel_BookNew($oExcel)

    Local $aData[][] = [ ["Petrol", "Price"], ["Gasoline", 0.624], ["Diesel", 0.6], ["LPG", 0.423], ["Electricity", 0.037], ["Natural gas", 0.045] ]
;~  _ArrayDisplay($aData, "$aData")
    Local $oPetrolPriceWorksheet = $oWorkBook.Activesheet
    _Excel_RangeWrite($oWorkBook, $oPetrolPriceWorksheet, $aData)
    Local $oNewlyAddedWorksheet = _Excel_SheetAdd($oWorkBook)

    Local $PetrolPriceWorksheetName = 'PetrolPriceSheet'
;~  Local $oRange = _Excel_RangeWrite($oWorkBook, $oNewlyAddedWorksheet, '=WYSZUKAJ.PIONOWO("' & $aData[1][0] & '";' & "'" & $PetrolPriceWorksheetName & "'!A2:B6;2;0)", "A1", False) ;polish version
    Local $oRange = _Excel_RangeWrite($oWorkBook, $oNewlyAddedWorksheet, '=VLOOKUP("' & $aData[1][0] & '",' & "'" & $PetrolPriceWorksheetName & "'!A2:B6,2,0)", "A1", False)
    $oPetrolPriceWorksheet.Name = $PetrolPriceWorksheetName

    $oNewlyAddedWorksheet.Calculate
    $oRange.Calculate
    $oNewlyAddedWorksheet.UsedRange.Calculate
EndFunc

 

[water]

Before spending a lot of time to find a solution for your problem I suggest to use the working solution provided by Nine.

[maniootek]

20 minutes ago, water said:

Before spending a lot of time to find a solution for your problem I suggest to use the working solution provided by Nine.

 

15 hours ago, Nine said:

Naming the sheet before you refer to it should solve your issue...

 

2 hours ago, mandriospo said:

This is still not what I am looking for. I am looking for a way to update cell. My original problem needs it. This is only quick example.

My problem can not be resolved this way.

[jugador]

@mandriospo

Last try
If it's not working that mean you haven’t describe us properly what you need 

Now Run the code
When Msgbox appear 
Open PetrolPriceSheet.xlsx and change price of the Petrol product
you will see instant update in Arkusz2.xlsx sheet.

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>

Global $o_FileName = "PetrolPriceSheet.xlsx"
Global $o_FilePath = @ScriptDir & "\"

__CreatePetrolPriceSheet()
__CreateArkusz2Sheet()

Func __CreatePetrolPriceSheet()
    If FileExists($o_FilePath & $o_FileName) Then Return

    Local $o_Excel = _Excel_Open()
    If @error Then Exit

    Local $o_Workbook = _Excel_BookNew($o_Excel, 1)
    If @error Then
        _Excel_Close($o_Excel)
        Exit
    EndIf
    $o_Workbook.Sheets(1).Name = 'PetrolPriceSheet'

    Local $o_Data[][] = [["Petrol", "Price"], ["Gasoline", 0.624], ["Diesel", 0.6], ["LPG", 0.423], ["Electricity", 0.037], ["Natural gas", 0.045]]
    _Excel_RangeWrite($o_Workbook, 'PetrolPriceSheet', $o_Data)

    _Excel_BookSaveAs($o_Workbook, $o_FilePath & $o_FileName, Default, True)
    _Excel_BookSave($o_Workbook)
    _Excel_Close($o_Excel)
EndFunc

Func __CreateArkusz2Sheet()
    Local $o_Excel2 = _Excel_Open()
    If @error Then
        Exit
    Endif

    Local $o_Workbook2 = _Excel_BookNew($o_Excel2, 1)
    If @error Then
        _Excel_Close($o_Excel2)
        Exit
    EndIf

    $o_Workbook2.Sheets(1).Name = 'Arkusz2'
    Local $o_Data[] = ["Gasoline", "Diesel", "LPG", "Electricity", "Natural gas"]
    _Excel_RangeWrite($o_Workbook2, 'Arkusz2', $o_Data)

    Local $xlup = -4162
    Local $o_TRow = $o_Workbook2.ActiveSheet.Range("A65536").End($xlup).Row

    Local $o_Formula[$o_TRow]
    For $i = 0 To $o_TRow - 1
        $o_Formula[$i] = "=VLOOKUP(" & "A" & $i + 1 & ",'"  & $o_FilePath & "[" & $o_FileName & "]PetrolPriceSheet'!A2:B6, 2, 0)"
    Next
    ;_ArrayDisplay($o_Formula)

    _Excel_RangeWrite($o_Workbook2, $o_Workbook2.Activesheet, $o_Formula, "B1")
    $o_Workbook2.ActiveSheet.UsedRange

    _Excel_BookSaveAs($o_Workbook2, @ScriptDir & "\Arkusz2.xlsx", Default, True)
    _Excel_BookSave($o_Workbook2)

    MsgBox(0, "", "Click to continue.....")
    _Excel_Close($o_Excel2)
EndFunc

 

Edited March 12, 2022 by jugador