Jump to content

Excel Links Mapper

Recommended Posts


Here is another one from my archives that filled a specific need.

Here is the back story if you are interested.



I was getting constant calls from one department, complaining about how long it took (over 5 minutes at times) for their Excel files to open.

On investigating, I found that every month, they created a new file that linked back to the previous month's, as well as the previous year's files.  Often linking also to files that were missing (or renamed).

No matter how much I explained the need to break the links to old files that are no longer changing, they just didn't get it, and continued to complain that IT was not fixing the "network problems causing their files to open slow".

I finally broke down and wrote this script to create a visual map of their most recent file.  It took me almost a full day to print the massive graph and tape the sheets into a continuous scroll.  I then called a meeting with the supervisors, managers and the senior manager of that department.  As soon a the meeting started, I asked them all to step out into the hallway where, holding one end of the scroll, I threw the scroll down the hallway.  It didn't even unroll all the way, but the unrolled portion "thunked" against the far wall.  I then pointed at the map of this ONE Excel file and all of its links, and told them that this was their problem.  We didn't even walk back into the conference room.  Their senior manager looked at me and said, "I don't care what any one else here tells you.  Please go ahead and break ALL of the links.  If they need to reference the older data, they can copy and paste it."



Keep in mind that I wrote this script over 3 years ago, so it may not compile or run directly without some minor tweaks.  It also requires the use of GraphViz to build the graph.

#cs ----------------------------------------------------------------------------

    Project Name: ExcelLinksMapper
    Description: Analyse an Excel file's links and map them out.
    Creation Date: 9/26/2014
    AutoIt Version:
    Author: willichan
    Requires: Graphviz (http://graphviz.org/)

#ce ----------------------------------------------------------------------------

Opt("MustDeclareVars", 1)        ;0=no, 1=require pre-declare
Opt("TrayAutoPause",   0)        ;0=no pause, 1=Pause
Opt("TrayMenuMode",    0)        ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID  not return
Opt("TrayIconHide",    0)        ;0=show, 1=hide tray icon

Global Const $MyName=StringLeft(@ScriptName, StringInStr(@ScriptName,".", 0, -1)-1) ;get just the name portion of the script/exe name
Global Const $MyMutex=$MyName & "-82243BEBC30533A3" ;name the mutex for this app
Global $SQLloaded = False
Global $sDbName = @ScriptDir & "\db2gv.db"
ConsoleWrite($sDbName & @crlf)
If _MutexExists($MyMutex) Then Exit

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <file.au3>
#include <array.au3>
#include <excel.au3>


Func _ConfigInitialize()
    ;initializers here
    Global $sSQliteDll = _SQLite_Startup()
    If @error Then MsgBox(0, "SQLite Error", "could not load the DLL")
    Global $sSQLiteDB = _SQLite_Open($sDbName)
    If $sSQLiteDB = 0 Then MsgBox(0, "SQLite Error", "could not open the database")
    $SQLloaded =True
EndFunc  ;==>_ConfigInitialize

Func _ConfigDestroy()
    ;destructors here
    If $SQLloaded Then
EndFunc  ;==>_ConfigDestroy

Func _MutexExists($sOccurenceName)
    Local $ERROR_ALREADY_EXISTS = 183, $handle, $lastError
    $sOccurenceName = StringReplace($sOccurenceName, "\", "")
    $handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $sOccurenceName)
    $lastError = DllCall("kernel32.dll", "int", "GetLastError")
    Return $lastError[0] = $ERROR_ALREADY_EXISTS
EndFunc  ;==>_MutexExists

Func __CreateTables()
    _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS nodes;")
    _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS nodes( name TEXT PRIMARY KEY, fileexists INTEGER);")
    _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS links;")
    _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS links( id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, weight INTEGER);")

Func _Main()
    Local $sInfile, $vResult, $iErrLoop

    $sInfile = FileOpenDialog("Source File", @WorkingDir, "Excel files (*.xl*)", 1 + 2)
    If Not FileExists($sInfile) Then
        MsgBox(0, "Excel Links Mapper Error", "Unable to locate source file")

    $vResult = $SQLITE_IOERR
    $iErrLoop = 5
    While $vResult = $SQLITE_IOERR
        $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sInfile) & ", 1);")
        If Not $vResult = $SQLITE_OK Then Sleep(100)
        $iErrLoop -= 1
        If $iErrLoop = 0 Then
            ConsoleWrite($iErrLoop & " tries" & @CRLF & $sInfile & @CRLF)
            $vResult = $SQLITE_OK

    Global $hOutfile = FileOpen(@ScriptDir & "\" & $MyName & ".gv", 2)
    If $hOutfile = -1 Then
        MsgBox(0, $MyName & " ERROR", "Unable to upen file for output")
    ShellExecute(@ScriptDir & '\ExcelLinksMapper.png')
EndFunc   ;==>_Main

Func _GetExcelLinks($strFileName)
    Local $hQuery, $aCount, $iErrLoop, $vResult
    ConsoleWrite($strFileName & @CRLF)
    Local $iLoop, $iExists
    Local $aLinks
    Local Const $xlExcelLinks = 1
    Local $oExcel = _Excel_Open()

    Local $ret = _Excel_BookOpen_NoUpdate($oExcel, $strFileName, True, True)
    Local $err = @error
    If $err Then
        If Not IsObj($oExcel) Then
            ConsoleWrite($ret & " - " & $err & @CRLF)
    $aLinks = $oExcel.ActiveWorkbook.LinkSources($xlExcelLinks)
    _Excel_BookClose($oExcel, False)
    _Excel_Close($oExcel, False, True)
    If IsArray($aLinks) Then
        If UBound($aLinks) > 0 Then
            For $iLoop = 0 To UBound($aLinks) - 1
                If $aLinks[$iLoop] <> $strFileName Then
                    $iExists = FileExists($aLinks[$iLoop])
                    ConsoleWrite("DEBUG - Calling WriteNode()")
                    __WriteNode($aLinks[$iLoop], $iExists)
                    ConsoleWrite("DEBUG - Calling WriteLink()")
                    __WriteLink($strFileName, $aLinks[$iLoop])
                    If $iExists And ($aLinks[$iLoop] <> $strFileName) Then _GetExcelLinks($aLinks[$iLoop])
EndFunc   ;==>_GetExcelLinks

Func __WriteNode($sName, $iExists)
    Local $iErrLoop = 5 ;Number of attempts to make
    Local $vResult
        ConsoleWrite("DEBUG - WriteNode()" & @CRLF & "    _SQLite_Exec(INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");) - create node entry" & @CRLF)
        $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");")
        If Not $vResult = $SQLITE_OK Then Sleep(100)
        $iErrLoop -= 1
        If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success
    Until $vResult = $SQLITE_OK
EndFunc   ;==>__WriteNode

Func __WriteLink($sName1, $sName2)
    Local $iErrLoop = 5 ;Number of attempts to make
    Local $vResult, $hQuery, $vCount
        ConsoleWrite("DEBUG - WriteNode()" & @CRLF & "    _SQLite_Query(SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";) - lookup link entry" & @CRLF)
    _SQLite_Query($sSQLiteDB, "SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";", $hQuery)
      ConsoleWrite("DEBUG - _SQLite_FetchData()" & @CRLF)
    _SQLite_FetchData($hQuery, $vCount)
    If UBound($vCount) > 1 Then _ArrayDisplay($vCount)
    If $SQLITE_OK And UBound($vCount) > 1 Then
        $vCount = $vCount[1] + 1
        $vCount = 1
        If $vCount = 1 Then
            ConsoleWrite("DEBUG - _SQLite_Exec() - create link entry" & @CRLF)
            $vResult = _SQLite_Exec($sSQLiteDB, "INSERT INTO links ('name1', 'name2', 'weight') VALUES (" & _SQLite_FastEscape($sName1) & ", " & _SQLite_FastEscape($sName2) & ", " & $vCount & ");")
            ConsoleWrite("DEBUG - _SQLite_Exec() - update link entry" & @CRLF)
            $vResult = _SQLite_Exec($sSQLiteDB, "UPDATE links SET 'weight'=" & $vCount & " WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2=" & _SQLite_FastEscape($sName2) & ";")
        If Not $vResult = $SQLITE_OK Then Sleep(100)
        $iErrLoop -= 1
        If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success
    Until $vResult = $SQLITE_OK
EndFunc   ;==>__WriteLink

; #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

Func _GenerateGraph()
    RunWait(@ScriptDir & '\GraphViz238\bin\dot.exe -Tpng "' & @ScriptDir & '\' & $MyName & '.gv" -o "' & @ScriptDir & '\ExcelLinksMapper.png"')
EndFunc   ;==>_GenerateGraph

Func _WriteHeader()
    __OutLine(0, "digraph main {")
EndFunc   ;==>_WriteHeader

Func _WriteNodes()
    Local $aResult, $iRows, $iColumns, $iRval
    Local $iLoop, $sStyle
    __OutLine(1, "// Nodes")
    $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name, fileexists FROM nodes;", $aResult, $iRows, $iColumns)
    If @error Then ConsoleWrite("_WriteNodes()" & @CRLF & "_SQLite_GetTable")
    If $iRval = $SQLITE_OK Then
        For $iLoop = 1 To $iRows
            If $aResult[($iLoop * 2) + 2] > 0 Then
                $sStyle = "normal"
                $sStyle = "missing"
            __OutNode($aResult[1 + (2 * $iLoop)], $sStyle)
        MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()")
EndFunc   ;==>_WriteNodes

Func __OutNode($sName, $sStyle = Default)
    If $sStyle = Default Then $sStyle = "Normal"
    Switch StringLower($sStyle)
        Case "missing"
            __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=red,fontcolor=red,shape=octagon];')
        Case Else ;"normal", Default
            __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=black,fontcolor=black,shape=box];')
EndFunc   ;==>__OutNode

Func _WriteLinks()
    Local $aResult, $iRows, $iColumns, $iRval
    Local $iLoop, $sStyle, $aCount
    __OutLine(1, "// Links")
    $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name1, name2 FROM links ORDER BY name1 ASC, name2 ASC;", $aResult, $iRows, $iColumns)
    If $iRval = $SQLITE_OK Then
        For $iLoop = 1 To $iRows
            __OutLink($aResult[1 + (2 * $iLoop)], $aResult[2 + (2 * $iLoop)])
        MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndFunc   ;==>_WriteLinks

Func __OutLink($sName1, $sName2, $iWeight = 1)
    __OutLine(1, __MakeName($sName1) & ' -> ' & __MakeName($sName2) & ';')
EndFunc   ;==>__OutLink

Func _WriteFooter()
    __OutLine(0, "}")
EndFunc   ;==>_WriteFooter

Func __OutLine($iTabs, $sText)
    Local $iLoop
    If $iTabs > 0 Then
        For $iLoop = 1 To $iTabs
            FileWrite($hOutfile, "  ")
            ;ConsoleWrite("  ")
    FileWriteLine($hOutfile, $sText)
EndFunc   ;==>__OutLine

Func __MakeName($sText)
    Local $sNewName = StringReplace($sText, "\", " ")
    $sNewName = StringReplace($sNewName, "/", " ")
    $sNewName = StringReplace($sNewName, "'", " ")
    $sNewName = StringReplace($sNewName, '"', " ")
    $sNewName = StringReplace($sNewName, ':', " ")
    $sNewName = StringReplace($sNewName, '.', " ")
    $sNewName = StringReplace($sNewName, '-', " ")
    $sNewName = StringReplace($sNewName, '$', " ")
    $sNewName = StringStripWS($sNewName, 8)
    Return StringLower($sNewName)
EndFunc   ;==>__MakeName


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

    • Ahmed101
      By Ahmed101
      I have more than 12 workbooks opened together, if i wanted to attach to the last workbook opened it will take more than 1 minute !
      Is there any solution for that ?
    • Daniza
      By Daniza
      Hello! where should I start, if I want to have a Progress Bar while waiting for my File to be open, can I use WinWaitActive? Thanks,
    • Evolutionnext
      By Evolutionnext
      I am still a noob and not a programmer, would greatly appreciate your help.
      Open Excel file with file path and name: C:\Users\GENOBEAUTYPC1\Desktop\ACTIVE BEAUTY LABELS\BeautyMe Label 200ml ACTIVE VERSION.xlsx
      This file path and name is saved in the variable: $sAnswer
      Go to Excel Tab called "formular"
      Go to Cell A1
      Insert the text saved in the variable: $sAnswer2
      ATTENTION!!! This has 2 problems.
      Problem number 1: This text contains special characters that need to be interpreted as raw text. (content is: Gemischt für#30 ml#Mindestens haltbar bis#Maria Wallerstorfer#Anwendung: Täglich 1x morgens auf das gereinigte Gesicht auftragen. Augenkontakt vermeiden.#Über 0 C° und unter 25 C° lagern.#Lot:N8A1028/D30/V2.1#Genome Plus GmbH#Georg-Wrede-St. 13, D-83395 Freilassing#GEN SERUM#DAY)
      Problem number 2: This textis longer than 255 characters.
      Can anyone help me?
      I try to do it really primitively by opening the excel, waiting until it is open, clicking where the tab is, clicking where the cell is and inserting the content of the variable, but I am stuck at the point where I am limited by 255 characters.

      ; Opening the right excel FileChangeDir
                  tooltip("File exists and is called:"&$sAnswer ,300,300)
                  ShellExecute($sAnswer ,"" ,"" ,"" , @SW_MAXIMIZE)
                  tooltip("Now lets insert the right content into the excel",300,300)
                              tooltip("inserting label content",300,300)
                  Send($sAnswer2,  1)
                                          tooltip("inserting INCIS",300,300)
                  Send($sAnswer3, 1)
    • AzgarD
      By AzgarD
      Hi guys. I know this is a newbie topic, very newbie, but i've read a lot of stuff and still don't get it. I just need to copy something from Excel cell, paste this in other program, copy something in this program and paste in other Excel cell. Something like...
      Copy A2 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C2 Then Copy A3 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C3 ... And it goes on The problem is, how can i "communicate" with Excel and do this row change? Like A2 to C2 and A3 to C3 ... In a efficient way that can be done like hundreds of times.
      Very newbie question but still not understanding this.
      Ty guys.
    • Gowrisankar
      By Gowrisankar
      Dear members of the forum,
      I need to open excel files that may or may not need a password and finally move the files that needs password to manual queue.
      Is there a fastest way to do this?
      PS: I have a huge respect for the rules of this forum. I am not asking assistance to override any security measure. I just need to segregate the files that needs passwords.