Jump to content
willichan

Excel Links Mapper

Recommended Posts

willichan

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

Here is the back story if you are interested.

Spoiler

 

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>


_ConfigInitialize()
_Main()


Func _ConfigInitialize()
    OnAutoItExitRegister("_ConfigDestroy")
    ;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
    __CreateTables()
EndFunc  ;==>_ConfigInitialize

Func _ConfigDestroy()
    ;destructors here
    If $SQLloaded Then
        _SQLite_Close()
        _SQLite_Shutdown()
    EndIf
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);")
EndFunc

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")
        Exit
    EndIf

    $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
        EndIf
    WEnd
    _GetExcelLinks($sInfile)

    Global $hOutfile = FileOpen(@ScriptDir & "\" & $MyName & ".gv", 2)
    If $hOutfile = -1 Then
        MsgBox(0, $MyName & " ERROR", "Unable to upen file for output")
        Exit
    EndIf
    _WriteHeader()
    _WriteNodes()
    _WriteLinks()
    _WriteFooter()
    FileClose($hOutfile)
    _GenerateGraph()
    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)
            Exit
        EndIf
    EndIf
    $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])
                EndIf
            Next
        EndIf
    EndIf
EndFunc   ;==>_GetExcelLinks

Func __WriteNode($sName, $iExists)
    Local $iErrLoop = 5 ;Number of attempts to make
    Local $vResult
    Do
        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
    Else
        $vCount = 1
    EndIf
    Do
        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 & ");")
        Else
            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) & ";")
        EndIf
        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"
            Else
                $sStyle = "missing"
            EndIf
            __OutNode($aResult[1 + (2 * $iLoop)], $sStyle)
            ;;
        Next
    Else
        MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()")
        Exit
    EndIf
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];')
    EndSwitch
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)])
        Next
    Else
        MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
        Exit
    EndIf
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("  ")
        Next
    EndIf
    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

    • nooneclose
      By nooneclose
      Hey. I'm working on a new project and was wondering if there is a better way to "update" my Column E array. 
      Here is my code: 
      Local $nI  = 0                                                            ;Creates a name index of 0: nI = Name index Local $nII = 1                                                            ;Creates a name index of 1 for second loop: nII = Name Index 2 For    $iN = 0 To $IndexRows Step 1                                       ;Checks the roster for any names that appear twice      For $iN2 = 0 To $IndexRows Step 1          if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then              Local $timeSheetName = _ArraySearch($e_Names, $d_Names[$nI], 0, 0, 0, 0, 1)              ;MsgBox($MB_SYSTEMMODAL, "Found it", $d_Names[$nI] & " In column E on Row " & $timeSheetName)              Local $eI  = $timeSheetName + 1              ;ConsoleWrite($timeSheetName & @CRLF)              ;ConsoleWrite($eI & @CRLF)              ;ConsoleWrite(@CRLF)              _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $eI & ":F" & $eI, $xlShiftDown)                                                                          ;Inserts a empty cell in columns E and F.              _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $eI)                                                                         ;Fills the empty cell in columns E with the doubled name              $aArray_Index = 2                                           ;Array element counter              For $Index = 2 To $IndexRows Step 1                        ;Loops through every row in the Excel file unto no rows are found or a null row is found                  $Array_Value_E = _Excel_RangeRead($OpenWorkbook, Default, "E"&$Index)                  $e_names[$aArray_Index] = $Array_Value_E                ;While the code loops every value in column E is stored in the E array (updating the array)                  $aArray_Index += 1              Next              ExitLoop          EndIf      Next      $nI  += 1      $nII += 1 Next Basically, It checks a roster for people whose name appears twice then inserts a new "row" for that person because they work in two different departments.
      I have to find that name however in Column E if two appear in column D. My code works but I think it is not as efficient as it could be. 
      Any ideas on how to improve the "update" for my array?
      (once it finds the double names in Column D it then searches for that name by going name by name in the Column E array and once it finds it inserts a new row. However, the E array doesn't have that new row stored in it so I have to "update" the array to properly find the next name)
      Any and all tips would be greatly appreciated. 
       
      NOTE: Just assume I'm opening the excel file properly please do not add that code in, it only complicates your answer. 
    • smud
      By smud
      Currently, I'm working on a program that will display Dialog boxes with either Yes or No.
      For each dialog, I reward the user with X amount of Credits.
      I'm hoping to output the amount of credits to a cell in a column (there will be 20 different columns).
      It will only post to a row that is equal to today's date (first column). If no row exists yet with the current date, it will start a new row.
      Any suggestions?
      Thank you
    • nooneclose
      By nooneclose
      How do I properly convert this to Autoit? This is a VBA macro that I recorded in Excel.
       ActiveSheet.Outline.ShowLevels RowLevels:=2 I need this to close my subtotal once it is finished. 
      any help will be greatly appreciated. 
    • Skeletor
      By Skeletor
      Hi All,
      While creating a few excel spreadsheets using AutoIt, I came across something which to my limiting time to research the forums I don't anyone has mentioned. 
      The color pallettes are reversed. 
      Huge shock to me.
       
      I wanted to produce a red row but kept on getting blue. 
      Seems like 0xFF0000 was red on the charts but when running the script, I got blue. 
      I then played around with the colors, and after a few tries, I finally got Red. 
      Reversed the FF0000 and the result is 0000FF.
       
      So for Excel compared to Html
      0000FF (Red) - Excel
      0000FF (Blue) - Html

      FFFF00 (Cyan) - Excel
      FFFF00(Yellow) - Html
       
    • Jemboy
      By Jemboy
      Yesterday I had to make some little changes to an old Autoit program we use at my work.
      The program reads some data and convert it to Excel.
      Before writing the cell, it is changed to text and later on I slap the column with an autofitwidth.
      Furthermore weI execute a conditional format on the sheet, to make the data more readable.
      I quickly found out that because of the breaking changes Excel.udf had starting from AutoIt 3.3.12.0,
      a lot of things had to been changed.
      The changes I had to do, only took 10 minutes.
      After trying to adjust the script for over 5 hours, to get it working with the new Excel.udf, I gave up.
      I stopped changing the script, uninstalled the my Autoit and went looking for an older version.
      Luckilly I was able to find Autoit v3.3.8.1 (with corresponding Scite) in my software repository.
      Installing Autoit V3.3.8.1 and compiling the file, now took me  10 minutes .
       
      So why did I not get the old script working with the new Excel.udf?
      There are several reasons I failed getting the old script working with the new Excel UDF.
       I had some pressure from management to fix it ASAP (and got a little anxious)  Most all resources on the internet point to the working of the old EXcel.UDF And offcourse there were thosing "breaking changes",
      with new functions using diffrent parameters or using parameters in different order. One of the column's on the sheet is used to store EAN13 (barcode) and was formatted like 1,23E12.
      I couldn't change the cell to text, also autofitwidt was not working and using conditional formatting was also a no-no.
      So in the end I could use the new Excel UDF, but not desapointed management.
       
      What would I like to ask?
      I understand that sometimes you want to rewrite a program to make it better. I even understand that one has to make breaking changes sometimes.
      But in this case because of lacking examples/resources my day went completely down the drain.
      I would like to ask the Excel.udf developpers to:
      Make more functions available to do things like changing cell properties easily, changing cell color, do an autofit columnwidth, format data conditionally. 
        Or write an Example using the (new) Excel UDF, making examples how to format a cell, do conditional format, changing cell colors etc.
        I probably am more of an example guy.
      Having a good Excel.UDF Example showing a lot of common things normally makes, programming things easy for me.
      Because I can keep tweaking snippets until I get it working the way I want it.
       
      So dear developpers, could you help me and other future user out?
       
       
       
       
       
       
       
       
       
       
       
       
       
       
×