Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC excel Macro help needed
Can someone tell me what is wrong with this?
With Selection.QueryTable .Connection = Array(Array( _ "ODBC;DSN=Commerce Center;DATABASE=CommerceCenter;Trusted_Connection= Yes")) .CommandText = Array( _ "SELECT *" _ , _ "" & Chr(13) & "" & Chr(10) & "" _ , _ "FROM CommerceCenter.dbo.p21_view_inv_mast p21_view_inv_mast" _ ) .Refresh BackgroundQuery:=False End With I'm in an excel spreadsheet. I can edit the query and that works fine but from within the macro I keep getting a "Run-time error '1004' - Application-defined or object-defined error". I have shortened this query down as much a possible but still get the error. Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC excel Macro help needed
Why:
"SELECT *" _ , _ "" & Chr(13) & "" & Chr(10) & "" _ , _ "FROM CommerceCenter.dbo.p21_view_inv_mast p21_view_inv_mast" _ instead of: "SELECT * FROM CommerceCenter.dbo.p21_view_inv_mast p21_view_inv_mast" -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC excel Macro help needed
I don't know. You mean the "& Chr(13) & Chr(10) &"?
They represtent Carrage Return and Line Feed. That is only way that it worked. I have to place then at the end of each line before another commmand like FROM, WHERE, ORDER etc. I don't even remember where I found this. Anyway I have probably 30+ spreadsheets that have macros that are calling queries from a SQL2000 database. Dan "Bill Renaud" wrote in message . .. Why: "SELECT *" _ , _ "" & Chr(13) & "" & Chr(10) & "" _ , _ "FROM CommerceCenter.dbo.p21_view_inv_mast p21_view_inv_mast" _ instead of: "SELECT * FROM CommerceCenter.dbo.p21_view_inv_mast p21_view_inv_mast" -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC excel Macro help needed
I have never seen Carriage Return and Line Feed embedded in the middle
of an SQL query before. Anybody else know if this is legal syntax? (There may be something else wrong with your overall code; I just happened to spot something that stood out.) -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC excel Macro help needed
I understand. And I am no programmer. At least not in 20 years but that is
the only way it will work. This is wierd though. I just took one of my working macros and deleted just one of the CR LF (the one after the SELECT stmnt) in a statement and it worked. Then I deleted the other two (after FROM and WHERE stmnts) and I get this error "Run-time error '1004': SQL Syntax Error" By the way if there is a better way to do ODBC queries in a macro let me know. Like the connection statement. Can I connect once to the database and then just call the tables that I need? Back to my original problem: Could something have changed in Excel 2007? I save my work and got on my onter computer running Excel 2003 and it worked. I finished up the entire program and saved it. Pulled it up in Excel 2007 and I got the error. Strange. Dan "Bill Renaud" wrote in message . .. I have never seen Carriage Return and Line Feed embedded in the middle of an SQL query before. Anybody else know if this is legal syntax? (There may be something else wrong with your overall code; I just happened to spot something that stood out.) -- Regards, Bill Renaud |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC excel Macro help needed
"Dan" wrote:
I understand. And I am no programmer. At least not in 20 years but that is the only way it will work. This is wierd though. I just took one of my working macros and deleted just one of the CR LF (the one after the SELECT stmnt) in a statement and it worked. Then I deleted the other two (after FROM and WHERE stmnts) and I get this error "Run-time error '1004': SQL Syntax Error" By the way if there is a better way to do ODBC queries in a macro let me know. Like the connection statement. Can I connect once to the database and then just call the tables that I need? Back to my original problem: Could something have changed in Excel 2007? I save my work and got on my onter computer running Excel 2003 and it worked. I finished up the entire program and saved it. Pulled it up in Excel 2007 and I got the error. Strange. What was the original error that made you insert Chr(13) & "" & Chr(10) & in ..CommandText? What did the Sql string look like before that? Dan "Bill Renaud" wrote in message . .. I have never seen Carriage Return and Line Feed embedded in the middle of an SQL query before. Neither have I. I made some test using Excel 2007, Sql Server 2000 and 2005, with and without array function, using Chr(13) & "" & Chr(10) & or vbCrLf, or just setting CommandText to a single line string, and it all worked. Anybody else know if this is legal syntax? (There may be something else wrong with your overall code; I just happened to spot something that stood out.) -- Regards, Bill Renaud -- urkec |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC excel Macro help needed
From reading Excel Help, my best guess is that your code should look
something more like the following: With Selection.QueryTable .Connection = "ODBC;DSN=Commerce Center;DATABASE=CommerceCenter;Trusted_Connection= Yes" .CommandText = "SELECT * FROM CommerceCenter.dbo.p21_view_inv_mast WHERE condition op value" .Refresh BackgroundQuery:=False End With I don't understand why your original code has all the ARRAY functions in it, as the values for the Connection and CommandText properties do not require an array, but a simple text string. Also, in your SQL query ("SELECT * ...") there probably should be a condition clause somewhere, unless you want to return the entire table. i.e. "WHERE Cost <= 100". It appears that you have listed the table name twice. HTH -- Regards, Bill Renaud |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC excel Macro help needed
As an afterthought, sometimes VBA won't let you use Selection or
ActiveCell to do things. You HAVE to use an object variable. The macro recorder sometimes does not always record the exact code that you really need to use. Dim rngMyDataRange as Range Set rngMyDataRange = Worksheets("Sheet1").Range("A1:B2") With rngMyDataRange.QueryTable .... End With I don't know exactly what the range would have to be in this case, whether you can just use the range of the upper-left corner of the data area being used by the SQL query, or whether you need to specify the entire worksheet, or entire data area, or what. -- Regards, Bill Renaud |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ODBC excel Macro help needed
Dim qt As QueryTable
Set qt = Worksheets("Sheet1").QueryTable(1) With qt .Connection = "ODBC;DSN=CommerceCenter;DATABASE=CommerceCenter;T rusted_Connection=Yes" .CommandText = "SELECT * FROM CommerceCenter.dbo.p21_view_inv_mast WHERE condition op value" .Refresh BackgroundQuery:=False End With -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro Needed | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Excel Macro VBA Help Needed | Excel Programming | |||
Macro needed to set macro security in Excel to minimum | Excel Programming | |||
help needed with excel macro | Excel Programming |