Open Excel File

[HockeyFan]

Is anyone familiar with how to script the opening of an Excel file and then adding data to the first available row?

This is what I have so far and it's not working:

$file = FileOpen("C:\Info.xls", 1)
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
Wend

FileWrite($file, @UserName & @CR & $TypeName & @CR & @ComputerName & @CR & @IPAddress1 & @CR & @IPAddress2 & @CR & @OSVersion & @CR & @OSServicePack & "," & @MON & "/" & @MDAY & "/" & @YEAR)
FileClose($file)

It seems to get stuck in the While Loop...for what I can tell...and never writes to the file.

Thanks for the help!

[Kohr]

ExcelCOM udf has lots of examples on how to work with excel.

http://www.autoitscript.com/forum/index.php?showtopic=34302

Kohr

[tresa]

Is anyone familiar with how to script the opening of an Excel file and then adding data to the first available row?

This is what I have so far and it's not working:

$file = FileOpen("C:\Info.xls", 1)
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
Wend

FileWrite($file, @UserName & @CR & $TypeName & @CR & @ComputerName & @CR & @IPAddress1 & @CR & @IPAddress2 & @CR & @OSVersion & @CR & @OSServicePack & "," & @MON & "/" & @MDAY & "/" & @YEAR)
FileClose($file)

It seems to get stuck in the While Loop...for what I can tell...and never writes to the file.

Thanks for the help!

Excel files are not text files, so the @error is set to 1 then the while loop is not ending

[Locodarwin]

Grab the UDF at the link in my signature. Place that into the same directory as your script, and swap your snippet with mine.

This is my best approximation of what your script does:

expandcollapse popup

#include <ExcelCOM_UDF.au3>

;$TypeName = "Typename" ;I put this in for testing purposes - it's not defined in your snippet

$oBook = _ExcelBookOpen("C:\Info.xls")
If @error = 2 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf

$aUsedRange = _ExcelSheetUsedRangeGetA($oBook, 1)

$aLine = _ExcelReadArray($oBook, 1, 1, $aUsedRange[2], 0, 0)  ; The $line from your snippet is now an array

_ExcelWriteCell($oBook, @UserName, 2, 1)
_ExcelWriteCell($oBook, $TypeName, 3, 1)
_ExcelWriteCell($oBook, @ComputerName, 4, 1)
_ExcelWriteCell($oBook, @IPAddress1, 5, 1)
_ExcelWriteCell($oBook, @IPAddress2, 6, 1)
_ExcelWriteCell($oBook, @OSVersion, 7, 1)
_ExcelWriteCell($oBook, @OSServicePack, 8, 1)
_ExcelWriteCell($oBook, @MON & "/" & @MDAY & "/" & @YEAR, 9, 1)

_ExcelBookClose($oBook)

Exit

Func _ExcelSheetUsedRangeGetA($oExcel, $vSheet)
    Local $aSendBack[4], $sTemp, $aSheetList, $fFound = 0
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If IsNumber($vSheet) Then
        If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)
    Else
        $aSheetList = _ExcelSheetList($oExcel)
        For $xx = 1 To $aSheetList[0]
            If $aSheetList[$xx] = $vSheet Then $fFound = 1
        Next
        If NOT $fFound Then Return SetError(3, 0, 0)
    EndIf
    $oExcel.ActiveWorkbook.Sheets($vSheet).Select
    $aSendBack[0] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address
    $aSendBack[1] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
    $aSendBack[0] = StringReplace($aSendBack[0], "$", "")
    $sTemp = StringSplit($aSendBack[1], "C")
    $aSendBack[2] = Number($sTemp[2])
    $aSendBack[3] = Number(StringRegExpReplace($aSendBack[0], "\a", ""))
    If $aSendBack[0] = "A1" And $oExcel.Activesheet.Range($aSendBack[0]).Value = "" Then $aSendBack[0] = 0
    Return $aSendBack
EndFunc;==>_ExcelSheetUsedRangeGet

That should get you started. You'll want to check out the headers for my functions in the UDF for more information on how to use them to automate your Excel tasks.

-S

[HockeyFan]

Grab the UDF at the link in my signature. Place that into the same directory as your script, and swap your snippet with mine.

This is my best approximation of what your script does:

expandcollapse popup

#include <ExcelCOM_UDF.au3>

;$TypeName = "Typename" ;I put this in for testing purposes - it's not defined in your snippet

$oBook = _ExcelBookOpen("C:\Info.xls")
If @error = 2 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf

$aUsedRange = _ExcelSheetUsedRangeGetA($oBook, 1)

$aLine = _ExcelReadArray($oBook, 1, 1, $aUsedRange[2], 0, 0)  ; The $line from your snippet is now an array

_ExcelWriteCell($oBook, @UserName, 2, 1)
_ExcelWriteCell($oBook, $TypeName, 3, 1)
_ExcelWriteCell($oBook, @ComputerName, 4, 1)
_ExcelWriteCell($oBook, @IPAddress1, 5, 1)
_ExcelWriteCell($oBook, @IPAddress2, 6, 1)
_ExcelWriteCell($oBook, @OSVersion, 7, 1)
_ExcelWriteCell($oBook, @OSServicePack, 8, 1)
_ExcelWriteCell($oBook, @MON & "/" & @MDAY & "/" & @YEAR, 9, 1)

_ExcelBookClose($oBook)

Exit

Func _ExcelSheetUsedRangeGetA($oExcel, $vSheet)
    Local $aSendBack[4], $sTemp, $aSheetList, $fFound = 0
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If IsNumber($vSheet) Then
        If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)
    Else
        $aSheetList = _ExcelSheetList($oExcel)
        For $xx = 1 To $aSheetList[0]
            If $aSheetList[$xx] = $vSheet Then $fFound = 1
        Next
        If NOT $fFound Then Return SetError(3, 0, 0)
    EndIf
    $oExcel.ActiveWorkbook.Sheets($vSheet).Select
    $aSendBack[0] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address
    $aSendBack[1] = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
    $aSendBack[0] = StringReplace($aSendBack[0], "$", "")
    $sTemp = StringSplit($aSendBack[1], "C")
    $aSendBack[2] = Number($sTemp[2])
    $aSendBack[3] = Number(StringRegExpReplace($aSendBack[0], "\a", ""))
    If $aSendBack[0] = "A1" And $oExcel.Activesheet.Range($aSendBack[0]).Value = "" Then $aSendBack[0] = 0
    Return $aSendBack
EndFunc;==>_ExcelSheetUsedRangeGet

That should get you started. You'll want to check out the headers for my functions in the UDF for more information on how to use them to automate your Excel tasks.

-S

Thank you so much!!

I really appreciate the help!