Jump to content
willichan

Openning an Excel Workbook without updating links

Recommended Posts

willichan

I have seen some threads where others have been looking for the ability to open an Excel workbook, and not be prompted about updating the linked data.  I had need of this myself, so below is a modified version of _Excel_BookOpen() from the Excel UDF that will not prompt, and will allow you script to continue unimpeeded by linked data.  You will still need to #Include <Excel.udf> to use this

; #FUNCTION# ====================================================================================================================
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water, GMK, willichan
; ===============================================================================================================================
Func _Excel_BookOpen_NoUpdate($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default)
    If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    ;; changing the second parameter on the following line to a 0 tells Excel not to update any links.
    Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 0, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    $oExcel.Windows($oWorkbook.Name).Visible = $bVisible
    ; If a read-write workbook was opened read-only then return an error
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen_NoUpdate

Valid alternate values are:

0: Don't update any references

1: Update external references, but not remote references

2: Update remote references, but not external references

3: Update both remote and external references

 

  • Like 1

Share this post


Link to post
Share on other sites
13lack13lade

so with this - i can have a script automatically update the all links in the workbook when opened?

Share this post


Link to post
Share on other sites
water

I will add this to the official Excel UDF.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Valid alternate values are:

 

0: Don't update any references

1: Update external references, but not remote references

2: Update remote references, but not external references

3: Update both remote and external references

Where did you find this values?

MSDN only shows 0 and 3.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
willichan

Where did you find this values?

MSDN only shows 0 and 3.

I got them from here.  I haven't validated values 1 and 2, but they make sense.

Share this post


Link to post
Share on other sites
willichan

so with this - i can have a script automatically update the all links in the workbook when opened?

I am not sure you can open a workbook to be updated without Excel prompting you (depends on your settings in Excel).  But with this, you can open without updates, ensuring that your script will not be held up by an update prompt.

  • Like 1

Share this post


Link to post
Share on other sites
water

Function _Excel_BookOpen will be modified like this (parameter $iUpdateLinks has been added):

#include <excel.au3>
$oExcel = _Excel_Open()
$oWrokbook = _Excel_BookOpenex($oExcel, @ScriptDir & "\test1.xlsx", Default, Default, Default, Default)
Exit

; #FUNCTION# ====================================================================================================================
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water, GMK, willichan
; Value         Effect
; (omitted)     Excel prompts the user to decide how to update links.
; 0             Excel doesn't update links.
; 1             Excel updates external links but not remote links. (doesn't seem to work with Excel 2010)
; 2             Excel updates remote links but not external links. (doesn't seem to work with Excel 2010)
; 3             Excel updates all links.
; ===============================================================================================================================
Func _Excel_BookOpenEx($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default, $iUpdateLinks = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, $iUpdateLinks, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    $oExcel.Windows($oWorkbook.Name).Visible = $bVisible
    ; If a read-write workbook was opened read-only then set @extended = 1
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(0, 1, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpenEx

I have been testing with Excel 2010 and got the following result

Defautl displays a selection Window where the user has to decide how to handle links

0 doesn't update links

1 is ignored

2 is ignored

3 updates all links.

_Excel_Open parameter $bDisplayAlerts = False has no effect when $iUpdateLinks = Default.

Could you please test and tell me if the function meets your expectations? If yes, it will become part of the Excel UDF.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

so with this - i can have a script automatically update the all links in the workbook when opened?

With my modified version you set the parameter to 3 and all links are updated.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
antonioj84

Hi All, I am getting that error Variable must be of type "object"

here is my code where did I go wrong. The code work for awhile now all my version  are getting the same error on win7

$STORENUM = IniRead(@ScriptDir & "\config.ini", "data", "STORENUM", "")
                $Calculator = IniRead(@ScriptDir & "\config.ini", "data", "Calculator", "")
                
                
                ;$oExcel = _Excel_Open(False, Default, Default, Default, True) ; always create new instance will not close excel on destop
                $oExcel = _Excel_Open(False)
                $datawb = _Excel_BookOpen($oExcel, $STORENUM, True)
                $datawb.worksheets("Black").select
                $datawb.ActiveWorksheet.RefreshAll
                $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
                $mydata = _Excel_RangeRead($datawb, Default, "A1:AI" & $LastRow)
                _Excel_BookClose($datawb, False)
                ;_ArrayDisplay($mydata)

                
                sorry for the spamming here is the answer on the help file
                
                Use $oExcel.Windows($oWorkbook.Name).Visible = True to make the Workbook visible again.
            Else the Workbook will not be shown when you manually open it using Excel.
            Most of the time this parameter is not needed. Using $bVisible = False in _Excel_Open is the preferred way.

 

Captureexcelerror.JPG

Edited by antonioj84

Share this post


Link to post
Share on other sites
antonioj84

run the same script in windows 10 no error

Share this post


Link to post
Share on other sites
antonioj84

sorry for the spamming from the help file

Use $oExcel.Windows($oWorkbook.Name).Visible = True to make the Workbook visible again.
Else the Workbook will not be shown when you manually open it using Excel.
Most of the time this parameter is not needed. Using $bVisible = False in _Excel_Open is the preferred way.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • ajblandford
      By ajblandford
      I have embeded an Excel file in my autoit Script and want to use it as part of my GUI. I want to be able to select a range of cells and add the cell data to an edit box. Then dump that data to an array. I am using GUICtrlCreateObj to embed the spreadsheet. I cannot find any way to let my program see what cells are active so I can add that data to the edit box. 
       
      #include <WindowsConstants.au3> #include <GUIConstantsEx.au3> #include <excel.au3> $FileName = 'C:\VLog\book1.xlsx' If Not FileExists($FileName) Then MsgBox(0, "ERROR", "File not found") Exit EndIf ;Basic GUI $oExcelDoc = ObjGet($FileName) ; Get an excel Object from an existing filename If IsObj($oExcelDoc) Then $mainGUI = GUICreate("Production Room 2", 1800, 1200, 10, 10, $WS_MINIMIZEBOX + $WS_SYSMENU + $WS_CLIPCHILDREN) $GUI_ActiveX = GUICtrlCreateObj($oExcelDoc, 10, 70, @DesktopWidth - 250, @DesktopHeight - 260) $data = GUICtrlCreateEdit("", @DesktopWidth - 225, 75, 100) $btn = GUICtrlCreateButton( "GO", @DesktopWidth - 100, 75, 75) Else MsgBox(0, "", "failed") EndIf ;------------------ ;Turns off all command bars in excel to prevent user from making changes For $Bar In $oExcelDoc.CommandBars If $Bar.Enabled = True Then $Bar.Enabled = False If $Bar.Visible = True Then $Bar.Visible = False Next $oExcelDoc.Application.DisplayFormulaBar = False $oExcelDoc.Application.CommandBars("Shadow Settings").Visible = False $oExcelDoc.Application.DisplayScrollBars = True $oExcelDoc.Application.DisplayStatusBar = False GUISetState()  
    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
×