CODEWITHSUNDEEP
sqlvbaselectexcel
JJRhythm
JJRhythm

Reputation: 643

Select data from query without column names

I'm using SQL in VBA to populate a spread sheet, but when I do this I get the data including the column headers. I am trying to find away to pull just the information out and no column names.

For example,

id name job
0  Tom  Repair
1  Bob  Tech

instead I want,

0  Tom  Repair
1  Bob  Tech

Upvotes: 3

Views: 35283

Answers (3)

nassao
nassao

Reputation: 121

You can run the mysql client with --skip-column-names

Upvotes: 11

Hari Seldon
Hari Seldon

Reputation: 1060

Understanding that you did not indicate MySQL, at my work we use the following (MySQL) query using the ADODB reference in excel. I am posting it as you may be able to adapt it for your use:

Sub AdaptMeToYourUse()
  ' Connection variables
      Dim conn As ADODB.Connection
      Dim server_name As String
      Dim database_name As String
      Dim user_id As String
      Dim password As String
      Dim port_number As String

  ' Establish connection to the database
      server_name = "myserverName" ' 127.0.0.1 if running from a locally
      database_name = "myDBname" ' Enter your database name here
      user_id = "LoginName" ' enter your user ID here
      password = "PWforLoginName" ' Enter your password here
      port_number = "1234"

      Set conn = New ADODB.Connection
      conn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
        & ";SERVER=" & server_name _
        & ";PORT=" & port_number _
        & ";DATABASE=" & database_name _
        & ";UID=" & user_id _
        & ";PWD=" & password _
        & ";OPTION=16427" ' Option 16427 = Convert LongLong to Int

      Set rs = New ADODB.Recordset

      sqlstr = "SELECT  count(" & SomeField & ") FROM " _
             & SomeTable & " WHERE " & SomeField & " = '" & var2Compare2SomeField & "' "
      rs.Open sqlstr, conn, adOpenStatic, adLockReadOnly, adCmdText 'set appropriate options for you

      Worksheets("SomeWB").Range(SomeRNG).CopyFromRecordset rs

End Sub

Upvotes: 1

AllisonC
AllisonC

Reputation: 3099

I'm not sure that you can have no column headers... Note: this is mysql, not sure about your dbms, but this is the closest I got: 

SELECT id as "", name as "", job as "" FROM table

Upvotes: 3

Related Questions