Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macro help in MS Excel to connect to SQL database 1

Status
Not open for further replies.
PleaseGiveHelp

PleaseGiveHelp

Programmer
Oct 29, 2002
131
US
I'm pretty new with creating macros and have only created the very simple macros at that. I have an excel spreadsheet that I will get each week (with different data) and I will need to focus on the data in the D$ column. I'll need to create a macro that I can run once I open up this spreadsheet that will look at the D$ column, go to SQL server and run a query (select short_name from account where short_name like [D$ column contents]) and then from there will overwright the D$ column with the results of the query. It will do that for every row. Is this possible? How woudl I go about it?
 
Sort by date Sort by votes
Are you wanting an ENTIRELY DIFFERENT QUERY each time?

Once a QueryTable has been created (added), you do NOT have to add it again!!!

In fact, you can programatically change the database, table(s), SQL. I do this ALL THE TIME! I nearly NEVER ADD a querytable. I set ONE query table up and then modify the hell out of it in my code.
Code: 
for each c in MyColumnD_Range
   With Sheets("Query").[I1].QueryTables(1)
        .Connection = _
        "ODBC;DSN=landmarkprod;UID=landmarkdata;APP=Microsoft Office XP;DATABASE=Landmark;Trusted_Connection=Yes"
        .CommandText = _
        "SELECT account.short_name " & _
        "FROM Landmark.dbo.account account " & _
        "WHERE (account.short_name Like '" & c.value & "%')"
        .Refresh BackgroundQuery:=False
    End With

Next
:)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
The reason I wanted to do the add-query is because its going to be different spreadsheet every time. And I wanted to make things easier by just having the button (macro) do it all. Otherwise each time that I received a spreadsheet I'd have to add the same query as last time and run the macro whereas if I put it all in the macro all I have to do is hit the button which would create the query and voila it'd all just work. Thats my hope at least. Does this make sense?
So in that case...obviously I don't want to add the query each time within my loop but I still have to add the query at the beginning...after my code can I then move down to the D2 column and add your code or something similar? HOw do I introduce c or MyColumnD_Range? So this is similar to what I'll end up with?

Sub test2()
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=landmarkprod;UID=landmarkdata;APP=Microsoft Office XP;WSID=XPJOHNSONU-M3;DATABASE=Landmark;Trusted_Connection=Yes" _
, Destination:=Range("I1"))
.CommandText = Array( _
"SELECT account.short_name FROM Landmark.dbo.account account WHERE (account.short_name Like '" & [D1] & "%')" _
)
.Name = "Query from landmarkprod_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

For Each c In MyColumnD_Range
With Sheets("Query").[I3].QueryTables(1)
.Connection = _
"ODBC;DSN=landmarkprod;UID=landmarkdata;APP=Microsoft Office XP;DATABASE=Landmark;Trusted_Connection=Yes"
.CommandText = _
"SELECT account.short_name " & _
"FROM Landmark.dbo.account account " & _
"WHERE (account.short_name Like '" & c.Value & "%')"
.Refresh BackgroundQuery:=False
End With

Next

End Sub
 
Upvote 0 Downvote
Oh yeah you're right. Hmm...I've never created a for statement with 2 different variables. How would this work? Obviously I don't want it all to go in I3...but rather start at say I3 and work all the way down the I column...would i have a for loop within a for loop?
 
Upvote 0 Downvote
What do you want to happen once a resultset is returned to COlumn I and before the next query/resultset is requested?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
I want it simply to put the results in the I column, enter and find the results for the next row and put them in the corresponding I column until each row has results.
 
Upvote 0 Downvote
So your source data is in column D- D1, D2, D3 etc

And your results are in Column I.

So the value in D1 will return ONE and ONLY ONE value which you want in I1 and so on...

You start the process and it reads down thru the values in column D placing a value in that row in column I from the query resultset.

Do I have it correctly?


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
I'd rather it actually store the new value in the D column (overwrite the D column as it is with the new data) but I couldn't figure that out so I was thinking instead of adding into my macro code to copy the I column to the D column...
But yes it will return one and only one piece of value as I am looking for the primary key on the database table.
Yes you have the flow correct!
 
Upvote 0 Downvote
I suggest using a separate sheet -- Sheets("Query") -- for the MS Query because the resultset includes the column header(s).
Code: 
Sub Main()
'start this procedure on your DATA DISPLAY SHEET
    Dim MyColumnD_Range As Range
    Set MyColumnD_Range = Range(Cells(1, "D"), Cells(1, "D").End(xlDown))
    For Each c In MyColumnD_Range
       With Sheets("Query").QueryTables(1)
            .Connection = _
            "ODBC;DSN=landmarkprod;UID=landmarkdata;APP=Microsoft Office XP;DATABASE=Landmark;Trusted_Connection=Yes"
            .CommandText = _
            "SELECT account.short_name " & _
            "FROM Landmark.dbo.account account " & _
            "WHERE (account.short_name Like '" & c.Value & "%')"
            .Refresh BackgroundQuery:=False
            With .Cells(1, 1)
                RowCount = .CurrentRegion.Rows.Count
                If RowCount = 2 Then
                    c.Value = .Offset(1, 0).Value
                Else
                    'what happens if NOTHING is returned or MORE THAN 1 ROW is returned???
                End If
            End With
        End With
    Next
End Sub
:)



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Hmmm something is not working the way it should. I get a subscript out of range. I'm trying to create my macro in a way that would work in a fresh file where the macro would do all the work. Here is the code. It goes out of range at the for loop:

Sub CrossingTest()

Sheets.Add
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Query"
Sheets("Sheet1").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=landmarkprod;UID=landmarkdata;APP=Microsoft Office XP;DATABASE=Landmark;Trusted_Connection=Yes" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT account.short_name" & Chr(13) & "" & Chr(10) & "FROM Landmark.dbo.account account" & Chr(13) & "" & Chr(10) & "WHERE (account.short_name Like '+''+''$D1''+''%')" _
)
.Name = "Query from landmarkprod_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Dim MyColumnD_Range As Range
Set MyColumnD_Range = Range(Cells(1, "D"), Cells(1, "D").End(xlDown))
For Each c In MyColumnD_Range
With Sheets("Query").QueryTables(1) ---OUT OF RANGE!
.Connection = _
"ODBC;DSN=landmarkprod;UID=landmarkdata;APP=Microsoft Office XP;DATABASE=Landmark;Trusted_Connection=Yes"
.CommandText = _
"SELECT account.short_name " & _
"FROM Landmark.dbo.account account " & _
"WHERE (account.short_name Like '" & c.Value & "%')"
.Refresh BackgroundQuery:=False
With .Cells(1, 1)
RowCount = .CurrentRegion.Rows.Count
If RowCount = 2 Then
c.Value = .Offset(1, 0).Value
Else
'End Sub
End If
End With
End With
Next
End Sub


 
Upvote 0 Downvote
Perhaps it is out of range because the active sheet is NOT the query sheet as I had hoped...its the first page...how can I make a certain sheet the active sheet?
 
Upvote 0 Downvote
I set wsQuery and wsData as query sheet and data sheet. Sheets do not have to be activated or ranges selected. In fact, selecting and activating slows down your process.
Code: 
Sub CrossingTest()
    Dim wsQuery As Worksheet, wsData As Worksheet
    Set wsData = ActiveSheet
    Set wsQuery = Sheets.Add
    wsQuery.Name = "Query"

    With wsQuery.QueryTables.Add(Connection:= _
        "ODBC;DSN=landmarkprod;UID=landmarkdata;APP=Microsoft Office XP;DATABASE=Landmark;Trusted_Connection=Yes" _
        , Destination:=wsQuery.Range("A1"))
        .CommandText = _
        "SELECT account.short_name " & _
        "FROM Landmark.dbo.account account " & _
        "WHERE (account.short_name Like '" & wsData.[D1] & "%')"
        .Name = "Query from landmarkprod_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    
    Dim MyColumnD_Range As Range
    Set MyColumnD_Range = Range(wsData.Cells(1, "D"), wsData.Cells(1, "D").End(xlDown))
    For Each c In MyColumnD_Range
       With Sheets("Query").QueryTables(1)
            .CommandText = _
            "SELECT account.short_name " & _
            "FROM Landmark.dbo.account account " & _
            "WHERE (account.short_name Like '" & c.Value & "%')"
            .Refresh BackgroundQuery:=False
            With .Cells(1, 1)
                RowCount = .CurrentRegion.Rows.Count
                If RowCount = 2 Then
                    c.Value = .Offset(1, 0).Value
                Else
                    'End Sub
                End If
            End With
        End With
    Next
    Set wsData = Nothing
    Set wsQuery = Nothing
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
It looks like it worked for the first piece of data however then I got an error once it got to the end, specifically:

With .Cells(1, 1)
RowCount = .CurrentRegion.Rows.Count
If RowCount = 2 Then
c.Value = .Offset(1, 0).Value
Else
'End Sub
End If
End With
End With
Next
Set wsData = Nothing
Set wsQuery = Nothing
End Sub


The error: Object doesn't support this method or property.
 
Upvote 0 Downvote
sorry,
Code: 
    For Each c In MyColumnD_Range
        With wsQuery
            With .QueryTables(1)
                .CommandText = _
                "SELECT account.short_name " & _
                "FROM Landmark.dbo.account account " & _
                "WHERE (account.short_name Like '" & c.Value & "%')"
                .Refresh BackgroundQuery:=False
            End With
            With .Cells(1, 1)
                RowCount = .CurrentRegion.Rows.Count
                If RowCount = 2 Then
                    c.Value = .Offset(1, 0).Value
                Else
                    'End Sub
                End If
            End With
        End With
    Next


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Ok so it runs through all of the data but it puts it all in I1. How can I get D1 to place in I1 and D2 to place in I2 etc...I want to see all of the data. This puts it all in I1 and overwrites it each time.
 
Upvote 0 Downvote
Sorry I meant the A column. It puts everything in A1. I need to see the values in A1 - A*. Do I need another loop?
 
Upvote 0 Downvote
I don't understand. The code OVERWRITES the value in column D as you requested. I just tested it!

If you want it in column I
Code: 
                If RowCount = 2 Then
                    wsData.Cells(c.Row, "I").Value = .Offset(1, 0).Value
                Else
                    'End Sub
                End If


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Holy cow you're right. I didn't realize that it was actually overwriting D at this point. It totally does. You are amazing! I thank you so much. Now I will simply add code to delete the added worksheet at the end and re-save and I'll be all set. Thank you so much for your help!
 
Upvote 0 Downvote
Code: 
   Application.DisplayAlerts = False
   wsQuery.Delete
   ActiveWorkbook.Save
   Application.DisplayAlerts = True
[code]
:-)

Skip,
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
1K
strongm
strongm
Kymenglish
  • Locked
  • Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
619
strongm
strongm
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
1K
strongm
strongm
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top