;=============================================================================== ; Version: 1_4_8 ; Revision Sept 19th 2005; numbers changes ; Instructions ;examples, update, http://www.autoitscript.com/forum/index.php?showtopic=14166&view=findpost&p=96727 ; Description: Various functions to communicate with Excel files via com ; Turns off calculation to manual, and returns to auto/ manual which was preset if "Save" requested ; Can display [/ or not] as it goes, save at end or not, and exit with /or without] save ; Simple; Read, Add, Write("Into"), Run (macroRun) into a cell [or range (write only?)] ; Complex; Write on a row (option column) a tab-delimited string (eg from text file) ; Complex; Append after last row as option ; Syntax: _ExcelCOM($sFilePath,$Sheet,$Column,$Row,$MEExcelCom,$Save,$ExcelValue,$Visible,$Exit,$LastRow,$ToColumn) ; Syntax: Short - see at end ; Parameter(s): $sFilePath - Path and filename of the excel file ; $Sheet - Worksheet number [or name] ; $Column -parameter allows column by letter(s) [or numbers], or range [then row is ignored] ; $Row - Row number ; $MEExcelCom - Type of action; Read, Add, Into, Run, Calc, Paste, Show, Ready, Close, Import ; $Save - Saves after action ["other"/"Save"] [0/1] ; ;[**NOTE - no worksheet update calculation occurs till save, unless also run "calc", whatever previous setting] ; $ExcelValue - Value to Insert; dummy required otherwise [may be tab-delimited string ; ; [set $LastRow or $ToColumn if needed] ; $Visible - Set whether to show or not [0/1 - or 0/"Visible"] ; $Exit - Set whether to Exit or not [0/1 - or 0/"Exit"] ; $LastRow - Set whether to automatically select row after last as insertion row [0/1]- or 0/"LastRow" ; $ToColumn - Set whether to insert tab-delimited string as column (1) or not (otherwise)- or 0/"ToColumn" ; Requirement(s): AutIt3 Beta after about 3.1.1.18 (currently 74) ; Return Value(s): On Success - Returns value from index cell ; On Success - Returns "Lastrow" instead if $Lastrow="LastRow"; use for row number to append for next command ; ** no Error setting yet; I wish it [On Failure - Returns 0 and sets @error = 1] - doesn't, though! ; ;note - current ERROR still frequent is to try to open a worksheet already open ; ;note - current ERROR still frequent is to try to open a named worksheet which does not exist ; ; (eg sheet2 , only 1 present) ; Author(s): Randall ; Related Functions: nil? ; Short Options Use short Syntax ; _XLread($sFilePath,$Sheet,$Column,$Row) ; _XLwrite($sFilePath,$Sheet,$Column,$Row,$ExcelValue) ; _XLwriteCol($sFilePath,$Sheet,$Column,$Row,$ExcelValue) ; _XLadd($sFilePath,$Sheet,$Column,$Row,$ExcelValue) ; _XLmacroRun($sFilePath,$Sheet,$ExcelValue) ; _XLcalc($sFilePath) ; _XLclose($sFilePath,$Save) ; _XLcsvPaste($sFilePath,$Sheet,$Column,$Row,$Save,$csvPath) ; _XLpaste($sFilePath,$Sheet,$Column$Row,$ExcelValue,$Save,$ToColumn) ; _XLexit($sFilePath) [Saves changes and exit Excel] -or _XLexit($sFilePath, "Save") ; _XLexit($sFilePath,$Save) [/ _XLexit($FilePath,"NOSave"); NO changes and exit Excel] ; _XLlastRow($sFilePath,$Sheet) ; _XLsave($sFilePath,$Sheet) ; _XLshow($sFilePath,$Sheet) ; _XLready($sFilePath) ; _XLsort($FilePath,$Column1,$xlAscending,$Column2,$xlAscending,$SortRange) ; ;=============================================================================== ;**************************************************FUNC follows ********************************* func _ExcelCOM($sFilePath,$Sheet=1,$Column="A",$Row=1,$MEExcelCom="Read",$Save="Save",$ExcelValue=1,$Visible=0,$Exit=0,$LastRow=0,$ToColumn=0) Local $numtabs,$ColLettStart,$Range,$Col1,$ColNum1,$Col2,$ColNum2,$oExcel,$Calculate, $var Local $AddressExcel,$RowExcel,$Close, $oCSV ;MsgBox(0,"$Column",$Column) $Column2 = $Column $Row2 = $Row $Range=$Column & $Row if IsString($Sheet) and Number($Sheet)<>0 then $Sheet=Number($Sheet) if IsString($Column) and Number($Column)<>0 then $Column=Number($Column) if IsString($Save) and Number($Save)<>0 then $Save=Number($Save) if IsString($Visible) and Number($Visible)<>0 then $Visible=Number($Visible) if IsString($Exit) and Number($Exit)<>0 then $Exit=Number($Exit) if IsString($LastRow) and Number($LastRow)<>0 then $LastRow=Number($LastRow) if IsString($ToColumn) and Number($ToColumn)<>0 then $ToColumn=Number($ToColumn) ;MsgBox(0,"$Column",$Column) ;MsgBox(0,"$Save",$Save) if $Save="Save" then $Save=1 if $Visible="Visible" then $Visible=1 if $Exit="Exit" then $Exit=1 if $LastRow="LastRow" then $LastRow=1 if $ToColumn="ToColumn" then $ToColumn=1 ;==================================================if all numbers if StringIsDigit($Column) then if $Column<=26 then $ColLettStart=Chr($Column+64) Else $ColLettStart=Chr(int($Column/26)+64)& Chr(mod($Column,26)+64) EndIf $Column=$ColLettStart $Range=$Column & $Row ;MsgBox(0,"StringIsDigit",$Column) ;==================================================if all numbers Else ;==================================================if some numbers if StringRegExp($Column, "[0-9]", 0) then $Range=$Column $ColLettStart=StringRegExpReplace ($Column, "[0-9]","", 0) Else ;==================================================if no numbersand string length too long if StringLen($Column)>2 then $Range=$Column EndIf $Col1=StringLeft($Column,1) $ColNum1=(asc($Col1)-64) if stringlen($Column)>1 then $Col2=StringMid($Column,2,1) $ColNum2=(asc($Col1)-64) $ColNum1=26*$ColNum1+$ColNum2 EndIf $ColLettStart=$ColNum1 ;MsgBox(0,"NOTStringIsDigit/$Column/$ColLettStart",$Column&@CRLF&$ColLettStart) EndIf ;================================================== ;If $Sheet < 1 Then $Sheet = 1 If $Visible <> 1 Then $Visible = 0 If Not FileExists($sFilePath) Or Not StringInStr($sFilePath, "xls") Then msgbox (0,"$sFilePath=",$sFilePath) $sFilePath = FileOpenDialog("ERROR; Go", $sFilePath, "Worksheet" & " (" & "*.xls" & ")", 1);+ $Recurse+ $Recurse EndIf $oExcel = ObjGet ($sFilePath) ; Get an Excel Object from an existing filename If IsObj ($oExcel) Then If $Sheet > $oExcel.Worksheets.count Then $Sheet = $oExcel.Worksheets.count with $oExcel .Windows (1).Visible = 1; Set the first worksheet in the workbook visible .Worksheets ($Sheet).Activate .ActiveSheet.Visible = 1 .Application.Visible = $Visible $xlCalculationManual=-4135 $xlCalculationAutomatic=-4105 $Calculation=.Application.Calculation .Application.Calculation = $xlCalculationManual $RowExcel=$Row if (StringInStr($ExcelValue,@TAB) and StringInStr($MEExcelCom, "Into") and StringInStr($Range, ":")) or $ToColumn<>0 and not StringInStr($MEExcelCom, "Paste") then $ExcelValueArr=StringSplit($ExcelValue,@TAB) if $ToColumn=0 then $MaxNum=256 Else $MaxNum=63536 EndIf if $ToColumn=0 then if $ExcelValueArr[0]>(1+$MaxNum-$ColLettStart) then $ExcelValueArr[0]=1+$MaxNum-$ColLettStart $ColLettEndValue=$ColLettStart+$ExcelValueArr[0] if $ColLettEndValue<=26 then $ColLettEnd=Chr($ColLettEndValue+64) Else $FirstIntVal=int(($ColLettEndValue)/26) $DivBy26=mod($ColLettEndValue,26)=0 $FirstIntVal=$FirstIntVal $SecondVal=($ColLettEndValue-$FirstIntVal*26) +26*$DivBy26 $ColLettEnd=Chr($FirstIntVal-$DivBy26+64)& Chr($SecondVal+64) EndIf $Range=$Range&":"&$ColLettEnd&$RowExcel Else if $ExcelValueArr[0]>(1+$MaxNum-$RowExcel) then $ExcelValueArr[0]=1+$MaxNum-$RowExcel $Range=$Range&":"&$Column&($RowExcel+$ExcelValueArr[0]) EndIf $numtabs=$ExcelValueArr[0] for $j = 0 to $numtabs-1 $ExcelValueArr[$j] = $ExcelValueArr[$j+1] next EndIf Select Case StringInStr($MEExcelCom, "Into") if $ToColumn<>0 then for $j = 0 to $numtabs-1 $Range=$Column&($RowExcel+$j) ;MsgBox(0,"$Range",$Range) .activesheet.range ($Range).value = $ExcelValueArr[$j]; Fill cell numbers next Else .activesheet.range ($Range).value = $ExcelValue ; Fill cell numbers +0 EndIf $var = .activesheet.range ($Range).value Case StringInStr($MEExcelCom, "Paste") .activesheet.range ($Range).select .activesheet.paste ;(.range ($Range)) $var = .activesheet.range ($Range).value Case StringInStr($MEExcelCom, "Show") .activesheet.range ($Range).select Case StringInStr($MEExcelCom, "Add") $var = .activesheet.range ($Range).value .activesheet.range ($Range).value = number($ExcelValue) + $var ; Fill cell numbers $var = .activesheet.range ($Range).value Case StringInStr($MEExcelCom, "Read") $var = .activesheet.range ($Range).value Case StringInStr($MEExcelCom, "Run") .Application.Run($ExcelValue) ;"Normal.NewMacros.Font4" Case StringInStr($MEExcelCom, "Ready") $var = .Application.Ready Case StringInStr($MEExcelCom, "Calc") .Application.Calculation = $xlCalculationAutomatic Case StringInStr($MEExcelCom, "Close") $Close=1 case StringInStr($MEExcelCom, "Import") If Not FileExists($ExcelValue) Or Not StringInStr($ExcelValue, "csv") Then $ExcelValue = FileOpenDialog("Go - Choose your input file as inbuilt one not exists", @ScriptDir, "Comma Delimited Files" & " (" & "*csv" & ")", 1);+ $Recurse+ $Recurse EndIf $oCSV= ObjGet ($ExcelValue) ; Get an Excel Object from an existing filename $oCSV.Application.Visible = $Visible $oCSV.Windows (1).Visible = 1; Set the first worksheet in the workbook visible ;msgbox(0,"",$sFilePath&@CRLF ClipPut(1) $oCSV.activesheet.UsedRange.Copy .Windows (1).Visible = 1; Set the first worksheet in the workbook visible ;msgbox(0,"",$sFilePath&@CRLF .Worksheets ($Sheet).Activate .activesheet.range ($Range).Select .activesheet.paste EndSelect If $Save = "Save" or $Save = 1 Then .Application.Calculation = $Calculation .Save EndIf .Application.Visible = $Visible; Set the application invisible (without this Excel will exit) If $Close = 1 Then .saved=1 .close EndIf If $Exit = 1 Then .saved=1 .Application.Quit While ProcessExists('excel.exe') Sleep(10) WEnd EndIf If IsObj ($oCSV) Then $oCSV.close(0) EndIf if $LastRow= 1 Then .Application.Selection.SpecialCells(11).Select;$xlCellTypeLastCell=11 .Application.Visible = $Visible $AddressExcel = .Application.activecell.address ;;= $ExcelValue $AddressExcel=StringReplace($AddressExcel,"$","") $RowExcel = StringRight($AddressExcel,stringlen($AddressExcel)-1) while StringRegExp($RowExcel, "[A-Z]", 0) $RowExcel = StringRight($RowExcel,stringlen($RowExcel)-1) WEnd $RowExcel=$RowExcel+1 EndIf EndWith Else MsgBox(0, "Excel File Test", "Error: Could not open " & $sFilePath & " as an Excel Object.") EndIf if $LastRow= 1 Then return $RowExcel Else return $var EndIf EndFunc ;==>_ExcelCOM func _XLread($sFilePath,$Sheet=1,$Column="A",$Row=1) $varread=_ExcelCOM($sFilePath,$Sheet,$Column,$Row,"Read","NoSave",4,"NOTVisible","NOTExit","NOTLastRow","NOTToColumn"); read cell "E7" return $varread EndFunc ;==>_XLread func _XLwrite($sFilePath,$Sheet=1,$Column="A",$Row=1,$ExcelValue1=1) if not StringInStr($Column, ":") Then if $ExcelValue1<>"" then ClipPut($ExcelValue1) $varwr=_ExcelCOM($sFilePath,$Sheet,$Column,$Row,"Paste","NoSave",$ExcelValue1,"NOTVisible","NOTExit","NotLastRow","NOTToColumn"); read cell "E7" else $varwr=_ExcelCOM($sFilePath,$Sheet,$Column,$Row,"Into","NoSave",$ExcelValue1,"NOTVisible","NOTExit","NOTLastRow","NOTToColumn"); read cell "E7" EndIf return $varwr EndFunc ;==>_XLwrite func _XLpaste($sFilePath,$Sheet=1,$Column="A",$Row=1,$ExcelValue1="",$Save="Save",$ToColumn="NOTToColumn") ;MsgBox(0,"1=","$ExcelValue1="&$ExcelValue1) if $ToColumn<>0 or $ToColumn="ToColumn" Then $ExcelValue1=StringReplace($ExcelValue1,@CRLF,",") $ExcelValue1=StringReplace($ExcelValue1,@TAB,@CRLF) EndIf ;MsgBox(0,"2=","$ExcelValue1="&$ExcelValue1) if $ExcelValue1<>"" then ClipPut($ExcelValue1) $var=_ExcelCOM($sFilePath,$Sheet,$Column,$Row,"Paste","Save",$ExcelValue1,"NOTVisible","NOTExit","NotLastRow","NOTToColumn"); read cell "E7" return $var EndFunc ;==>_XLpaste func _XLexit($sFilePath,$Save=1) $var=_ExcelCOM($sFilePath,1,"A",1,"calc",$Save,1,"NOTVisible","Exit","NOTLastRow","NOTToColumn"); Exit excel ;return $var EndFunc ;==>_XLexit func _XLlastRow($sFilePath,$Sheet=1) $var2=_ExcelCOM($sFilePath,$Sheet,"A",1,"Read","NoSave",1,"NOTVisible","NOTExit","LastRow","NOTToColumn"); LastRow excel return $var2 EndFunc ;==>_XLlastRow func _XLsave($sFilePath,$Save=1) $var=_ExcelCOM($sFilePath,1,"A",1,"Read","Save",1,"NOTVisible","NOTExit","NotLastRow","NOTToColumn"); LastRow excel ;return $var EndFunc ;==>_XLsave func _XLclose($sFilePath,$Save=1) $var=_ExcelCOM($sFilePath,1,"A",1,"close",$Save,1,"NOTVisible","NOTExit","NotLastRow","NOTToColumn"); LastRow excel ;return $var EndFunc ;==>_XLclose func _XLshow($sFilePath,$Sheet=1,$Column="A",$Row=1) ;$var= _ExcelCOM($sFilePath,$Sheet,$Column,$Row,"Show","NoSave",1,"Visible","NOTExit","NotLastRow","NOTToColumn"); LastRow excel ;return $var EndFunc ;==>_XLshow func _XLmacroRun($sFilePath,$Sheet=1,$ExcelValue="persoNAL.XLS!Macro1") $var=_ExcelCOM($sFilePath,$Sheet,"A",1,"Run","NoSave",$ExcelValue,"NOTVisible","NOTExit","NOTLastRow","NOTToColumn"); read cell "E7" return $var EndFunc ;==>_XLmacroRun func _XLready($sFilePath) $var=_ExcelCOM($sFilePath,1,"A",1,"Ready","NoSave",4,"NOTVisible","NOTExit","NOTLastRow","NOTToColumn"); read cell "E7" return $var EndFunc ;==>_XLready func _XLadd($sFilePath,$Sheet=1,$Column="A",$Row=1,$ExcelValue=1) $var=_ExcelCOM($sFilePath,$Sheet,$Column,$Row,"Add","NoSave",$ExcelValue,"NOTVisible","NOTExit","NOTLastRow","NOTToColumn"); read cell "E7" return $var EndFunc ;==>_XLadd func _XLcalc($sFilePath) $var=_ExcelCOM($sFilePath,1,"A",1,"Calc","NoSave",1,"NOTVisible","NOTExit","NOTLastRow","NOTToColumn"); read cell "E7" return $var EndFunc ;==>_XLcalc func _XLwriteCol($sFilePath,$Sheet=1,$Column="A",$Row=1,$ExcelValue=1) $var=_ExcelCOM($sFilePath,$Sheet,$Column,$Row,"Into","NoSave",$ExcelValue,"NOTVisible","NOTExit","NOTLastRow","ToColumn"); read cell "E7" return $var EndFunc ;==>_XLwriteCol func _XLcsvPaste($sFilePath,$Sheet=1,$Column=1,$Row=1,$Save="Save",$csvPath="test.csv") ; rem must save else may not paste (? calc?) $var=_ExcelCOM($sFilePath,$Sheet,$Column,$Row,"Import","Save",$csvPath,"NotVisible","NOTExit","NotLastRow","NOTToColumn"); LastRow excel return $var EndFunc ;==>_XLcsvPaste func _XLsort($sFilePath,$Columns=1,$Direction1=1,$Columns2=1,$Direction2=1,$SortRange="UsedRange") If Not FileExists($sFilePath) Or Not StringInStr($sFilePath, "xls") Then $FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $sFilePath, "Worksheet" & " (" & "*.xls" & ")", 1) EndIf $xlAscending = 1 $xlDescending = 2 $xlGuess = 0 $xlTopToBottom = 1 if $SortRange<>"" then $SortRange="Range("&'"'&$SortRange&'"'&")" Else $SortRange="UsedRange" EndIf $code= "Sub XLSort(FilePath, Columns1, Direction1,Columns2,Direction2)" $code=$code & @CRLF & "Dim oXL" $code=$code & @CRLF & "Set oXL = GetObject(FilePath)" $code=$code & @CRLF & "With oXL" $code=$code & @CRLF & ".Windows (1).Visible = 1" $code=$code & @CRLF & ".Worksheets (1).Activate" $code=$code & @CRLF & ".ActiveSheet.Visible = 1" $code=$code & @CRLF & ".Application.Visible = 0" $code=$code & @CRLF & "Set objRange1 =.ActiveSheet.Range(Columns1)" $code=$code & @CRLF & "Set objRange2 =.ActiveSheet.Range(Columns2)" $code=$code & @CRLF & ".ActiveSheet."&$SortRange&".Select" $code=$code & @CRLF & ".Application.Selection.Sort objrange1, Direction1,objRange2,,Direction2,,,0,2, False,1" $code=$code & @CRLF & ".save" $code=$code & @CRLF & "End with" $code=$code & @CRLF & "End Sub" $vbs = ObjCreate("ScriptControl") $vbs.language="vbscript" $vbs.addcode($code) $retour = $vbs.run("XLSort",$sFilePath,$Columns,$Direction1,$Columns2,$Direction2) EndFunc ;==>_XLsort