PDA

View Full Version : Solved: D*MN Column Headers!!!!



maxhayden
09-07-2009, 12:39 PM
When importing data into Excel via SQL query integrated into VBA code - how do I just import the data - without the header columns?

When I run the query to pull 1 row off an access database it is returning the coumn headers for each row - which is pretty annoying.

There doesn't seem to be any help on the almight google!

Your collective genius will be most greatly appreciated.

Thanks in advance!

Max

p.s. this post is also up in the Excell Help forum. Wasn't too sure where to post this!

Bob Phillips
09-08-2009, 11:47 AM
Max, just use ADO with your SQL query string. The returned recordset has items and headers separate, so you can ignore headers.

maxhayden
09-14-2009, 12:36 AM
I'm fairly new to this. How do I do that?

This is my query string....

varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\Users\*****\Documents\IDSDB.mdb; Driver={Driver do Microsoft Access (*.mdb)}"
varSQL = "SELECT [instructionsreceivedDS-090816].[Instruction Reference],
[instructionsreceivedDS-090816].[Instruction Type],
[instructionsreceivedDS-090816].[Instruction Received],
[instructionsreceivedDS-090816].[Given Name],
[instructionsreceivedDS-090816].[Surname],
[instructionsreceivedDS-090816].[Records],
[instructionsreceivedDS-090816].[Instructor Name]
FROM [instructionsreceivedDS-090816]
WHERE Left( [instructionsreceivedDS-090816].[Instruction Reference], 6) = '" + y + "'"

With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=Cells(i, 1))

.CommandText = varSQL

.Name = "Query-39008"

.Refresh BackgroundQuery:=False

End With

maxhayden
09-14-2009, 02:34 AM
I've done it with the help of Brad the legend:

Dim varConnection
Dim varSQL
Dim SQL As String
Dim SQLrs As New ADODB.Recordset
Dim SQLcon As New ADODB.Connection

- code -

SQLcon.ConnectionString = varConnection
SQLcon.Open
SQLrs.Open varSQL, SQLcon, adOpenStatic


Thanks for the ADO hint XLD