Change Excel SQL Command with macros

Question

I need to create a Excel 2010 macro, which changes the SQL Command text of a Excel Connection. I'm working with a SQL Server, if it matters.

How could I do this? I've found several links online, but none of them have worked.

The most recent code I used:

Dim comText As String

comText = "select  top 10000 [activitypointer0.subject] as subject , [activitypointer0.activityid] as activityid , [activitypointer0.instancetypecodename] as instancetypecodename , [activitypointer0.scheduledend] as scheduledend , [activitypointer0.createdbyname] as createdbyname , [activitypointer0.activitytypecodename] as activitytypecodename , [a_9cdbdceab5ee4a8db875050d455757bd.name] as [a_9cdbdceab5ee4a8db875050d455757bd.name]"
comText = comText & " from  FilteredActivityPointer as [activitypointer0] left outer join FilteredAccount as [a_9cdbdceab5ee4a8db875050d455757bd] on ([activitypointer0.regardingobjectid]  =  [a_9cdbdceab5ee4a8db875050d455757bd.accountid])"
comText = comText & " where  ([activitypointer0.activitytypecode] = 4201 and ([activitypointer0.ownerid] in (N'{69CB3CDE-0998-E011-916A-00155D000607}' , N'{D8D40800-B798-E011-916A-00155D000607}' , N'{7ECB3CDE-0998-E011-916A-00155D000607}' , N'{70CB3CDE-0998-E011-916A-00155D000607}')) and [activitypointer0.statecode] = 1 and  [activitypointer0.scheduledendutc] >= '20111231 22:00:00'  and  [activitypointer0.scheduledendutc] < '20120131 22:00:00' )"
comText = comText & " order by  [activitypointer0.subject] asc , [activitypointer0.activityid] asc"

With ActiveWorkbook.Connections("conCRM").ODBCConnection
    .commandText = comText
End With

ActiveWorkbook.Connections("conCRM").Refresh

This is giving me an error about column names.

Column name error

What code do you currently have, what have you specifically tried, and in what manner didn't it work? — Dan
– Dan, Commented Feb 10, 2012 at 14:20
Your question is way to vague for us to help you. You need to provide more details — Lamak
– Lamak, Commented Feb 10, 2012 at 14:31
Does the query run correctly in SQL Server? I'm not much with SQL, but "[a_9cdbdceab5ee4a8db875050d455757bd.name] as [a_9cdbdceab5ee4a8db875050d455757bd.name]" looks funny to me. — Doug Glancy
– Doug Glancy, Commented Feb 10, 2012 at 15:03
I changed it, but the problem was not solved. It's giving me errors about column names. — ositra
– ositra, Commented Feb 13, 2012 at 14:11

leoinfo · Accepted Answer · 2012-02-13 14:16:34Z

2

Maybe you should be using column names like [activitypointer0].[subject] instead of [activitypointer0.subject] (and others like it) if the error you get is only "complaining" about column names.

answered Feb 13, 2012 at 14:16

leoinfo

8,2458 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ositra Over a year ago

You solved it, leoinfo! You made me very happy. Thank you very much!

Collectives™ on Stack Overflow

Change Excel SQL Command with macros

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related