Josh_Blade
asked on
Running a sql function in an Excel macro
I have a scalar function I have written in SQL. It takes in 2 date times and spits out our business cycle time.
I'd like to be able to have a macro that will look at two columns of cells and run the function for each row that has data.
IE, I want columns A and B to have a bunch of rows with Date1 and Date2.
I want to run a macro that will put
myDateFunction(A1, B1) into column C1
myDateFunction(A2, B2) into column C2
etc...
I'd like to be able to have a macro that will look at two columns of cells and run the function for each row that has data.
IE, I want columns A and B to have a bunch of rows with Date1 and Date2.
I want to run a macro that will put
myDateFunction(A1, B1) into column C1
myDateFunction(A2, B2) into column C2
etc...
ASKER
imnorie,
Thanks for the quick response.
I entered the below into the macro
When I run it, I get a
"Run-time error '1004':
Method 'Range' of object '_Global' failed." error message.
(in debug, it highlights the second line as causing the problem)
Here's the Sample data I used:
A B C
01/13/12 02/01/12
01/27/12 02/03/12
01/16/12 02/08/12
01/16/12 02/08/12
Looking around the web a little bit, I just wanted to be clear that I'm wanting to use a scalar function from SQL. I've noticed a lot of other posts about this topic creating connection strings (this spreadsheet does already have a connection string set up in the data tab if that makes a difference).
Thanks for the quick response.
I entered the below into the macro
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("C1:C" & C).Formula = "=dbo.CycleTimeASIhmmss(A1,B1)"
When I run it, I get a
"Run-time error '1004':
Method 'Range' of object '_Global' failed." error message.
(in debug, it highlights the second line as causing the problem)
Here's the Sample data I used:
A B C
01/13/12 02/01/12
01/27/12 02/03/12
01/16/12 02/08/12
01/16/12 02/08/12
Looking around the web a little bit, I just wanted to be clear that I'm wanting to use a scalar function from SQL. I've noticed a lot of other posts about this topic creating connection strings (this spreadsheet does already have a connection string set up in the data tab if that makes a difference).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
imnorie,
Thanks again for your informed response. After some more digging around, i've stumbled across a few similar UDFs that other people have written. I've tweaked it to what I think should work for me, but I keep getting
Comile error:
User-defined type not defined
and the debugger highlights the signature of my function.
From what I'm reading there's some kind of reference missing (probably something to do with the ADO/sql stuff that's going on). I was hoping you might have an idea of what that would be. If not, thanks for all of your help!
Edit: After some testing, I was able to verify that it was this line causing the problem, so I need to find the right reference for it. I'll update when I find it
Thanks again for your informed response. After some more digging around, i've stumbled across a few similar UDFs that other people have written. I've tweaked it to what I think should work for me, but I keep getting
Comile error:
User-defined type not defined
and the debugger highlights the signature of my function.
Public Function CycleTime(FromDate As Date, ToDate As Date) As String
Dim cn As New ADODB.Connection, cmd As New ADODB.Command, rs As ADODB.Recordset
cn.Open "Provider=SQLOLEDB;Data Source=MYSERVER;Initial Catalog=MYCATALOG;Integrated Security=SSPI;"
cmd.ActiveConnection = cn
cmd.CommandText = "Select dbo.CycleTimeASIhmmss('" & FromDate & "','" & ToDate & "')"
Set rs = cmd.Execute()
rs.MoveFirst
CycleTime = rs(0)
End Function
From what I'm reading there's some kind of reference missing (probably something to do with the ADO/sql stuff that's going on). I was hoping you might have an idea of what that would be. If not, thanks for all of your help!
Edit: After some testing, I was able to verify that it was this line causing the problem, so I need to find the right reference for it. I'll update when I find it
Dim cn As New ADODB.Connection, cmd As New ADODB.Command, rs As ADODB.Recordset
ASKER
I needed the Microsoft ActiveX Data Objects 2.0 Library
You can find it by going to Tools>References from the vba window.
Here's a link in another EE page I found:
link
You can find it by going to Tools>References from the vba window.
Here's a link in another EE page I found:
link
Josh
Glad you got it running.
How is it performance wise?
Glad you got it running.
How is it performance wise?
Open in new window
If it's more complicated than that please post further details.