Trying to write to Excel Using Script

[kpu]

Yes I've seen the UDF here http://www.autoitscript.com/forum/index.ph...727entry96727 But it doesn't work for me possible due to not having office 2003.

Here's what I'm trying to do:

1. Read line by line from File 1

2. Open file 2 and try and find the line from File 1

3. If it CAN match the data, I wan't it to write to an excel workbook. Row1, Col1 and Col2

4. Read next line in File 1 and find it in file 2.

5 If it CAN match the data, I wan't it to write to an excel workbook. Row2, Col1 and Col2...etc.

My issue is that it keeps opening a new workbook and I haven't figured out how to get it to write to just the work book I have it opening. Right now I have the Code in the loop so I understand why it wants to open it again and again, and again..., but I can't figure out where to place it so it work correclty.

Please see _WriteExcelInformation() as this is where the code needs some tweaking.

Any help would greatly be appreciated!

expandcollapse popup

Dim $line, $line2
$row_1 = 1;add to this one
$col_1 = 1
$row_2 = 1;add to this one
$col_2 = 2
$xlsFile = @ScriptDir & "\testing.xls"

;######################## Get File One
$1file = FileOpenDialog("", @ScriptDir, "Text File (*.txt)")
If @error Then Exit
;######################## Get File Two
$2file = FileOpenDialog("", @ScriptDir, "Text File (*.txt)")
If @error Then Exit
    
;######################## Open File One
$file = FileOpen($1file, 0)
If $file = -1 Then
    MsgBox(16, "Error", "Unable to open " & $1file)
    Exit
EndIf
While 1
    $line = FileReadLine($file)
    If @error = -1 Then ExitLoop
    _CompareFile()
WEnd
FileClose($file)

Func _CompareFile()
    $file2 = FileOpen($2file, 0)
    If $file2 = -1 Then
        MsgBox(16, "Error", "Unable to open " & $1file)
        Exit
    Else
    EndIf
    While 1
        $line2 = FileReadLine($file2)
        If @error = -1 Then ExitLoop
        If $line == $line2 Then
        ;MsgBox(32,"Found","File 1: " & $line & " File 2: " & $line2)
            _WriteExcelInformation()
        Else
        ;Future (Write to another sheet with Unfound Name)
        EndIf
    WEnd
    FileClose($file2)
EndFunc  ;==>_CompareFile
Func _WriteExcelInformation()
    MsgBox(32, "Found", "File 1: " & $line & " File 2: " & $line2)
    $objExcel = ObjCreate("Excel.Application")
    $objWorkbook = $objExcel.Workbooks.Open ($xlsFile)
;$objExcel = ObjGet($xlsFile, "Excel.Application")
    $objExcel.Visible = 1
    $objExcel.Workbooks.Add ()
    $objExcel.Cells ($row_1, $col_1).Value = $line
    $objExcel.Cells ($row_2, $col_2).Value = $line2
    $row_1 = $row_1 + 1
    $row_2 = $row_2 + 1
EndFunc  ;==>_WriteExcelInformation

[kpu]

No one has any ideas?

[kpu]

Okay. I got it working.

Func _WriteExcelInformation()
    MsgBox(32, "Found", "File 1: " & $line & " File 2: " & $line2)
    $objExcel = ObjCreate("Excel.Application")
    $objExcel.Visible = 0
    $objWorkbook = $objExcel.Workbooks.Open($xlsFile)
    $objExcel.Cells ($row_1, $col_1).Value = $line
    $objExcel.Cells ($row_2, $col_2).Value = $line2
    $objExcel.DisplayAlerts = 0
    $objExcel.ActiveWorkbook.Save();As ("c:\testing.xls")
    $objExcel.ActiveWorkbook.Close()
    $objExcel.Quit()    
    $row_1 = $row_1 + 1
    $row_2 = $row_2 + 1

EndFunc  ;==>_WriteExcelInformation