danmcg Posted December 4, 2020 Share Posted December 4, 2020 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 popupSub 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 Link to comment Share on other sites More sharing options...
danmcg Posted December 6, 2020 Author Share Posted December 6, 2020 any help please Link to comment Share on other sites More sharing options...
Developers Jos Posted December 6, 2020 Developers Share Posted December 6, 2020 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. SciTE4AutoIt3 Full installer Download page - Beta files Read before posting How to post scriptsource Forum etiquette Forum Rules Live for the present, Dream of the future, Learn from the past. Link to comment Share on other sites More sharing options...
danmcg Posted December 7, 2020 Author Share Posted December 7, 2020 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? Link to comment Share on other sites More sharing options...
danmcg Posted December 10, 2020 Author Share Posted December 10, 2020 any help please... Link to comment Share on other sites More sharing options...
GokAy Posted December 11, 2020 Share Posted December 11, 2020 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 Link to comment Share on other sites More sharing options...
danmcg Posted December 16, 2020 Author Share Posted December 16, 2020 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now