AutoHotkey Community

It is currently May 26th, 2012, 2:02 pm

All times are UTC [ DST ]




Post new topic Reply to topic  [ 132 posts ]  Go to page Previous  1, 2, 3, 4, 5, 6, 7 ... 9  Next
Author Message
 Post subject:
PostPosted: August 8th, 2007, 12:33 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
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")


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: August 8th, 2007, 8:33 am 
Offline

Joined: March 16th, 2005, 10:33 pm
Posts: 968
Location: Frisia
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?

8)

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


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: August 8th, 2007, 9:35 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
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?

8)


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


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: August 8th, 2007, 11:22 am 
Offline

Joined: March 16th, 2005, 10:33 pm
Posts: 968
Location: Frisia
Great! Thanks again. Will use looping to get ranges then. 8)

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


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: April 9th, 2008, 2:02 pm 
On July 27, 2007 Sean (is he alive? :shock:) has promised to shed some light on [this].
Well, meanwhile we're running out of time :wink:, and therefore request the community (in general) to get support on this. Any help will be much appreciated.

8)


Report this post
Top
  
Reply with quote  
 Post subject:
PostPosted: April 9th, 2008, 5:26 pm 
Offline

Joined: July 25th, 2006, 7:37 pm
Posts: 490
Location: Midwest, USA
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


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: May 25th, 2008, 8:36 am 
Offline
User avatar

Joined: December 21st, 2007, 3:14 pm
Posts: 3826
Location: Louisville KY USA
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

_________________
No matter what your oppinion Please join this discussion
Formal request to Polyethene
Image


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: January 11th, 2009, 5:52 am 
Offline

Joined: October 13th, 2008, 2:54 am
Posts: 9
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: January 11th, 2009, 11:28 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
namebran wrote:
when i try to use a Stored Procedure instead of say a SELECT statement, the RecordSet is empty.
Post your code.


Report this post
Top
 Profile  
Reply with quote  
PostPosted: January 11th, 2009, 7:04 pm 
Offline

Joined: November 10th, 2006, 5:10 am
Posts: 110
Sean,

Would this allow me to read a MS Access Database ?


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: January 11th, 2009, 8:43 pm 
Offline

Joined: October 13th, 2008, 2:54 am
Posts: 9
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
}


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: January 12th, 2009, 1:06 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: January 12th, 2009, 5:03 am 
Offline

Joined: October 13th, 2008, 2:54 am
Posts: 9
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: January 12th, 2009, 6:30 am 
Offline

Joined: February 12th, 2007, 7:54 am
Posts: 2462
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.


Report this post
Top
 Profile  
Reply with quote  
 Post subject:
PostPosted: January 13th, 2009, 1:44 am 
Offline

Joined: October 13th, 2008, 2:54 am
Posts: 9
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:

Image

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]


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, 6, 7 ... 9  Next

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users and 17 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