Help need to add sql to vba code

tatendatiffany

Board Regular
Joined
Mar 27, 2011
Messages
103
Could someone please i have to create this vba that runs through different tables and return the result on an sql sheet. I have done some of it but i am stuck on a part where it loops through the tables on a dropdown list: and combining this with sql is proving difficult. this is what i have done so far:

Public Sub LoopTable()
Dim TABLES_S As range
For Each TABLES_S In Worksheets("Sheet 1").range("C2:C39").Cells ' runs through the column of table names
If TABLES_S = "" Then
End If
Next TABLES_S
End Sub
Public Sub LoopColumn()
Dim TableClmn As range
Dim Index As Worksheet
Set Index = ActiveSheet
Index.Activate
For Each TableClmn In Worksheets("Index").range("W2:W39").Cells 'runs through the column of column names
If TableClmn = "" Then
End If
Next TableClmn
End Sub
Public Sub Retrieve()
Dim j As Long
Dim SQL1 As String
Dim TABLES_S As range
Dim TableClmn As range
Dim rst As Recordset
For j = 1 To 38
Cells(j + 1, 24).Value = j 'column were value will be placed
Dim i As Long
With Sheets("Index")
For i = 1 To .range("TABLES_S").Rows.count
SQL1 = "SELECT FROM " & .range("TABLES_S")(i, 1) & .range("TableClmn")(i, 1) & Sheets("Element").range("GESTATUS")(i, 1)
Debug.Print SQL1 ' loop through the ranges in excel sheet
Next i

Dim rngCell As range
For Each rngCell In range("GESTATUS")
Call execSQL(SQL1, "Get Element", "GEREGS")
Next Rng
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,834
Office Version
  1. 365
Platform
  1. Windows
Does your SQL statement work at all?

It seems to be missing quite an important part - the list of fields you want to return.

That would usually be between SELECT and FROM:

SELECT [Field1], [Field2]...[FieldX] FROM [TableName]

Though you can use * to return all fields.

SELECT * FROM [TableName]

Apart from that what problems are you having?
 

tatendatiffany

Board Regular
Joined
Mar 27, 2011
Messages
103
well the thing is i have the sql queries already but this excel in connecting to a database (DB2) so i have created a range for the database tables i will be looking through in the excel file. so basically it runs through the ranges in the excel in retrieves from database as they have the same names.

at the moment it doesn't seem to be working but i thought i was on the right direction.
So what should i add to the sql??

Thank you again
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,834
Office Version
  1. 365
Platform
  1. Windows
Sorry I'm not quite following, is everything working apart from the SQL part?

If that's the case it's probably because of what I mentioned.

What are the names of the queries?

Is this how you are getting those from the worksheet?
Rich (BB code):
.range("TABLES_S")(i, 1) & .range("TableClmn")(i, 1) & Sheets("Element").range("GESTATUS")(i, 1)
If it is then that could be easy to fix, I think.

Perhaps simply adding in the '*' I mentioned previously:
Rich (BB code):
SQL1 = "SELECT * FROM " & .range("TABLES_S")(i, 1) & .range("TableClmn")(i, 1) & Sheets("Element").range("GESTATUS")(i, 1)

Actually, there is something I've noticed about the code that I don't quite understand.

The 2nd loop seems to set what's in SQL1, but within the loop that's all it does.

When that loop finishes the value of SQL1 will be the last value it's given in the loop.

So when you use execSQL(SQL1...), SQL1 is always going to be that last value.

I hope that makes some sort of sense.:eek:

Anyway, is that what's supposed to happen?
 

tatendatiffany

Board Regular
Joined
Mar 27, 2011
Messages
103

ADVERTISEMENT

it kind of makes sense but the first loop i wanted it to run through the column that contains the table names and the 2nd loop runs thorugh the column with the column name from the table names
then the last loop is suppose to execute the sql of counting from the tables and column then i want to display the result on the sheet.:confused:

Does it make sense


Thanks again
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,695
Office Version
  1. 2013
Platform
  1. Windows
Code:
For i = 1 To .range("TABLES_S").Rows.count
SQL1 = "SELECT FROM " & [COLOR="Blue"].range("TABLES_S")[/COLOR](i, 1) & .range("TableClmn")(i, 1) & Sheets("Element").range("GESTATUS")(i, 1)

The loops don't seem to be even connected (they're in separate subs). And TABLE_S is a range variable and is only a single cell. You've used it as a string in your code which won't work. There's a lot of issues here. How many tables and columns are you using? Couldn't you just write a straightforward query and run it?
 

tatendatiffany

Board Regular
Joined
Mar 27, 2011
Messages
103

ADVERTISEMENT

so these are the columns i have:
ok so I have two column is the excel sheet containing 28 table names and then another column containing the column names of the table:

So i want a loop that goes into the table then the column name and runs all of them then display the result:
the table name (TABLES_S) and the column name (Tableclmn).
how do i get them to be connected, am i on the right page or do i have a lot of changes..please help me


Thank you
<table border="0" cellpadding="0" cellspacing="0" width="111"><col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <tbody><tr style="mso-height-source:userset;height:28.5pt" height="38"> <td class="xl2414" style="height:28.5pt;width:83pt" height="38" width="111">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2412" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2415" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:13.5pt" height="18"> <td class="xl2413" style="height:13.5pt;border-top:none" height="18">
</td> </tr> </tbody></table>
<table border="0" cellpadding="0" cellspacing="0" width="111"><col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <tbody><tr style="mso-height-source:userset;height:28.5pt" height="38"> <td class="xl2413" style="height:28.5pt;width:83pt" height="38" width="111">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2411" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl2414" style="height:12.75pt;border-top:none" height="17">
</td> </tr> <tr style="height:13.5pt" height="18"> <td class="xl2412" style="height:13.5pt;border-top:none" height="18">
</td> </tr> </tbody></table>
 

tatendatiffany

Board Regular
Joined
Mar 27, 2011
Messages
103
so below is an example of the two columns i want to look up data from; they are in the database and the sql will run through these two columns and display value:
<table class="MsoTableGrid" border="1" cellpadding="0" cellspacing="0"><tbody><!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-GB</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true" DefSemiHidden="true" DefQFormat="false" DefPriority="99" LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false" UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} table.MsoTableGrid {mso-style-name:"Table Grid"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-priority:59; mso-style-unhide:no; border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-border-insideh:.5pt solid windowtext; mso-border-insidev:.5pt solid windowtext; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} </style> <![endif]--> <table class="MsoTableGrid" style="margin-left:203.85pt;border-collapse:collapse;border:none;mso-border-alt: solid windowtext .5pt;mso-yfti-tbllook:1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:49.7pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> Ktpt80t
</td> <td style="width:43.7pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="58"> Pogen13
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:49.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> Ktpt81t
</td> <td style="width:43.7pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="58"> Inaccept
</td> </tr> <tr style="mso-yfti-irow:2"> <td style="width:49.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> Ktptc6t
</td> <td style="width:43.7pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="58"> Inratdrv
</td> </tr> <tr style="mso-yfti-irow:3"> <td style="width:49.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> ktptkit
</td> <td style="width:43.7pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="58"> inunacpt
</td> </tr> <tr style="mso-yfti-irow:4"> <td style="width:49.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> Ktptk9t
</td> <td style="width:43.7pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="58"> Cdminsec
</td> </tr> <tr style="mso-yfti-irow:5"> <td style="width:49.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> Ktptjt
</td> <td style="width:43.7pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="58"> Nuconpts
</td> </tr> <tr style="mso-yfti-irow:6"> <td style="width:49.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> Ktptcdt
</td> <td style="width:43.7pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="58"> inflitds
</td> </tr> <tr style="mso-yfti-irow:7;mso-yfti-lastrow:yes"> <td style="width:49.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> Ktp70t
</td> <td style="width:43.7pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="58"> cdragrb
</td> </tr> </tbody></table> </tbody></table>
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,834
Office Version
  1. 365
Platform
  1. Windows
How do those columns relate to the queries?

What does each column contain? Field names? Query/table names?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,695
Office Version
  1. 2013
Platform
  1. Windows
1) Is that all of the tables or just a sample of a larger list. I know its an irrelevant question but I like to know if I'm dealing with hundreds/thousands of items or just a handful.

2) What do you want the query results to return? Count? Sum?

3) Do you have another sub called execSQL? If so, what is the code for it. It seems to be used here:
Code:
Dim rngCell As range
For Each rngCell In range("GESTATUS")
Call [COLOR="Blue"]execSQL[/COLOR](SQL1, "Get Element", "GEREGS")
Next Rng

4) What is range("GESTATUS")? This doesn't seem to be explained.
Code:
SQL1 = "SELECT FROM " & _
.range("TABLES_S")(i, 1) & _
.range("TableClmn")(i, 1) & _
Sheets("Element")[COLOR="Blue"].range("GESTATUS")[/COLOR](i, 1)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,181
Members
409,856
Latest member
MAO
Top