_Excel_Open() COM error -2147221164
-
Recently Browsing 0 members
No registered users viewing this page.
-
Similar Content
-
By JohnnyTries
Hi Fellow Automators,
Long time listener, first time caller. I've resisted posting on the forums as long as possible for fear of public lynching, but I'm stuck and could really use some help.
Note: If this is the wrong side of the forum for this topic, I apologize.
I've built a GUI and script to make our lab data collection easier. The app has a number of input boxes and a 'record' button. The user fills out the input boxes with various notes and then presses 'record', which in turn presses 'record' on two other, separate apps simultaneously, pulls those recordings together into one folder, and then takes the text from the input boxes and adds it in a new row at the bottom of an existing .xlsx spreadsheet. Everything works great, except that every time I open the app to start collecting data for the day, the 'headers' for the $aArray are added to a new row and then the text is added below it. Now, if I don't close the app between collections, subsequent 'recordings' are added to the spreadsheet as expected. If I close the app and open it, the 'first' recording of the day adds the headers to a new row. I don't need new 'header' info because I've already got that in row 1 of the spreadsheet.
If someone could tell me where I'm !#$%ing up, I would greatly appreciate it.
#RequireAdmin #include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <MsgBoxConstants.au3> #include <WindowsConstants.au3> #include <String.au3> #include <Process.au3> #include <FileConstants.au3> #include <WinAPIFiles.au3> #include <DirConstants.au3> #include <Array.au3> #include <AutoItConstants.au3> #include <File.au3> #include <WinAPIShPath.au3> #include <Excel.au3> ;Declaring the $aArray and location of the speadsheet at the top of the script Local $aArray[1][9] = [["TestID","DateTimeStamp","Tamb_C","BGTemp_C", "GasType", "TrueFlow_slm", "Lens-BGDist_in", "Lens-LeakDist_In", "AddNotes"]] Local $sDataFilePath = @ScriptDir & "\Notes\DualCaptureNotes.xlsx" Func CaptureVideo() Global $TestID = GuiCtrlRead($TestIDInput) Global $timestamp = @YEAR & "-" & @MON & "-" & @MDAY & "-" & @HOUR & "-" & @MIN & "-" & @SEC Global $Cam1Dir = "C:\Archive" Global $Cam2Dir = "C:\ALD" ;Capture Cam1 Data WinActivate("Cam1 App") ControlFocus("Cam1 App", "Save",'WindowsForms10.Window.8.app.0.2099316_r7_ad114') ControlSend("Cam1 App", "Save", 'WindowsForms10.Window.8.app.0.2099316_r7_ad114', "{SPACE}") ;Capture Cam2 Data WinActivate("Cam2 App") ControlClick("Cam2 App", "", 'WindowsForms10.Window.8.app.0.1b0ed41_r7_ad122', '', 1, 10, 10) ;Wait to ensure data files have been fully written to their default locations Sleep(2000) ;Self-explanatorily named functions MoveData() RecordNotesToArray() RecordArraytoExcel() WinActivate("DualCapture") EndFunc ;==>CaptureVideo ;Skipping ahead to the .xlsx part.... Func RecordNotesToArray() _ArrayAdd($aArray, GUICtrlRead($TestIDInput) & "|" & $timestamp & "|" & GUICtrlRead($Tamb_CInput) & "|" & GUICtrlRead($BGTemp_CInput) & "|" & GUICtrlRead($GasTypeInput) & "|" & GUICtrlRead($TrueFlow_slmInput) & "|" & GUICtrlRead($Dist_BG_inInput) & "|" & GUICtrlRead($Dist_Leak_inInput) & "|" & GUICtrlRead($AddNotesInput)) EndFunc ;==>RecordNotesToArray() Func RecordArraytoExcel() Local $oExcel = _Excel_Open() Local $oWorkBook If Not FileExists($sDataFilePath) Then $oWorkBook = _Excel_BookNew($oExcel) Else $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath) EndIf $oWorkBook.Worksheets("DataTable").Columns("A:I").AutoFit $LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row $Rowrange = "A"&$LastRow+1 Consolewrite($Rowrange & @crlf) _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray, $Rowrange) If FileExists($sDataFilePath) Then _Excel_BookSave($oWorkBook) Else _Excel_BookSaveAs($oWorkBook, $sDataFilePath) EndIf _Excel_BookClose($oWorkBook) _Excel_Close($oExcel) EndFunc ;==>RecordArrayToExcel() I appreciate your time and any help you can provide.
Best,
Johnny
-
By goku200
I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
Example of filename:
12345_v1.0_TEST Name [12345]_01.01.2022.html
12345 would be in one column
v1.0 would be in another column
TEST Name [12345] would be in another column
01.01.2022 would be in another column
.html would be in another column
Note: filenames always change each day.
Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
-
By SkysLastChance
I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567
#include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved.
-
By SkysLastChance
I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong.
My goal is to auto fill some formulas that are next to a pivot table in columns A-C.
_Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling.
Hoping someone can point me in the right direction.
-
By Rskm
Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel. Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on. If i read the notepad and paste it line by line, it is taking lot of time. Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel. The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time. The expected excel format is attached here. any help is appreciated. thanks
Tmp.xls
-
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