 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
Sean
Joined: 12 Feb 2007
Posts: 2462
|
 Posted: Tue Aug 07, 2007 11:33 pm Post subject: |
 |
|
| daonlyfreez wrote: | | Can anybody create a simple script that demonstrates how to get Excel contents, a single cell, or a range? |
It's similar to set/put a value into a cell:
| Code: | Value11 := Invoke(Invoke(pcls, "Item", 1, 1), "Value")
Value22 := Invoke(Invoke(pcls, "Item", 2, 2), "Value") |
Compare it with Put (:notice the suffix = in the function name "Value"):
| Code: | Invoke(Invoke(pcls, "Item", 1, 1), "Value=", "Test11")
Invoke(Invoke(pcls, "Item", 2, 2), "Value=", "Test22") |
|
|
| Back to top |
|
 |
daonlyfreez
Joined: 16 Mar 2005
Posts: 949
Location: Berlin
|
| Posted: Wed Aug 08, 2007 7:33 am Post subject: |
|
|
Great! Thank you.
Here is an example script. Could you check what is needed (what I am doing wrong) to get a range of cells? I do get some value...
| Code: | #Include %A_ScriptDir%\CoHelper.ahk
testFile := A_ScriptDir . "\test.xls"
MsgBox Step 1: Writing to Excel file
CoInitialize()
pxl := ActiveXObject("Excel.Application")
pawb := Invoke(Invoke(pxl, "Workbooks"), "Add")
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")
Invoke(Invoke(pcls, "Item", 1, 1), "Value=", "Test11")
Invoke(Invoke(pcls, "Item", 1, 2), "Value=", "Test12")
Invoke(Invoke(pcls, "Item", 1, 3), "Value=", "Test13")
Invoke(pawb, "SaveAs", testFile)
Invoke(pxl, "Quit")
Release(pxl)
MsgBox Step 2: Getting individual cells from Excel file
pxl := ActiveXObject("Excel.Application")
pawb := Invoke(Invoke(pxl, "Workbooks"), "Add")
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")
pawb := Invoke(Invoke(pxl, "Workbooks"), "Open", testFile)
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")
Value11 := Invoke(Invoke(pcls, "Item", 1, 1), "Value")
Value12 := Invoke(Invoke(pcls, "Item", 1, 2), "Value")
MsgBox % "Value11: " . Value11 . "`nValue12: " . Value12
MsgBox Step 3: Getting a range of cells from Excel file
ValueR := Invoke(Invoke(pcls, "Range", "A1", "C1"), "Value")
;MsgBox % ErrorLevel . "`n" . A_LastError
Invoke(pxl, "Quit")
Release(pxl)
CoUninitialize()
MsgBox % "ValueR: " . ValueR
FileDelete % testFile |
And, I tried shortening the code by removing the "duplicates":
| Code: | pxl := ActiveXObject("Excel.Application")
pawb := Invoke(Invoke(pxl, "Workbooks"), "Add")
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")
pawb := Invoke(Invoke(pxl, "Workbooks"), "Open", testFile)
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")
Invoke(pxl, "Quit")
Release(pxl)
|
But, I guess I need to repeat them, for else AHK/Excel hangs/crashes... Could you shed a light on that too?
_________________
 mirror 1 • mirror 2 • mirror 3 • ahk4.me • PM or  |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Wed Aug 08, 2007 8:35 am Post subject: |
|
|
| daonlyfreez wrote: | | Here is an example script. Could you check what is needed (what I am doing wrong) to get a range of cells? I do get some value... |
The syntax is correct. What you missed is that if you try to retrieve multiple cells/range, then the result will be returned in an array. So, what you saw is the address of the returned array.
As needs extra steps to have the actual values, I'd rather retrieve the values one-by-one.
| Quote: | And, I tried shortening the code by removing the "duplicates":
But, I guess I need to repeat them, for else AHK/Excel hangs/crashes... Could you shed a light on that too?
|
You don't need the step "Add" when you open an existing file. Examine the following code:
| Code: | #Include CoHelper.ahk
CoInitialize()
pxl := ActiveXObject("Excel.Application")
;Invoke(pxl, "Visible=", "True")
pawb := Invoke(Invoke(pxl, "Workbooks"), "Add")
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")
Invoke(Invoke(pcls, "Range", "A1:C3"), "Value=", "Test")
;Invoke(Invoke(pcls, "Range", "A1", "C3"), "Value=", "Test")
Invoke(pawb, "SaveAs", A_Temp . "\test.xls")
Invoke(pawb, "Close")
pawb := Invoke(Invoke(pxl, "Workbooks"), "Open", A_Temp . "\test.xls")
paws := Invoke(Invoke(pxl, "Worksheets"), "Item", 1)
pcls := Invoke(paws, "Cells")
Value11 := Invoke(Invoke(pcls, "Item", 1, 1), "Value")
Value22 := Invoke(Invoke(pcls, "Item", 2, 2), "Value")
Value33 := Invoke(Invoke(pcls, "Item", 3, 3), "Value")
Invoke(Invoke(pcls, "Item", 4, 4), "Value=", "Test44")
Invoke(pawb, "Save")
Invoke(pawb, "Close")
Invoke(pxl, "Quit")
Release(pxl)
CoUninitialize()
|
|
|
| Back to top |
|
|
daonlyfreez
Joined: 16 Mar 2005
Posts: 949
Location: Berlin
|
| Posted: Wed Aug 08, 2007 10:22 am Post subject: |
|
|
Great! Thanks again. Will use looping to get ranges then.
_________________
mirror 1 • mirror 2 • mirror 3 • ahk4.me • PM or |
|
| Back to top |
|
|
BoBo¨
Guest
|
| Posted: Wed Apr 09, 2008 1:02 pm Post subject: |
|
|
On July 27, 2007 Sean (is he alive?  ) has promised to shed some light on [this].
Well, meanwhile we're running out of time  , and therefore request the community (in general) to get support on this. Any help will be much appreciated.
|
|
| Back to top |
|
|
silveredge78
Joined: 25 Jul 2006
Posts: 481
Location: Midwest, USA
|
| Posted: Wed Apr 09, 2008 4:26 pm Post subject: |
|
|
I reviewed this post and it looks very promising. I'm not too great at DB things. However I have need to pull data from MS SQL 2000 databases for clients. I currently use t4esql to do it. It pulls incorrect information when there is NULLs so I had to write code to filter it accordingly. It seems to work, but if I could do this purely with AHK that would be what I prefer.
I know this refers to ADO, which I believe is what Access can use. But can this script set be used in MS SQL 2000 (I don't know what access standards can be used). If so, can someone provide a quick example comparable to the one used for the Access database.
Thank you very much in advance if you can help! 
_________________
SilverEdge78 |
|
| Back to top |
|
|
tank
Joined: 21 Dec 2007
Posts: 3700
Location: Louisville KY USA
|
| Posted: Sun May 25, 2008 7:36 am Post subject: |
|
|
the use of com library still finds me in utter amazement
in some ways it has actually come to feel more natuaral to me then using vb kinda sur real as it were
i suspect that even tho this library has been out now couple years well almost that it may be yet years to come before the true depth of its benifet is fully seen
on another note i had totally forgoten you could silently interact with an excell document
till i saw sean comment it out
truly a freaking genious
_________________
We are troubled on every side‚ yet not distressed; we are perplexed‚
but not in despair; Persecuted‚ but not forsaken; cast down‚ but not destroyed; |
|
| Back to top |
|
|
namebran
Joined: 13 Oct 2008
Posts: 9
|
| Posted: Sun Jan 11, 2009 4:52 am Post subject: |
|
|
I've run into a problem using the what i've learned from this topic. I have been working on a script in which i have to access a SQL Server and its Databases often for maintenance reasons. Anyway, everything has worked great when running queries using ADO, but when i try to use a Stored Procedure instead of say a SELECT statement, the RecordSet is empty. Is there someone that could please help me with executing a Stored Procedure using ADO.
Thank you in Advance. |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Sun Jan 11, 2009 10:28 am Post subject: |
|
|
| namebran wrote: | | when i try to use a Stored Procedure instead of say a SELECT statement, the RecordSet is empty. |
Post your code. |
|
| Back to top |
|
|
drmurdoch
Joined: 10 Nov 2006
Posts: 110
|
| Posted: Sun Jan 11, 2009 6:04 pm Post subject: I want to write my schedule to text files |
|
|
Sean,
Would this allow me to read a MS Access Database ? |
|
| Back to top |
|
|
namebran
Joined: 13 Oct 2008
Posts: 9
|
| Posted: Sun Jan 11, 2009 7:43 pm Post subject: |
|
|
Here is the code that matters, i am using almost exactly what is on the first page of this topic with some minor changes. Even using that code exactly as it is, with my connection string of course, i still couldn't get a SQL built in stored procedure to return any result. And just to let you know i am no expert at any of this. This is my 2nd post ever and i've only been using AHK or done any scripting for like 3 or 4 months now.
One thing i noticed when i run this the nRecordCount variable is "-1". I'm not sure what that means but i was hoping it might be helpful.
Thanks!
| Code: |
sConnect := "Driver={SQL Server};Server=" . dbConnectB . ";Uid=" . loginUser . ";Pwd=" . loginPass . ";"
prs := ActiveXObject("ADODB.RECORDSET")
NEXquery := "exec sp_databases"
qErr := adoQuery(prs, NEXquery, sConnect, qData)
If qErr
{
MsgBox % "Error connecting to SQL Server"
Return
}
MsgBox % "Result = " . qData
adoQuery(xprs, sQry, sCon, ByRef qData)
{
Invoke(xprs, "open", sQry, sCon, 1, -1, -1) ;Open
sState := Invoke(xprs, "State") ;State
If !sState
Return "1"
BOF := Invoke(xprs, "BOF") ; BOF
EOF := Invoke(xprs, "EOF") ; EOF
If BOF && EOF
MsgBox % "No Match Found!"
Invoke(xprs, "MoveLast") ; MoveLast
nRecordCount := Invoke(xprs, "RecordCount") ; RecordCount
Invoke(xprs, "MoveFirst") ; MoveFirst
qData := ""
Loop, %nRecordCount%
{
pFields := Invoke(xprs, "Fields") ; Fields
nFieldCount := Invoke(pFields, "Count") ; Count
sData := ""
Loop, %nFieldCount%
{
pField%A_Index% := Invoke_(pFields, "Item", 3, arg:=A_Index-1) ; Item
sName := Invoke(pField%A_Index%, "Name") ; Name
sValue := Invoke(pField%A_Index%, "Value") ; Value
sData .= sName . ": " . sValue . "`r`n"
Invoke(pField%A_Index%, "Release") ; Release
}
If nRecordCount > 1
{
If A_Index = %nRecordCount%
qData := qData . sValue
Else
qData := sValue . ";" . qData
}
Else
qData := sValue
Release(pFields) ; Release
Invoke(xprs, "MoveNext") ; MoveNext
}
Invoke(xprs, "Close") ; Close
Return
}
|
|
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Mon Jan 12, 2009 12:06 am Post subject: |
|
|
First just specify the stored procedure name like:
| Code: | | NEXquery := "sp_databases" ; Stored Procedure name |
Then, specify the CommandType when opening the recordset:
| Code: | | Invoke(xprs, "open", sQry, sCon, 1, -1, adCmdStoredProc := 4) |
I hope it work. BTW, if you need to specify parameters to the stored procedure, should use the Command object ADODB.Command. |
|
| Back to top |
|
|
namebran
Joined: 13 Oct 2008
Posts: 9
|
| Posted: Mon Jan 12, 2009 4:03 am Post subject: |
|
|
| Thanks for your advice Sean, unfortunately i wasn't able to get it to work. I must be missing something although i have been looking at msdn and w3schools info and it looks like it should work. I have tried the Command Object also with the same results. If you think of anything else let me know but i dont wont to take up too much of your time. Of course i will keep playing with it but i 'll be looking for a work around with just using standard queries too. |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Mon Jan 12, 2009 5:30 am Post subject: |
|
|
| namebran wrote: | | Thanks for your advice Sean, unfortunately i wasn't able to get it to work. |
Need error messages. For that I suggest to use COM Standard Library. Just include it into the script and change all the function call Invoke to COM_Invoke, and see if it outputs an error message. |
|
| Back to top |
|
|
namebran
Joined: 13 Oct 2008
Posts: 9
|
| Posted: Tue Jan 13, 2009 12:44 am Post subject: |
|
|
Okay i used the code below this time, basically the same thing (the ADO.ahk script). I first plugged in the stored procedure with my connectin string to see if it worked and it didn't. So then i did as you said and used the Com Library. Here is the output error message i received:
| Code: |
; Adjust sSource and sConnect appropriately
#Include Com.ahk
sSource := "sp_databases"
sConnect := "Driver={SQL Server};Server=XXXXXX;Uid=sa;Pwd=abc123;"
Com_CoInitialize()
prs := ActiveXObject("ADODB.Recordset")
Com_Invoke(prs, "Open", sSource, sConnect, 1, -1, 4)
If Com_Invoke(prs, "BOF") && Com_Invoke(prs, "EOF")
{
MsgBox, No Match Found!
ExitApp
}
Com_Invoke(prs, "MoveLast")
nCount := Com_Invoke(prs, "RecordCount")
Com_Invoke(prs, "MoveFirst")
Loop, % nCount
{
pFields := Com_Invoke(prs, "Fields")
Loop, % Com_Invoke(pFields, "Count")
pField%A_Index% := Com_Invoke(pFields, "Item", A_Index-1)
, sData .= Com_Invoke(pField%A_Index%, "Name") . ": " . Com_Invoke(pField%A_Index%, "Value") . "`r`n"
, Com_Release(pField%A_Index%)
Com_Release(pFields)
Com_Invoke(prs, "MoveNext")
; Com_Invoke(prs, "MovePrevious")
MsgBox, % sData
}
Com_Invoke(prs, "Close")
Com_Release(prs)
Com_CoUninitialize()
|
[/img] |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
