Sign in to follow this  
Followers 0
HockeyFan

Open Excel File

5 posts in this topic

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!

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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:

#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


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

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:

#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! :whistle:

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0