Hi,

Can someone tell me how to exclude the header or titles from the
results of an ODBC query against a MySQL DB.

Here is my code in case someone else would like to use it.

Sub get_oem_data(RowIndex, LastRowOfData, Program)

Dim oemConn As String
Dim oemSql As String
Dim oQt As QueryTable
Dim start_row As Integer

Application.StatusBar = "Clean out old OEM Data"
Sheets("oem_data").Select
current_sheet = ActiveSheet.Name
'LastRealCell_ftr =
RealLastCell(Sheets("Insp_data")).Address(RowAbsol ute:=False,
ColumnAbsolute:=False)
'Range("A1", LastRealCell_ftr).Select
'Range("A1", "IV6536").Select
Columns("A:IV").Select
Selection.ClearContents
'Selection.Delete Shift:=xlShiftToLeft
Cells.Select
Range("IV1").Activate
Selection.Clear


oemConn = "ODBC;DATABASE=databaseName;DESCRIPTION=MySQL ODBC 3.51
Drive
DSN;DSN=DSNConnectionName;OPTION=0;;PORT=0;SERVER= server.com;UID=userlogin"

Sheets("variables").Select

oemSql = "SELECT * "

oemSql = oemSql & "FROM aces_data.oem_data_fpm oem_data_fpm_0 "

oemSql = oemSql & "WHERE 1 AND ( ( "

If (RowIndex < start_row) Then

oemSql = oemSql & " OR ( "
End If

If (Cells(RowIndex, "C").Value < "") Then
oemSql = oemSql & " AND oem_data_fpm_0.Program=" & "'"
oemSql = oemSql & Cells(RowIndex, "C") & "'"
End If

oemSql = oemSql & " ) "


oemSql = oemSql & " ) "

oemSql = oemSql & " ORDER BY oem_data_fpm_0.id "
'MsgBox oemSql
Sheets("oem_data").Select
Cells(LastRowOfData, "A").Value = oemSql

If (LastRowOfData < 1) Then
LastRowOfData = LastRowOfData + 1
End If
insert_here_cell = "A" & LastRowOfData
Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=oemConn, _
Destination:=Range(insert_here_cell), _
Sql:=oemSql)

oQt.Refresh BackgroundQuery:=False ' explicitly set background off
Call move_oem_data_to_tcgaz_sheet
End Sub

QueryTables has a "FieldNames" argument which by default is True. Set it to
false to lose the fieldnames. With a different format it may seem easier to
control the various settings. See my example below:

With ActiveSheet.QueryTables.Add(Connection:=MyString,
Destination:=Range(ActiveCell.Address))
.AdjustColumnWidth = True
.BackgroundQuery = False
.CommandText = MySQL
.CommandType = xlCmdSql
.Destination = "Import running since: " & Format(Time, "HH:MM:SS AM/PM")
.FieldNames = False
.HasAutoFormat = True
.Name = "RangeNameForMyImport"
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.Refresh 'BackgroundQuery = True
End With

There are many more arguments available, but you may also wish to explore
ADO, since it uses newer technology than DAO.

HTH.

" wrote:

Hi,

Can someone tell me how to exclude the header or titles from the
results of an ODBC query against a MySQL DB.

Here is my code in case someone else would like to use it.

Sub get_oem_data(RowIndex, LastRowOfData, Program)

Dim oemConn As String
Dim oemSql As String
Dim oQt As QueryTable
Dim start_row As Integer

Application.StatusBar = "Clean out old OEM Data"
Sheets("oem_data").Select
current_sheet = ActiveSheet.Name
'LastRealCell_ftr =
RealLastCell(Sheets("Insp_data")).Address(RowAbsol ute:=False,
ColumnAbsolute:=False)
'Range("A1", LastRealCell_ftr).Select
'Range("A1", "IV6536").Select
Columns("A:IV").Select
Selection.ClearContents
'Selection.Delete Shift:=xlShiftToLeft
Cells.Select
Range("IV1").Activate
Selection.Clear


oemConn = "ODBC;DATABASE=databaseName;DESCRIPTION=MySQL ODBC 3.51
Drive
DSN;DSN=DSNConnectionName;OPTION=0;;PORT=0;SERVER= server.com;UID=userlogin"

Sheets("variables").Select

oemSql = "SELECT * "

oemSql = oemSql & "FROM aces_data.oem_data_fpm oem_data_fpm_0 "

oemSql = oemSql & "WHERE 1 AND ( ( "

If (RowIndex < start_row) Then

oemSql = oemSql & " OR ( "
End If

If (Cells(RowIndex, "C").Value < "") Then
oemSql = oemSql & " AND oem_data_fpm_0.Program=" & "'"
oemSql = oemSql & Cells(RowIndex, "C") & "'"
End If

oemSql = oemSql & " ) "


oemSql = oemSql & " ) "

oemSql = oemSql & " ORDER BY oem_data_fpm_0.id "
'MsgBox oemSql
Sheets("oem_data").Select
Cells(LastRowOfData, "A").Value = oemSql

If (LastRowOfData < 1) Then
LastRowOfData = LastRowOfData + 1
End If
insert_here_cell = "A" & LastRowOfData
Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=oemConn, _
Destination:=Range(insert_here_cell), _
Sql:=oemSql)

oQt.Refresh BackgroundQuery:=False ' explicitly set background off
Call move_oem_data_to_tcgaz_sheet
End Sub

CAn you give me an ADO example, I cannot get it ADO to work

I can't get past the first line, seems like my excel is out of date or
missing something


Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String

I have Excel 2003 with VB 6.3

Thank you


quartz wrote:
QueryTables has a "FieldNames" argument which by default is True. Set it to
false to lose the fieldnames. With a different format it may seem easier to
control the various settings. See my example below:

With ActiveSheet.QueryTables.Add(Connection:=MyString,
Destination:=Range(ActiveCell.Address))
.AdjustColumnWidth = True
.BackgroundQuery = False
.CommandText = MySQL
.CommandType = xlCmdSql
.Destination = "Import running since: " & Format(Time, "HH:MM:SS AM/PM")
.FieldNames = False
.HasAutoFormat = True
.Name = "RangeNameForMyImport"
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.Refresh 'BackgroundQuery = True
End With

There are many more arguments available, but you may also wish to explore
ADO, since it uses newer technology than DAO.

HTH.

" wrote:

Hi,

Can someone tell me how to exclude the header or titles from the
results of an ODBC query against a MySQL DB.

Here is my code in case someone else would like to use it.

Sub get_oem_data(RowIndex, LastRowOfData, Program)

Dim oemConn As String
Dim oemSql As String
Dim oQt As QueryTable
Dim start_row As Integer

Application.StatusBar = "Clean out old OEM Data"
Sheets("oem_data").Select
current_sheet = ActiveSheet.Name
'LastRealCell_ftr =
RealLastCell(Sheets("Insp_data")).Address(RowAbsol ute:=False,
ColumnAbsolute:=False)
'Range("A1", LastRealCell_ftr).Select
'Range("A1", "IV6536").Select
Columns("A:IV").Select
Selection.ClearContents
'Selection.Delete Shift:=xlShiftToLeft
Cells.Select
Range("IV1").Activate
Selection.Clear


oemConn = "ODBC;DATABASE=databaseName;DESCRIPTION=MySQL ODBC 3.51
Drive
DSN;DSN=DSNConnectionName;OPTION=0;;PORT=0;SERVER= server.com;UID=userlogin"

Sheets("variables").Select

oemSql = "SELECT * "

oemSql = oemSql & "FROM aces_data.oem_data_fpm oem_data_fpm_0 "

oemSql = oemSql & "WHERE 1 AND ( ( "

If (RowIndex < start_row) Then

oemSql = oemSql & " OR ( "
End If

If (Cells(RowIndex, "C").Value < "") Then
oemSql = oemSql & " AND oem_data_fpm_0.Program=" & "'"
oemSql = oemSql & Cells(RowIndex, "C") & "'"
End If

oemSql = oemSql & " ) "


oemSql = oemSql & " ) "

oemSql = oemSql & " ORDER BY oem_data_fpm_0.id "
'MsgBox oemSql
Sheets("oem_data").Select
Cells(LastRowOfData, "A").Value = oemSql

If (LastRowOfData < 1) Then
LastRowOfData = LastRowOfData + 1
End If
insert_here_cell = "A" & LastRowOfData
Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=oemConn, _
Destination:=Range(insert_here_cell), _
Sql:=oemSql)

oQt.Refresh BackgroundQuery:=False ' explicitly set background off
Call move_oem_data_to_tcgaz_sheet
End Sub

Yeah, you are using early binding, which means you need to set a reference to
the following:

Microsoft Office ActiveX Data Objects 2.X Library

Where the 2.X is the highest number shown. To do this, in the VBE, {Tools}
then {References}.

HTH - and sorry it took so long to reply - hope you get this!


" wrote:

CAn you give me an ADO example, I cannot get it ADO to work

I can't get past the first line, seems like my excel is out of date or
missing something


Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String

I have Excel 2003 with VB 6.3

Thank you


quartz wrote:
QueryTables has a "FieldNames" argument which by default is True. Set it to
false to lose the fieldnames. With a different format it may seem easier to
control the various settings. See my example below:

With ActiveSheet.QueryTables.Add(Connection:=MyString,
Destination:=Range(ActiveCell.Address))
.AdjustColumnWidth = True
.BackgroundQuery = False
.CommandText = MySQL
.CommandType = xlCmdSql
.Destination = "Import running since: " & Format(Time, "HH:MM:SS AM/PM")
.FieldNames = False
.HasAutoFormat = True
.Name = "RangeNameForMyImport"
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.Refresh 'BackgroundQuery = True
End With

There are many more arguments available, but you may also wish to explore
ADO, since it uses newer technology than DAO.

HTH.

" wrote:

Hi,

Can someone tell me how to exclude the header or titles from the
results of an ODBC query against a MySQL DB.

Here is my code in case someone else would like to use it.

Sub get_oem_data(RowIndex, LastRowOfData, Program)

Dim oemConn As String
Dim oemSql As String
Dim oQt As QueryTable
Dim start_row As Integer

Application.StatusBar = "Clean out old OEM Data"
Sheets("oem_data").Select
current_sheet = ActiveSheet.Name
'LastRealCell_ftr =
RealLastCell(Sheets("Insp_data")).Address(RowAbsol ute:=False,
ColumnAbsolute:=False)
'Range("A1", LastRealCell_ftr).Select
'Range("A1", "IV6536").Select
Columns("A:IV").Select
Selection.ClearContents
'Selection.Delete Shift:=xlShiftToLeft
Cells.Select
Range("IV1").Activate
Selection.Clear


oemConn = "ODBC;DATABASE=databaseName;DESCRIPTION=MySQL ODBC 3.51
Drive
DSN;DSN=DSNConnectionName;OPTION=0;;PORT=0;SERVER= server.com;UID=userlogin"

Sheets("variables").Select

oemSql = "SELECT * "

oemSql = oemSql & "FROM aces_data.oem_data_fpm oem_data_fpm_0 "

oemSql = oemSql & "WHERE 1 AND ( ( "

If (RowIndex < start_row) Then

oemSql = oemSql & " OR ( "
End If

If (Cells(RowIndex, "C").Value < "") Then
oemSql = oemSql & " AND oem_data_fpm_0.Program=" & "'"
oemSql = oemSql & Cells(RowIndex, "C") & "'"
End If

oemSql = oemSql & " ) "


oemSql = oemSql & " ) "

oemSql = oemSql & " ORDER BY oem_data_fpm_0.id "
'MsgBox oemSql
Sheets("oem_data").Select
Cells(LastRowOfData, "A").Value = oemSql

If (LastRowOfData < 1) Then
LastRowOfData = LastRowOfData + 1
End If
insert_here_cell = "A" & LastRowOfData
Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=oemConn, _
Destination:=Range(insert_here_cell), _
Sql:=oemSql)

oQt.Refresh BackgroundQuery:=False ' explicitly set background off
Call move_oem_data_to_tcgaz_sheet
End Sub