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 Chriss Miller 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
Joined
Oct 29, 2002
Messages
131
Location
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
PleaseGiveHelp

1. On a blank sheet, use Data/Get External Data to create a query to your SQL Database.

2. After getting the result you want, turn on the macro recorder and edit the query -- next, next, next, finish -- turn off the recorder.

3. inspect your new macro and clean it up -- post it here and get some help.

4. Once you have your Col D data, you will write a procedure (macro) to loop thru each value i that column and run the query for the value and get the returned value and use it however you want.

How's that for a plan? :-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Ok the Get External data is NOT working. It will not let me reference a cell. I need to create my query to select short_name where short_name like $D1. It does not recognize this. I can put in literal values, etc but it will not let me do anything like ' + $D1 + %'. I just don't get any data returned. I'm wondering if it would be easier to just create all of this in VB but I don't really know how to start. Unless I am putting the ' + $D1 + %' in where it should say something else. Ideas?
 
Upvote 0 Downvote
Ms Query Assumes that the first row are column headings.

What is ' + $D1 + %'???

What is re reason to use "short name" which is really a Cell Reference?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Sorry yes $D1 is my cell reference. I want to reference the contents IN the cell in my query. So I'm looking for data to populate where short_name is like the contents in the cell. Short_name is a column in my database table and I want the relevent data to show up in my excel spreadsheet.
 
Upvote 0 Downvote
Have you used queries before?

This is a CRITERIA that you specify in your query. It is called a Parameter Query. I would do it thusly...

Without regard to your D1 reference, format your query and return the data to your sheet.

Turn on the macro recorder and then Edit the query you just made AS PREVIOUSLY DESCRIBED.

Turn OFF the recorder and go inspect the code.

Post the code and I'll help you modify it to use the value in D1

:-)


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Ok here goes. This is what the macro produced:

With Selection.QueryTable
.Connection = _
"ODBC;DSN=landmarkprod;UID=landmarkdata;APP=Microsoft Office XP;DATABASE=Landmark;Trusted_Connection=Yes"
.CommandText = Array( _
"SELECT account.short_name" & Chr(13) & "" & Chr(10) & "FROM Landmark.dbo.account account" & Chr(13) & "" & Chr(10) & "WHERE (account.short_name Like '52-014269.2%')" _
)
.Refresh BackgroundQuery:=False
End With
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D2").Select
End Sub

I added the copy-paste because I'll eventually need to take the new data and place it into the D column.
 
Upvote 0 Downvote
I would suggest that you put your query on a separate sheet and use the results on the original sheet
Code: 
Sub ATest()
'this assumes a sheet named "Query" returning data to A1
   With Sheets("Query").[A1].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 '" & [D1] & "%')"
        .Refresh BackgroundQuery:=False
    End With
End Sub
:-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
Cool I'll try this. One question though. If I wanted to just place the data on the same sheet, say in column $E1, would I simply not put anyting within the parantheses after Sheets?
 
Upvote 0 Downvote
Ok this may complicate matters as I now notice I need to give some more background because this is not going to work. Basically I'm going to get files on a daily basis with the D column filled in with the substring of the short_name field. I'll need to simply use the macro to overwrite the D column with the actual short_name that I pull from the database.
The macro you've been helping me with may pose a problem because I can't run the import data and create the query each time I want to run this. I need to find a way to add that to the macro so it'll automatically store the query. Does this make sense?
 
Upvote 0 Downvote
You can't "automatically" store the query because it CHANGES with the data that you put in D1.

Why can't your run that procedure whenever? It does not CREATE anything. It refreshes the query based on the current data.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
It will change with the data. Yes. However the query is still going to be UPDATE $D1 set $D1 = short_name where short_name like '" & [D1] & "%' right? So can't I put that in a macro and just run the macro each time I receive the spreadsheet?
 
Upvote 0 Downvote
And I do see its in the macro but when I strip the cells of the query behind it it gives me an hour at this piece of code:

With Activesheet.QueryTables(1)

because I do not have the query table any longer. So since I don't want to have the query table behind my sheet am I going about this wrong? Perhaps I don't want to use query tables...
 
Upvote 0 Downvote
I don't think the query tables is in my macro because I get a subscript out of range (the 1 since there is none) when I run it...
 
Upvote 0 Downvote
1. You have to CREATE the querytable via Data/Get External Data

2. Once created, run the macro. :-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Upvote 0 Downvote
hmmm so there's no way to design the macro to create the query then...so I'd have to manually create the query and then run the macro each time?
 
Upvote 0 Downvote
You can create your query with the Add method of the QueryTables class:
Code: 
Add(Connection, Destination As Range, [Sql]) As QueryTable

Hope This Help
PH.
 
Upvote 0 Downvote
Great. I'm actually going with this instead which is similar:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=landmarkprod;UID=landmarkdata;APP=Microsoft Office XP;DATABASE=Landmark;Trusted_Connection=Yes" _
, Destination:=Range("I1"))
.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

So now that I can get this far...and when I run the macro it populates my I column (I figure I can figure out later on how to get it to overwrite the D column) but in the meantime I need to figure out how to get the macro to not only read the D1 column but all of the D column (as long as it contains a value). So I would need to create a loop. I'm concerned because of the reference to D in my query definition. Is this possible?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word
Replies
7
Views
2K
strongm
strongm
Kymenglish
  • Locked
  • Question Question
Automated data entry code in MS Excel VB - Edge code how to hit enter to remove popups
Replies
3
Views
748
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 Question
CTRL + BREAK from second macro button
Replies
5
Views
1K
strongm
strongm
remeng
  • Locked
  • Helpful tip Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
2K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top