AutoHotkey Community

It is currently May 25th, 2012, 12:10 pm

All times are UTC [ DST ]




Post new topic Reply to topic  [ 132 posts ]  Go to page Previous  1, 2, 3, 4, 5 ... 9  Next
Author Message
 Post subject:
PostPosted: July 23rd, 2007, 9:48 am 
Offline

Joined: March 16th, 2005, 10:33 pm
Posts: 968
Location: Frisia
Great examples. Can you explain a bit more what the values represent that you used?

Code:
Invoke_(pcls, "Item=", 3, 1, 3, 2, 8, "Test12")


Could you create an example which reads from an Excel file too? That would be very helpful. Thanks.

_________________
Image mirror 1mirror 2mirror 3ahk4.me • PM or Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 1:50 pm 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
daonlyfreez wrote:
Great examples. Can you explain a bit more what the values represent that you used?
Code:
Invoke_(pcls, "Item=", 3, 1, 3, 2, 8, "Test12")

Notice that it used Invoke_(), which is supposed to be used in some rare cases where need to specify the types too.
But, "Item" should not be included in some rare cases, as it's the default member of many COM objects.
So, I'm not satisfied to use Invoke_() for "Item", but it's rather complicated to cover all possible cases without specifying the types.

Anyway, I attempted and updated CoHelper.ahk, so as to cover at least the Method/PropertyGet with the integer index parameter:

Code:
#Include CoHelper.ahk

CoInitialize()
pxl := ActiveXObject("Excel.Application")

pawb := Invoke(Invoke(pxl, "Workbooks"), "Add")
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")

Invoke(pcls, "Item=", 1, 2, "Test12")
Invoke(pcls, "Item=", 2, 1, "Test21")

Invoke(pawb, "SaveAs", A_Temp . "\test.xls")
Invoke(pxl, "Quit")
Release(pxl)
CoUninitialize()


Quote:
Could you create an example which reads from an Excel file too? That would be very helpful. Thanks.

I'll do it later.


Last edited by Sean on July 24th, 2007, 2:03 am, edited 1 time in total.

Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 2:20 pm 
Offline

Joined: March 16th, 2005, 10:33 pm
Posts: 968
Location: Frisia
Your example with the updated CoHelper.ahk doesn't work for me (Excel 2000 v. 9.0.6926 SP-3) :(

_________________
Image mirror 1mirror 2mirror 3ahk4.me • PM or Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 2:43 pm 
Offline

Joined: May 20th, 2007, 7:48 pm
Posts: 48
daonlyfreez wrote:
Your example with the updated CoHelper.ahk doesn't work for me (Excel 2000 v. 9.0.6926 SP-3)
What does it do, or not do? I run the same version of Excel on XPSP2 and it works fine here.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 2:58 pm 
Offline

Joined: March 16th, 2005, 10:33 pm
Posts: 968
Location: Frisia
:oops:

My bad...

I forgot changing "Sheet1" to "Tabelle1" (I'm working on a German system).

_________________
Image mirror 1mirror 2mirror 3ahk4.me • PM or Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 3:28 pm 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
daonlyfreez wrote:
I forgot changing "Sheet1" to "Tabelle1" (I'm working on a German system).

This is a good example of "Item" with the string parameter and the integer index parameter. Actually, I used the string parameter instead of the index parameter to avoid using Invoke_().
Now, the following can be replaced

Code:
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", "Sheet1")

with

Code:
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)


BTW, what do you guys think about omitting member function name "Item" in the index parameter cases?
Was it a good or a bad decision?


Last edited by Sean on July 24th, 2007, 2:05 am, edited 1 time in total.

Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 4:04 pm 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
Another example:

Code:
#Include CoHelper.ahk

CoInitialize()
pxl := ActiveXObject("Excel.Application")

pawb := Invoke(Invoke(pxl, "Workbooks"), "Open", A_Temp . "\test.xls")
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")

Invoke(Invoke(pcls, "Item", 1, 1), "Value=", "Test11")
Invoke(Invoke(pcls, "Item", 2, 2), "Value=", "Test22")

Invoke_(pawb, "Save")
Invoke(pxl, "Quit")
Release(pxl)
CoUninitialize()


Last edited by Sean on July 24th, 2007, 2:06 am, edited 1 time in total.

Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 4:07 pm 
Offline

Joined: March 16th, 2005, 10:33 pm
Posts: 968
Location: Frisia
Thanks for the example.

I have no idea if leaving the function name and sheet name out is good or bad. Would it be problematic to access a sheet by name now?

I'm really not savvy at this, just experimenting a bit, but being able to read/write from Excel directly would be very useful (right now I save sheets as csv, parse them, write back to csv, and convert from csv to xls).

_________________
Image mirror 1mirror 2mirror 3ahk4.me • PM or Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 6:02 pm 
Offline

Joined: May 20th, 2007, 7:48 pm
Posts: 48
Sean wrote:
BTW, what do you guys think about omitting member function name "Item" in the index parameter cases?
Was it a good or a bad decision?
The only problem I have with it will be remembering to use it six months from now. It's easy to remember when something doesn't work with Invoke() to just switch to Invoke_(). That still works. It's harder to remember a new syntax just for "Item" when it's an index. A note in CoHelper would be a useful reminder for the future since unwinding the code takes a few minutes.

daonlyfreez wrote:
I'm really not savvy at this, just experimenting a bit, but being able to read/write from Excel directly would be very useful (right now I save sheets as csv, parse them, write back to csv, and convert from csv to xls).
Say goodbye to your old ways of handling Excel data. This is so much simpler and faster. I'm currently translating some of my old Access Visual Basic macros into AHK. Gone are the headaches of moving data between the database and AHK.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 23rd, 2007, 11:25 pm 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
daonlyfreez wrote:
I have no idea if leaving the function name and sheet name out is good or bad. Would it be problematic to access a sheet by name now?

The problem is that you have to specify the function name "Item" if using string parameter, but should omit if using the integer parameter.
This is unavoidable as there is no way for Invoke() to differentiate whether a string is a function name or a parameter.
It's also based on the assumption that there is no function name purely consist of digits, and we're never gonna use the dispID directly.
I'll continue in the following reply.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 24th, 2007, 12:08 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
tfcahm wrote:
Sean wrote:
BTW, what do you guys think about omitting member function name "Item" in the index parameter cases?
Was it a good or a bad decision?
The only problem I have with it will be remembering to use it six months from now. It's easy to remember when something doesn't work with Invoke() to just switch to Invoke_(). That still works. It's harder to remember a new syntax just for "Item" when it's an index. A note in CoHelper would be a useful reminder for the future since unwinding the code takes a few minutes.

Count, Item, _NewEnum are special member names. So, "Item" is omitted frequently like:
Worksheets("Sheet1"), Worksheets(1), Cells(1,2), etc.
However, looks like it was a bad decision to omit the name "Item" only for the integer index parameters. I'll restore it to specify the name "Item" even with the integer index.
However, there still remains discrepancy between the string & the integer index parameters: can simply use Invoke() with the string, but should use Invoke_() with the integer parameter.
And, I'd really like to avoid using Invoke_() with the "Item" function as it's one of the special members in COM.

I suppose a way with the least risk would be that if a parameter is recognized as an integer don't do type coercion into bstring but do specify it as one of the integer types. It would also reduce substantially further the need of Invoke_().
One problem with it, however, is what integer type should be used.
integer (:VT_I4) or (U)int64 (:VT_I8 or VT_UI8) or etc.
VT_I4 may be the safest bet, whereas VT_UI8 may be the most generic.

Although the types didn't really matter in my quick tests, I can't be sure of it atm.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 24th, 2007, 1:15 am 
Offline

Joined: May 20th, 2007, 7:48 pm
Posts: 48
Sean wrote:
I suppose a way with the least risk would be that if a parameter is recognized as an integer don't do type coercion into bstring but do specify it as one of the integer types. It would also reduce substantially further the need of Invoke_().
One problem with it, however, is what integer type should be used.
integer (:VT_I4) or (U)int64 (:VT_I8 or VT_UI8) or etc.
VT_I4 may be the safest bet, whereas VT_UI8 may be the most generic.
Whatever is most common in the Office apps is probably the way to go. Folks poking around in the Windows API will probably be more comfortable with specifying types when necessary.



This may be some sort of type issue? I'm translating a bit of code from VB to AHK, and can't seem to get the "Write" method on a stream object to work when the source is a binary database field. The stream object stays empty no matter what VT_ type is specified for the data value (see the highlighted line of code). Thanks for any hints.

Here's the VB code I'm translating
Code:
'Reference http://support.microsoft.com/kb/258038
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mstream As ADODB.Stream

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"

Set rs = New ADODB.Recordset
rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimistic

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs.Fields("logo").Value
mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWrite

rs.Close
cn.Close
And the AHK version
Code:
#Include CoHelper.ahk
adOpenKeyset := 1, adLockOptimistic := 3, adTypeBinary := 1, adSaveCreateOverWrite := 2

CoInitialize()

pcn := ActiveXObject("ADODB.CONNECTION")
Invoke(pcn, "Open", "Provider=SQLOLEDB;data Source=<name of your SQL Server>;Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>")

prs := ActiveXObject("ADODB.RECORDSET")
Invoke_(prs, "Open", 8,"Select * from pub_info", 9,pcn, 3,adOpenKeyset, 3,adLockOptimistic)

pmstream := ActiveXObject("ADODB.STREAM")
Invoke(pmstream, "Type=", adTypeBinary)
Invoke(pmstream, "Open")
Invoke_(pmstream, "Write", 12,Invoke(Invoke(Invoke(prs, "Fields"), "Item", "logo"), "Value")) ;doesn't work
Invoke(pmstream, "SavetoFile", "c:\publogo.gif", adSaveCreateOverWrite)

Invoke(pcn, "Close")
Invoke(prs, "Close")
Release(pmstream)
Release(prs)
Release(pcn)
CoUninitialize()
ExitApp


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 24th, 2007, 2:21 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
tfcahm wrote:
Sean wrote:
I suppose a way with the least risk would be that if a parameter is recognized as an integer don't do type coercion into bstring but do specify it as one of the integer types. It would also reduce substantially further the need of Invoke_().
One problem with it, however, is what integer type should be used.
integer (:VT_I4) or (U)int64 (:VT_I8 or VT_UI8) or etc.
VT_I4 may be the safest bet, whereas VT_UI8 may be the most generic.
Whatever is most common in the Office apps is probably the way to go. Folks poking around in the Windows API will probably be more comfortable with specifying types when necessary.

I updated the Invoke() with VT_I4, however, am currently using VT_UI8 myself as an experiment, as would not specify correctly data values of integer type greater than 2**32 with VT_I4.

Quote:
This may be some sort of type issue? I'm translating a bit of code from VB to AHK, and can't seem to get the "Write" method on a stream object to work when the source is a binary database field. The stream object stays empty no matter what VT_ type is specified for the data value (see the highlighted line of code). Thanks for any hints.

Why did you use 12 as the type? 12 is VT_VARIANT, so, I suppose it was wrong here unless the data field is encapsulated in a variant.

Need to do some debug: go to the near the end of Invoke(), add MsgBox, % NumGet(varResult,0,"Ushort") before and after If nFlags & 3 syntax. The reason of adding two is to check also whether a type coercion is happening or not.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 24th, 2007, 3:01 am 
Offline

Joined: June 27th, 2007, 9:07 pm
Posts: 101
Location: California
Sean wrote:
I suppose a way with the least risk would be that if a parameter is recognized as an integer don't do type coercion into bstring but do specify it as one of the integer types. It would also reduce substantially further the need of Invoke_().

I agree that in most cases it will probably be more helpful if it is assumed to be an integer than to be a string.

Sean wrote:
One problem with it, however, is what integer type should be used.
integer (:VT_I4) or (U)int64 (:VT_I8 or VT_UI8) or etc.
VT_I4 may be the safest bet, whereas VT_UI8 may be the most generic.

According to the VARIANT specification, VT_I8 and VT_UI8 should not appear in a VARIANT.
http://msdn2.microsoft.com/en-us/library/ms221170.aspx
http://www.marin.clara.net/COM/variant_ ... itions.htm

I had one example of a property returning a value that wouldn't fit in I4:
Code:
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oDrive = oFSO.GetDrive("C:")
oDrive.FreeSpace

The FreeSpace property was returning a VT_R8 on my computer that had dozens of GB of free space.

_________________
-m35


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: July 24th, 2007, 3:20 am 
Offline

Joined: May 20th, 2007, 7:48 pm
Posts: 48
Sean wrote:
Why did you use 12 as the type? 12 is VT_VARIANT, so, I suppose it was wrong here unless the data field is encapsulated in a variant.
I tried them all. VT_VARIANT was the last one left in there before I posted.

Quote:
Need to do some debug: go to the near the end of Invoke(), add MsgBox, % NumGet(varResult,0,"Ushort") before and after If nFlags & 3 syntax. The reason of adding two is to check also whether a type coercion is happening or not.
Before/After the "If nFlags". Same result for every VT_ type I tried.
Code:
Invoke_(pmstream, "Write", 12,Invoke(Invoke(Invoke(prs, "Fields"), "Item", "logo"), "Value"))
 \_ 0/8                         \_ 8209/8


Thanks for the help.


Report this post
Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 132 posts ]  Go to page Previous  1, 2, 3, 4, 5 ... 9  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: Google Feedfetcher, JamixZol, kiropes, RoAltmann and 52 guests


You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Powered by phpBB® Forum Software © phpBB Group