SQLStr = " SELECT Field1, Field2, Field3, Field4 " Server_Name = "Your_Server_Name" ' Enter your server name hereĭatabase_Name = "Northwnd" ' Enter your database name here Just look out for word wrap! Sub ADOExcelSQLServer() Side note: whatever you do, never concatenate user input into a SQL query. The result will at least be much cleaner than a mess of interleaved line-continuated concatenations, and will perform better. If there's absolutely no way to avoid it, then I'd warmly recommend you use a StringBuilder to do the string concatenations. If you can't create a view or stored procedure on the database, give your query to someone who can. They belong on the server, in a view or a stored procedure. Regardless of the language, such complex queries don't belong in source code. Merely looking at that query will give you nightmares. Maintaining that query will be a nightmare. Fixing a bug in that query will be a nightmare. Obviously this is going to get very ugly, very fast. Sql = "SELECT Count(column1) as status_count, case when " & _Īnd when the compiler starts complaining that there are too many line continuations, you just concatenate it with itself: sql = sql & " rest of the " & _ See, the above statement is one single logical line of code, but it spans 3 physical lines, which involves 2 line continuation tokens. Now, one problem you'll run into with such a long query, is that VBA will not let you make a logical line of code that spans much more than 10-15 physical lines of code. Please let me know how the long SQL queries can be embedded into the VBA code without any errors.Įxactly how you're building your connection string: sConnString = "Provider=SQLOLEDB Data Source=vrsqladhoc " & _ SELECT Count(column1) as status_count, case when Missing_flag1 = 1 then 'Missing' else 'Available' end Inner join table2 md on md.column1 = cm.column2 Missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type The long query I am intending to embed in the VBA code looks something like this: SELECT Count(column1) as status_count, case when MsgBox "Error: No records returned.", vbCritical Sheets(2).Range("A2").CopyFromRecordset rs Set rs = conn.Execute("select distinct column1 from table1 ") ' Create the Connection and Recordset objects. SConnString = "Provider=SQLOLEDB Data Source=vrsqladhoc " & _ When I have complex and long queries: Sub ConnectSqlServer() Select * from table1), but this code isn't working and showing aĬompile error: user-defined type not defined. return an empty date object, if the string is empty or malformed (for comparison in the loop).I have the below VBA code that works successfully in getting data from SQL database to an Excel worksheet when I have to embed a simple query (e.g.parse string with any date format and return a date object to work with.Unfortunately, CDate() function throws the error: Range("A1").Resize(UBound(result), 1).Value = Application.Transpose(result) ' some more operations an put dates into result array So I have to convert the values into array: Dim current as Date, highest as Date, result() as Date I need to make decision on basics of date and then print the result in a new sheet. I have stored the values of DeliveryDate in an array. I have data in an excel sheet in the following format: ItemCode DeliveryDate
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |