Jump to content

Search the Community

Showing results for tags 'write excel cell data'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • General
    • Announcements and Site News
    • Administration
  • AutoIt v3
    • AutoIt Help and Support
    • AutoIt Technical Discussion
    • AutoIt Example Scripts
  • Scripting and Development
    • Developer General Discussion
    • Language Specific Discussion
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Categories

  • AutoIt Team
    • Beta
    • MVP
  • AutoIt
    • Automation
    • Databases and web connections
    • Data compression
    • Encryption and hash
    • Games
    • GUI Additions
    • Hardware
    • Information gathering
    • Internet protocol suite
    • Maths
    • Media
    • PDF
    • Security
    • Social Media and other Website API
    • Windows
  • Scripting and Development
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Location


WWW


Interests

Found 1 result

  1. 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
×
×
  • Create New...