Writing an UDF to work with Excel

[evg64]

Is it possible to write something like Filewrite() function, but for Excel? I know, it is possible to create an Excel object and write data to specific cells. But how to fill excel cells without creating an excel object? Filewrite() works right in this way, but with text files. Anybody has an idea?)

[Airwolf]

Is it possible to write something like Filewrite() function, but for Excel? I know, it is possible to create an Excel object and write data to specific cells. But how to fill excel cells without creating an excel object? Filewrite() works right in this way, but with text files. Anybody has an idea?)

You could do it with the Office 2007 format since it is XML based. Anything previous, however, is going to be a proprietary Microsoft format.

[someone]

Why doesn't the excel com UDF (uses objects) work for you? What are you trying to avoid doing by not interacting with excel that way?

I don't think you can do something like filewrite for excel that doesn't interact with objects.

[Airwolf]

Why doesn't the excel com UDF (uses objects) work for you? What are you trying to avoid doing by not interacting with excel that way?

I don't think you can do something like filewrite for excel that doesn't interact with objects.

Interacting with Excel is somewhat slow, I imagine he is trying to speed things up a bit. muttley

[evg64]

It wont be very convenient to have a lot of excel windows like I`m going to. If to make them invisible, how to close them then?))

[someone]

If thats the case, you absolutely can use the excel com UDF. When use $oExcel = _ExcelBookOpen(), you can then use the same $oExcel to reference that instance of excel in closing. Just use _ExcelBookClose($oExcel).

If you are opening multiple Excel workbooks, you need to give them different variables, so just $oExcel1, $oExcel2...etc. Then close each on accordingly. You can do all of in silently.

PS Airwolf do you know what causes working with Excel to be slow? Like is it autoit, interacting excels com interface, or what?

Edited July 9, 2008 by someone

[litlmike]

Is it possible to write something like Filewrite() function, but for Excel? I know, it is possible to create an Excel object and write data to specific cells. But how to fill excel cells without creating an excel object? Filewrite() works right in this way, but with text files. Anybody has an idea?)

I do not the answer for sure, but my guess is you are going to have to work with the Excel file as a Database to do this the way you are wanting to. But, if you were to do that, then why not just work with it as a Database and skip the Excel file altogether? Take a look at the signatures of ptrex and LocoDarwin before you spend too much time on this, they probably have already done enough for your UDF. If not, it is my guess that there is a better way for you to accomplish what you want to do. Good luck either way!

[aslani]

It wont be very convenient to have a lot of excel windows like I`m going to. If to make them invisible, how to close them then?))

Here's a function I wrote that deals with Excel files without opening and closing them, everything is done "under the sheets".

expandcollapse popup

Func _cList()
    WinActivate($title)
    
    Dim $aList[1][5]
    
    $i_path_1 = FileOpenDialog("Load Forms", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)", 4)
    $i_path_2 = FileOpenDialog("Create List or Add to a List", @DesktopDir & "\", "MS Excel Files (*.xl*;*.xls)")
    
    $aFiles = StringSplit($i_path_1, '|')
    
    ;_ArrayDisplay ($aFiles, "$aFiles") ; for debug
    
    $mousePos = MouseGetPos()
    
    _MouseTrap($mousePos[0], $mousePos[1], $mousePos[0], $mousePos[1])
    
    $prog_gui = GuiCreate("Please wait", 500, 100, -1, $guiloc_y-100, $DS_MODALFRAME + $DS_SETFOREGROUND)
    $prog_bar1 = GUICtrlCreateProgress ($offset_1, 5, 475, 10, $PBS_SMOOTH)
    $prog_bar2 = GUICtrlCreateProgress ($offset_1, 20, 475, 10, $PBS_SMOOTH)
    $prog_label = GUICtrlCreateLabel ("", $offset_1, 35, 475, 25)
    GUISetState (@SW_SHOW, $prog_gui)
    $prog_perc1 = 0
    $prog_perc2 = 0
    
    For $ia = 0 To Ubound($aFiles) - 3
        $cOrig = 26 ;26 or 28
        ;If Ubound($aFiles) = 2 Then
        ;   MsgBox(0, "", "$aFiles[0] = 1")
        ;   Local $rExcel = _ExcelBookOpen($aFiles[1], 1)
        ;Else
            Local $rExcel = _ExcelBookOpen($aFiles[1] & "\" & $aFiles[$ia+2], 0)
        ;EndIf
        GUICtrlSetData ($prog_label, "Reading..." & @CRLF & $aFiles[1] & "\" & $aFiles[$ia+2])
        $aList[$ia][0] = _ExcelReadCell($rExcel, "C5")              ; description
        $aList[$ia][1] = _ExcelReadCell($rExcel, "C7")              ; manufacturer
        $aList[$ia][2] = _ExcelReadCell($rExcel, "C9")              ; mfg partnumber
        $aList[$ia][3] = _ExcelReadCell($rExcel, "C" & $cOrig)      ; originator name
        $ord = _ExcelReadCell($rExcel, "P" & $cOrig)                ; originator date
        _ExcelBookClose($rExcel)
        
        $ord = StringLeft(String($ord), 8)
        $ordm = StringRight(StringLeft($ord, 6), 2)
        $ordd = StringRight($ord, 2)
        $ordy = StringLeft($ord, 4)
        $aList[$ia][4] = $ordm & "/" & $ordd & "/" & $ordy
        
        ReDim $aList[UBound($aList) + 1][Ubound($aList, 2)]
        $prog_perc1 += 100 / (Ubound($aFiles) - 3)
        GUICtrlSetData ($prog_bar1, $prog_perc1)
    Next
    
    For $ib = 0 To UBound($aList) - 1
        $cl = 2
        GUICtrlSetData ($prog_label, "Writing..." & @CRLF & $i_path_2)
        Local $oExcel = _ExcelBookOpen($i_path_2, 0)
        
        While _ExcelReadCell($oExcel, "B" & $cl) <> ""
            $cl = $cl+1
        WEnd
        
        _ExcelWriteCell($oExcel, $aList[$ib][0], "B" & $cl)     ; description
        _ExcelWriteCell($oExcel, $aList[$ib][1], "C" & $cl)     ; manufacturer
        _ExcelWriteCell($oExcel, $aList[$ib][2], "D" & $cl)     ; mfg partnumber
        _ExcelWriteCell($oExcel, $aList[$ib][3], "J" & $cl)     ; originator name
        _ExcelWriteCell($oExcel, $aList[$ib][4], "K" & $cl)     ; originator date
        
        _ExcelBookClose($oExcel)
        $prog_perc2 += 100 / Ubound($aList)
        GUICtrlSetData ($prog_bar2, $prog_perc2)
    Next
    
    _MouseTrap()
    
    MsgBox(0, "Done.", "Task is complete.")
    GUISetState (@SW_HIDE, $prog_gui)
EndFunc

EDIT: typo

Edited July 9, 2008 by aslani

[Airwolf]

PS Airwolf do you know what causes working with Excel to be slow? Like is it autoit, interacting excels com interface, or what?

It's just as slow as if you had to launch Excel and do the work yourself, because that is what it's doing.

@aslani - That script still uses the ExcelCOM UDF library and launches an Excel instance in the background.

@evg64 - Look into modifying *.xlsx format (Excel 2007). I haven't dealt with it much yet, but I understand that it is basically just XML which you could easily modify without opening the file in Excel.

[ptrex]

@all

Excel uses BIFF format to create the files.

Maybe this can get you started Excel File format

regards

ptrex

[Airwolf]

@all

Excel uses BIFF format to create the files.

Maybe this can get you started Excel File format

regards

ptrex

Thanks ptrex. I learn something everyday on these forums. muttley