ExcelCOM UDF

[blitzkrg]

i'm sorry, i really wasn't trying to be critical at all, and meant no offense, to you or any of the others that have been keeping this project going. i was just trying to suggest what i thought might be a little simpler to write, and alot easier to read. the one i put up is going to be in my excel udf if i ever get some time at work to finish it. they're talking about promoting me again this week, and then i'm giong to have to automate a whole new job before i can get active with it again

No need to be sorry..No offense was taken

I'm very novice with COM stuff.. but i can modify existing code just fine

[heibel]

Hi Randallc,

could you zip everything (first post) in one single zip?

That would be great, thanks in advance!

Edited June 15, 2006 by heibel

[blitzkrg]

Hi Randallc,

could you zip everything (first post) in one single zip?

That would be great, thanks in advance!

all you need is the main udf and if you want the functions recently created, they are in the last page or 2

[randallc]

OK, zipped in first post .

Best, Randall

[randallc]

Hi,

New version in first post.

2_95 June17th 06 ; FontSize @blitzkrg; suppress macro security warning; Checkbox read tristate @cameronsdad

1. Disables popup messages (eg about macro security level)

2. FontSize

3. CheckBoxState

Best, randall

[SandelPerieanu]

How can i refresh data for a querytable?

[randallc]

How can i refresh data for a querytable?

Hi,

Show me the vba macro and I'll try to convert it!

Else use XLmacroRun ... your macro

Best, Randallc

[SandelPerieanu]

http://msdn2.microsoft.com/en-us/library/m...le.refresh.aspx

$oexcel = ObjCreate ("Excel.Application")
$oexcel.visible = 1
$osheet = $oExcel.Application.WorkBooks.Open (@ScriptDir & '\Raport.xls')
$a=$oExcel.Worksheets (2).Activate
;For $i=1 to 3
;With $oexcel
 ;  .Worksheets ($i).Activate
; For $pivot In $osheet.pivotcaches
;  $pivot.refresh
; Next
; .ActiveSheet.Cells.EntireColumn.AutoFit ()
;EndWith
;Next

Edited July 17, 2006 by psandu.ro

[blitzkrg]

@randallc

EDIT 2 : randallc - hold off on this request, i think Valik may have found a fix for this problem.

thanks

can you look at this post

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

and the original post (link to original post is in the thread in the above link)

is there something we can add/modify in the UDF to make it work again?

basically when i do an XLread and then an XLwrite

the information read is NOT the same that is written.

so somewhere along the line something isnt compatiable in the UDF with the new compiler (based on Valik's information)

but i'm afraid the cause is a little over myhead at this point.

just wondering if you can figure it out?

thanks

EDIT: I added the script from the original threads so you dont have to go back and forth

#include<ExcelCom.au3>
$app1 = FileGetShortName(@DesktopDir & "\test1.xls")
$var1 = _XLread ($app1,1,"A",1,1)
$var2 = _XLWrite($app1,1,"B",1,$var1,1)
msgbox(0,"debug",$var1)
exit

---------

in cell A 1 type 100886078 and then run the script

and see what the results in B are?

Edited August 23, 2006 by blitzkrg

[joshiieeii]

Been racking my brain trying to figure this one out....

How would I insert a hyperlink in excel??

I guess I could script a CTRL + K and then insert the text into the popup and then hit Enter, but that is too rough. Anyone have any ideas?

[blitzkrg]

Been racking my brain trying to figure this one out....

How would I insert a hyperlink in excel??

I guess I could script a CTRL + K and then insert the text into the popup and then hit Enter, but that is too rough. Anyone have any ideas?

I'm sure there is a better way to do this, but here is a quick & dirty way

$url = your url, dont put the http://

$fname = friendly name, could use like "click here"

this assumes you want to enter a web address

you could modify to do UNC if you need to

#include<ExcelCom.au3>
$app1 = FileGetShortName(@DesktopDir & "\test1.xls")
$url = "www.autoitscript.com"
$fname = "AutoIT Home Page"
$var2 = _XLWriteurl($app1,1,"B",1,$url & '"' & chr(44) & '"' & $fname,1)
exit


Func _XLWriteurl(ByRef $s_FilePath, $s_i_Sheet = 1, $s_i_Column = "A", $i_Row = 1, $ExcelValue1 = 1, $s_i_Visible = 0)
    $varwr = _ExcelCOM($s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, "Into", "NoSave",'=HYPERLINK("http://' & $ExcelValue1 & '")', $s_i_Visible, "NOTExit", "NOTLastRow", "NOTToColumn"); read cell "E7"
    Return $varwr
EndFunc  ;==>_XLWriteurl

[joshiieeii]

I'm sure there is a better way to do this, but here is a quick & dirty way

$url = your url, dont put the http://

$fname = friendly name, could use like "click here"

this assumes you want to enter a web address

you could modify to do UNC if you need to

#include<ExcelCom.au3>
$app1 = FileGetShortName(@DesktopDir & "\test1.xls")
$url = "www.autoitscript.com"
$fname = "AutoIT Home Page"
$var2 = _XLWriteurl($app1,1,"B",1,$url & '"' & chr(44) & '"' & $fname,1)
exit
Func _XLWriteurl(ByRef $s_FilePath, $s_i_Sheet = 1, $s_i_Column = "A", $i_Row = 1, $ExcelValue1 = 1, $s_i_Visible = 0)
    $varwr = _ExcelCOM($s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, "Into", "NoSave",'=HYPERLINK("http://' & $ExcelValue1 & '")', $s_i_Visible, "NOTExit", "NOTLastRow", "NOTToColumn"); read cell "E7"
    Return $varwr
EndFunc ;==>_XLWriteurl

Thanks a bunch, that was it!!

[randallc]

Hi,

@Blitzkrg, thanks! - I guess there is a macro for this and a proper function as well;

Or, to modify;

;XLhyperlink.au3
#include<ExcelCom.au3>
$app1 = FileGetShortName(@DesktopDir & "\test1.xls")
$LinkSplit = "http://www.autoitscript.com|AutoIT Home Page2"
$var2 = _XLWriteurl($app1,1,"B",1,$LinkSplit,1)
exit
Func _XLWriteurl(ByRef $s_FilePath, $s_i_Sheet = 1, $s_i_Column = "A", $i_Row = 1, $ExcelValue1 = 1, $s_i_Visible = 0)
    $arExcelValue1=StringSplit($ExcelValue1,"|")
    $sNewExcelVal='=HYPERLINK("' & $arExcelValue1[1] & '"' & chr(44) & '"' & $arExcelValue1[2] & '")'
    $varwr = _ExcelCOM($s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, "Into", "NoSave",$sNewExcelVal, $s_i_Visible, "NOTExit", "NOTLastRow", "NOTToColumn"); read cell "E7"
    Return $varwr
EndFunc ;==>_XLWriteurl

Best, Randall

[Simucal]

Maybe I'm blind.. but I dont see a link to the main ExcelCom.au3 on the front page/first post anymore. I just see a ton of example au3's?

[randallc]

Hi,

I think it is still in the zip at the top? [running out of allowed space on forum and I have had to zip any big file..... else I cannot post attachments]

Best, Randall

[wysocki]

Can the ExcelCom UDF be used along with creating an Excel object in the same program? Here's some lines I extracted from a big app I'm writing. The ExcelCom lines get the number of worksheets in the workbook then later in the program, I create an Excel object to do more dirty work. When I have the ExcelCom lines in the program, there's always Excel.exe in the Task Manager when it's done running.

#include <ExcelCom.au3>
$fname='c:\apps\sue\book1.xls'
$XLSheetProps=_XLSheetProperties( $fname,0)
$nSheets = Ubound($XLSheetProps,2) -1
msgbox(1,'Sheets',$nSheets);the above uses the ExcelCom.au3 functions
$oExcel = Objcreate("Excel.Application");Now create an actual Excel object
$oExcel.Visible = 1 
$oExcel.Workbooks.Open($fname, 0, 1)
$objXLBook = $oExcel.ActiveWorkbook
$oExcel.Sheets('Sheet2').Select
$oExcel.Application.activesheet.PrintOut()
$objXLBook.Close(0)
$oExcel.quit;After this runs, there's still excel.exe in task manager

[randallc]

Hi,

"_XLClose( $fname,0)" b4 the msgbox

Works for me?

Best, Randall

[MythBusted]

Hi folks,

I'm trying to find how to take an active sheet in Excel and move it to the beginning of the workbook. Has anyone had to do something like that, and could explain it to me?

Thanks!

[ptrex]

Hi folks,

I'm trying to find how to take an active sheet in Excel and move it to the beginning of the workbook. Has anyone had to do something like that, and could explain it to me?

Thanks!

Something like this will get you started :

$oExcel.Sheets("Sheet3").Select

$oExcel.Sheets("Sheet3").Move Before:=Sheets(1)

regards

ptrex

[MythBusted]

Great! Thanks so much for your help!