Search the Community

Hi, I've some data that I need to write into an existing excel sheet.The sheet has 24 active columns (a-x) and some rows populated, 30 or so. To get my data I read a locally saved htm file and populate some vars, no problem. The question is how to then find the first blank row, write my data, move to the next row, read the next data set and write it. I've got the code done for the data loop piece, just can not for the life of me figure out the excel. I don't care if I use the excel UDF or COM calls, just need a push in the right direction please... Here is what I have so far, any help is appreciated. #include <Constants.au3> #include <Excel.au3> #include <Array.au3> #include <Date.au3> #include <File.au3> #include <String.au3> ;WinSetState("Excel","",@SW_MINIMIZE) Const $log = @ScriptDir & "\CreateDB.log" Const $sSrcPath = @ScriptDir & "\result" Const $xlPath = "\xxx\upload.xls" Local $fTgtFile Local $sCompName Local $sCompStreetAdd Local $sCompCity Local $sCompState Local $sCompZip Local $sCompCounrty Local $sCompTele Local $sNAICS Local $sNAICSDesc Local $sSIC2 Local $sSIC4 Local $sSICM Local $sSIC2Desc Local $sSIC4Desc Local $sSICMDesc Local $sMemberVar Local $sMemberTitle Local $sMemberFunc Local $sMemberPosition Local $sMemberSIC2 Local $sMemberSIC4 Local $sMemberSICM Local $sMemberRev Local $sMemberEmp _FileWriteLog($log, "***Run Start***") Local $oExcel = _ExcelBookOpen($xlPath) ;Setup Excel WinSetState("Excel","",@SW_MAXIMIZE) Sleep (1000) _ExcelSheetActivate($oExcel, "Sheet1") Local $iRows = $oExcel.ActiveSheet.UsedRange.Rows.Count Local $aHandle = _FileListToArray($sSrcPath, "*.htm",1) ; Work through the folder If @error Then MsgBox(0, "Error", "No files matched") Else ; Loop through the found files For $i = 1 To 5 ;$aHandle[0] MsgBox(4096, "File:", $aHandle[$i]) $fTgtFile = FileOpen($sSrcPath & "\" & $aHandle[$i],0) If $fTgtFile = -1 Then MsgBox(0, "Error", "Unable to open file.") Exit EndIf While 1 ;MsgBox(0, "Company City:", $sCompCity[0]) Local $line = FileReadLine($fTgtFile) If @error = -1 Then ExitLoop ;Company Name If StringInStr ($line,'companyName: "') Then $sCompName = _StringBetween($line,'companyName: "','",') EndIf ;Company Street If StringInStr ($line,'itemprop="streetAddress">') Then $sCompStreetAdd = _StringBetween($line,'itemprop="streetAddress">','</div>') EndIf ;Company City If StringInStr ($line,'city: "') Then $sCompCity = _StringBetween($line,'city: "','",') EndIf ;Company State If StringInStr ($line,'state: "') Then $sCompState = _StringBetween($line,'state: "','",') EndIf ;Company Zip If StringInStr ($line,'<span class="addresspostalCode" itemprop="postalCode">') Then $sCompZip = _StringBetween($line,'itemprop="postalCode">','</span>') EndIf ;Company Country If StringInStr ($line,'"addressCountry" class="">') Then $sCompCountry = _StringBetween($line,'class="">','<!--country-->') EndIf ;Company Telephone If StringInStr ($line,'itemprop="telephone">') Then $sCompTele = _StringBetween($line,'itemprop="telephone">','</dd>') EndIf ;NACIS If StringInStr ($line,'naics":"') Then $sNAICS = _StringBetween($line,'naics":"','"}') EndIf ;NACIS Desc If StringInStr ($line,'naics_desc":"') Then $sNAICSDesc = _StringBetween($line,'naics_desc":"','"}') EndIf ;SIC2 If StringInStr($line,'{"sic2":') Then $sSIC2 = _StringBetween($line,'{"sic2":','}') EndIf ;SIC4 If StringInStr($line,'{"sic4":') Then $sSIC4 = _StringBetween($line,'{"sic4":','}') EndIf ;SIC4 If StringInStr($line,'{"sicm":') Then $sSICM = _StringBetween($line,'{"sicm":','}') EndIf ;SIC2Desc If StringInStr($line,'{"sic2_desc":') Then $sSICM = _StringBetween($line,'{"sic2_desc":','}') EndIf ;SIC4Desc If StringInStr($line,'{"sic4_desc":') Then $sSICM = _StringBetween($line,'{"sic4_desc":','}') EndIf ;SICM_Desc If StringInStr($line,'{"sicm_desc":') Then $sSICM = _StringBetween($line,'{"sicm_desc":','}') EndIf ;Member Var If StringInStr($line,'{"member_s_var":') Then $sMemberVar= _StringBetween($line,'{"member_s_var":','}') EndIf ;Member Title If StringInStr($line,'member_attrs_title":"') Then $sMemberVar= _StringBetween($line,'member_attrs_title":"','}') EndIf ;Member Function If StringInStr($line,'{"member_function":"') Then $sMemberFunc= _StringBetween($line,'{"member_function":"','}') EndIf ;Member Position If StringInStr($line,'{"member_position":"') Then $sMemberPosition= _StringBetween($line,'{"member_position":"','}') EndIf ;Member SIC2 If StringInStr($line,'{"member_sic2":"') Then $sMemberSIC2= _StringBetween($line,'{"member_sic2":"','}') EndIf ;Member SIC4 If StringInStr($line,'{"member_sic4":') Then $sMemberSIC4= _StringBetween($line,'{"member_sic4":','}') EndIf ;Member SICM If StringInStr($line,'{"member_sicm":') Then $sMemberSICM= _StringBetween($line,'{"member_sicm":','}') EndIf ;Member Revenue If StringInStr($line,'{"member_revenue":"') Then $sMemberRev= _StringBetween($line,'{"member_revenue":','}') EndIf ;Member Employees If StringInStr($line,'{"member_employees":"') Then $sMemberRev= _StringBetween($line,'{"member_employees":','}') EndIf ;Stuck here on how to get those vars into excel :( WEnd Next EndIf Exit ; Open the workbook ;~ $oExcel = ObjCreate("Excel.Application") ;~ $oExcel = ObjCreate("Excel.Application") ;~ $oBook = $oExcel.Workbooks.Open("xxx\upload.xls") ;~ $oExcel.Visible = True ;~ $oSheet = $oBook.Worksheets(1) ;~ $oSheet.Activate ;~ $oRange = $oSheet.UsedRange ;~ $oRange.SpecialCells($xlCellTypeLastCell).Activate ;~ $newRow = $oExcel.ActiveCell.Row + 1 ;~ $oExcel.Range("A" & $newRow).Activate