Sign in to follow this  
Followers 0
evg64

Writing an UDF to work with Excel

11 posts in this topic

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?)

Share this post


Link to post
Share on other sites



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.

Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

Share this post


Link to post
Share on other sites

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.


While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

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

Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

Share this post


Link to post
Share on other sites

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?))

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by someone

While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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".

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 by aslani

[font="Georgia"]Chances are, I'm wrong.[/font]HotKey trouble?Stringregexp GuideAutoIT Current Version

Share this post


Link to post
Share on other sites

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.


Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

Share this post


Link to post
Share on other sites

@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

Certifications: A+, Network+, Security+, Linux+, LPIC-1, MCSA | Languages: AutoIt, C, SQL, .NETBooks: AutoIt v3: Your Quick Guide - $7.99 - O'Reilly Media - September 2007-------->[u]AutoIt v3 Development - newbie to g33k[/u] - Coming Soon - Fate Publishing - Spring 2013UDF Libraries: SkypeCOM UDF Library | ADUC Computers OU Cleanup | Find PixelChecksumExamples: Skype COM Examples - Skype4COMLib Examples converted from VBS to AutoIt

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