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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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>
 
Upvote 0
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>
 
Upvote 0
How do those columns relate to the queries?

What does each column contain? Field names? Query/table names?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top