Hello all, I AM trying to use the wonderful feature of Microsoft Query. I want to use a Excel workbook as a database, so I have created the attached workbook. Sales.xlsx is the source, so another workbook for reports is created. When I pointed to the Sales.xlsx, in external data settings, the following connection string was automatically defined:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\n\Vendas.xlsx;Mode=Share Deny Write;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False I tried the following SQL to get data: Select * from records; The success was absolute. So I tried: Select sum(receita) from records; Having gotten na error, I set “support complex data” to true. So I have success again. However, I could not understand how to use SQL with the “group by” clause. For example: Select Store, sum(revenue) as Revenue from records group by Store; I have a error message: No value is supplied to one or more parameters. I don’t know what it means, so I ask the help from you. I know it’s not a doubt directly linked to VBA, but as I could not find the answer and I know, in this group, there are many Excel experts, I decide to ask you a help. I thank you very much for any support. -- Luciano de Souza -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
