![]() ![]() #Excel query table in same workbook code#Note, connection string in the above code shown for. ' output the values from the recordset and the corresponding values from the dictionaryĭebug.Print dProdNum & " - " & oDict(dProdNum) ODict(Trim(aKeys(i)) + 0) = Trim(aItems(i)) Set oDict = CreateObject("Scripting.Dictionary") Using the MS-Query functionality in excel, everything works fine except when I try to send the file to other people, the workbook is unable to reference itself as the source of the MS-Query and I think looks for the absolute path of the file on my machine. ' retrieve key - value pairs from relation sheet Due to an 'unusual' situation, I have had to setup a workbook as an RDBMS. Set oRs = oCn.Execute("SELECT DISTINCT ProductNumber FROM WHERE ProductSource = 'A1'") ![]() ![]() "Extended Properties=""Excel 8.0 HDR=Yes FMT=Delimited IMEX=1 "" " With Excel, you define several named ranges in a single workbook, and then use the ranges as database tables. With text files, you can place them all in one folder to form a database. But with the help of the fabulous DAX Studio. "DataSource='" & ThisWorkbook.FullName & "' " & _ MS Query gives Excel users the ability to access 3rd party databases, text files, and Excel workbooks as relational data sources. TL DR We can connect almost any data source in Power Query, but PowerPivot data model is not included in that extensive list of sources out of the box. Set oCn = CreateObject("ADODB.Connection") ' create ADODB connection to this workbook #Excel query table in same workbook how to#Take a look at the below example showing how to create ADODB connection to this workbook, get ADODB recordset from SQL query, retrieve key - value pairs from relation sheet, create and populate a dictionary, and output the values from the recordset and the corresponding values from the dictionary: Option Explicit The numbers corresponds to their respective Product company in order. 9 is for Amul, 5 is for Nestle and so on. Now after I get the numbers from Data sheet, I need to find the corresponding "Extended Properties=""Excel 8.0 HDR=Yes FMT=Delimited IMEX=1 """) The getData function is defines as below Public funtion getData(path as String, SQL as string) as ADODB.RecordsetĬn.Open ("Provider= .4.0 " & _ 'Not sure if this is the value I shall send as datapath in getData function 'sdata is a sheet named as Data in the workbook 'pSource is a string that stores Product Source SQL = "Select ProductNumber from where ProductSource = " & pSource & " Use QueryTables (index), where index is the index number of the query table, to return a single QueryTable object. The QueryTable object is a member of the QueryTables collection. I am novice in VBA so please don't mind if the question is of low level.I am trying to run a SQL query where the data has to be extracted from one of the sheets of the same workbook. Represents a worksheet table built from data returned from an external data source, such as a SQL server or a Microsoft Access database. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |