My network script works but local C fails, don't know why

[danmcg]

so i have a procedure call LoadAutoIT2 that works in a networked environment but when it is run locally (on my laptop) it fails (meaning that the code is not pasted in the editor). don't know why. maybe someone has some insight. note that i renamed my usernames to network and local to make it clearer.

 

expandcollapse popup

Sub LoadAutoIT2()
    'made changes here, merge with home

    Dim answer As VbMsgBoxResult
    Dim ReadData As String
    Dim autoit As AutoItX3

    On Error GoTo errhandler
    
    'early binding
    
    Set autoit = New AutoItX3
    
    'Quick reference for the Send( "keys" [, flag] ) Command.
    '^=Ctrl    !=Alt    +=Shift    #=Win
    With autoit
        'open editor
        Call .Run("C:\Program Files\Office RibbonX Editor\OfficeRibbonXEditor.exe")
        Sleep (3000)
        'wait till it opens
        Call .WinSetState("Office RibbonX Editor", "", autoit.SW_MAXIMIZE)
        Sleep (3000)
        
        If Environ("username") = "network" Then
            With New FileSystemObject
                If .FileExists("W:\code\callbacks.txt") Then
                    .DeleteFile "w:\code\callbacks.txt"
                End If
            End With
        Else
            With New FileSystemObject
                If .FileExists("c:\code\callbacks.txt") Then
                    .DeleteFile "c:\code\callbacks.txt"
                End If
            End With
        End If
        
        'check for window
        If .WinExists("Office RibbonX Editor") Then
            'not sure if wait is needed
            Sleep (3000)
            'Ctrl Open
            Call .Send("^o")
            'wait
            Sleep (3000)
            Call .WinWait("Open OOXML Document")
            Sleep (3000)
            'open excel file
            If Environ("Username") = "network" Then
                Call .Send("C:\Users\network\Documents\Excel\RibbonTester.xlsm")
            Else
                Call .Send("C:\Users\local\Documents\Excel\RibbonTester.xlsm")
            End If
            'wait
            Sleep (3000)
            'press enter
            Call .Send("{ENTER}")
            'press Ctrl A - select all
            Call .Send("^a")
            'need to remove paste this way and loop an array to read text file line by line
            'press Ctrl V - paste
            'Call .Send("^v")
            
            If Environ("username") = "network" Then
                Open "w:\code\RibbonXButton.xml" For Input As #1
            Else
                Open "c:\code\RibbonXButton.xml" For Input As #1
            End If
            Do Until EOF(1)
                Line Input #1, ReadData
                Debug.Print ReadData
                SendKeys (ReadData)
            Loop
            Close #1
            
            'wait
            Sleep (3000)
            'press Ctrl + Shift + V - Validation
            Call .Send("^+v")
            Sleep (3000)
            'wait for window to appear
            If .WinExists("XML is Valid") Then
                'press enter
                Call .Send("{ENTER}")
                'press Ctrl S - save
                Call .Send("^s")
                'press Ctrl W - close file
                'Call .Send("^w")
                'press Ctrl Shift C - callbacks
                Call .Send("^+c")
                'press tab to move focus
                Call .Send("{TAB}")
                'press Ctrl A - select all
                Call .Send("^a")
                'press Ctrl C - copy
                Call .Send("^c")
                'run notepad
                If FileExists("C:\windows\system32\notepad.exe") Then
                    Call .Run("C:\Windows\system32\Notepad.exe")
                ElseIf FileExists("C:\windows\notepad.exe") Then
                    Call .Run("C:\Windows\Notepad.exe")
                Else
                    MsgBox ("Notepad not found in the Windows folder, exiting")
                    Exit Sub
                End If
                'wait for window to appear
                Call .WinWaitActive("Untitled - Notepad")
                'Call .WinWaitActive("Untitled - Notepad")
                'press Ctrl V - paste
                Call .Send("^v")
                'press Ctrl Shift S - save as
                Call .Send("^+s")
                'name the file
                Sleep (3000)
                If Environ("Username") = "network" Then
                    Call .Send("W:\Code\Callbacks.txt")
                Else
                    Call .Send("C:\Code\Callbacks.txt")
                End If
                'wait for the save as Dialog box
                Sleep (3000)
                'send Alt S
                Call .Send("!s")
                Sleep (3000)
                'close the editor
                Call .WinClose("Office RibbonX Editor")
                'open test workbook with ribbon code
                If Environ("username") = "network" Then
                    Call .Run("C:\Program Files (x86)\Microsoft Office\root\Office16\excel.exe C:\Users\network\Documents\Excel\RibbonTester.xlsm")
                Else
                    Call .Run("C:\Program Files (x86)\Microsoft Office\root\Office16\excel.exe C:\Users\local\Documents\Excel\RibbonTester.xlsm")
                End If
            Else
                'the XML is Valid dialog box did not appear so the code is not validated
                Call MsgBox("There are error(s) with the xml code", vbOKOnly, "XML Error(s)")
                'decide whether to close the editor or leave open to capture the errors
                Call .WinClose("Office RibbonX Editor")
                Exit Sub
            End If
        Else
            'ribbon editor is not installed, maybe add link to file
            Call MsgBox("Office RibbonX Editor missing from computer", vbOKOnly, "MISSING!")
            answer = MsgBox("Do you want to download it from the author's website? ", vbYesNo, "Download")
            If answer = vbNo Then
                Call MsgBox("No further automation is avaiable till you download and install the editor", vbOKOnly, "AUTOMATION ERROR")
                Exit Sub
            Else
                MsgBox ("Click on 'Self-Contained .NET Binaries (Unsigned)' " & vbCr & "and install it on your computer" & vbCr & "then rerun the save command")
                Call ThisWorkbook.FollowHyperlink("https://dev.azure.com/fernandreu-public/OfficeRibbonXEditor/_build/results?buildId=949&view=artifacts&type=publishedArtifacts")
            End If
        End If
    End With
    
    Exit Sub
    
errhandler:
    MsgBox (Err.Number & " " & Err.Description)
    Resume Next
    Exit Sub
    
End Sub

 

[danmcg]

any help please

[Jos]

So you dump a part of a VB project here and tell us that it doesn't work without any specifics and expect help?

Guess it is crystal ball time.  

[danmcg]

sorry, maybe i wasn't clear, my sincere apologies. when the procedure runs on my networked environment, it copies the code from RibbonXButton.xml and pastes in the customui editor. and all is good. but when it runs on my laptop, then it fails during the paste and a message box tells me that there are error's in the code.

below is the relevant code

            If Environ("username") = "network" Then
                Open "w:\code\RibbonXButton.xml" For Input As #1
            Else
                Open "c:\code\RibbonXButton.xml" For Input As #1
            End If
            Do Until EOF(1)
                Line Input #1, ReadData
                Debug.Print ReadData
                SendKeys (ReadData)
            Loop
            Close #1

i have tried both excel sendkeys and autoit send but nothing seems to work. any clues as to what i am doing wrong?

[danmcg]

any help please...

[GokAy]

Hey,

I would start by putting a debug.print inside if username = "network" checks, see which one is running.

Also, environ$("user...") would be better, however, also see:

https://stackoverflow.com/questions/39656844/excel-vba-environusername-error

[danmcg]

ok, i can do that (and probably best if i print out the code for network vs local) but as for the environ statement, it has always worked well in the past for me. will bring my laptop to office to check if issue remains.