ADO connection retrieving data to Excel using EXCEL-VBA
 

How can I tell the "recordset" during the process of pasting the data to the
spreadsheet, not to paste blank values, I mean, only import values < "" ONLY?
Thanks

Coco
This is my working fine ADO connection but it is retrieving ALL records for
that table, including Blank values.


DBConnection = "Provider=MSDASQL.1 ........."
SqlString = "SELECT UNITX,DESCRIPTION from UNIT"
Dim TargetRange As Range
Set TargetRange = Worksheets("Sheet1").Range("A1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set rs = New ADODB.Recordset
With rs
.Open SqlString, DBConnection
TargetRange.Offset(0, 0).CopyFromRecordset rs ' recordset data
End With
rs.Close
Set rs = Nothing

ADO connection retrieving data to Excel using EXCEL-VBA
 

I am not sure if your data source uses blanks ("") or Null values, but here
is the way to account for both:
SqlString = "SELECT UNITX, DESCRIPTION from UNIT WHERE DESCRIPTION<'' AND
DESCRIPTION IS NOT NULL AND UNIT <'' AND UNIT IS NOT NULL"

"coco" wrote:

How can I tell the "recordset" during the process of pasting the data to the
spreadsheet, not to paste blank values, I mean, only import values < "" ONLY?
Thanks

Coco
This is my working fine ADO connection but it is retrieving ALL records for
that table, including Blank values.


DBConnection = "Provider=MSDASQL.1 ........."
SqlString = "SELECT UNITX,DESCRIPTION from UNIT"
Dim TargetRange As Range
Set TargetRange = Worksheets("Sheet1").Range("A1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set rs = New ADODB.Recordset
With rs
.Open SqlString, DBConnection
TargetRange.Offset(0, 0).CopyFromRecordset rs ' recordset data
End With
rs.Close
Set rs = Nothing

ADO connection retrieving data to Excel using EXCEL-VBA
 

In the database both values are blank and when I open the table this case
only happens for record 1.

I tried with NULL but it did not work, any other idea?

Thanks

Coco


"K Dales" wrote:

I am not sure if your data source uses blanks ("") or Null values, but here
is the way to account for both:
SqlString = "SELECT UNITX, DESCRIPTION from UNIT WHERE DESCRIPTION<'' AND
DESCRIPTION IS NOT NULL AND UNIT <'' AND UNIT IS NOT NULL"

"coco" wrote:

How can I tell the "recordset" during the process of pasting the data to the
spreadsheet, not to paste blank values, I mean, only import values < "" ONLY?
Thanks

Coco
This is my working fine ADO connection but it is retrieving ALL records for
that table, including Blank values.


DBConnection = "Provider=MSDASQL.1 ........."
SqlString = "SELECT UNITX,DESCRIPTION from UNIT"
Dim TargetRange As Range
Set TargetRange = Worksheets("Sheet1").Range("A1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set rs = New ADODB.Recordset
With rs
.Open SqlString, DBConnection
TargetRange.Offset(0, 0).CopyFromRecordset rs ' recordset data
End With
rs.Close
Set rs = Nothing

ADO connection retrieving data to Excel using EXCEL-VBA
 

I tried with "" and it works fine
Thank you

Coco

"K Dales" wrote:

I am not sure if your data source uses blanks ("") or Null values, but here
is the way to account for both:
SqlString = "SELECT UNITX, DESCRIPTION from UNIT WHERE DESCRIPTION<'' AND
DESCRIPTION IS NOT NULL AND UNIT <'' AND UNIT IS NOT NULL"

"coco" wrote:

How can I tell the "recordset" during the process of pasting the data to the
spreadsheet, not to paste blank values, I mean, only import values < "" ONLY?
Thanks

Coco
This is my working fine ADO connection but it is retrieving ALL records for
that table, including Blank values.


DBConnection = "Provider=MSDASQL.1 ........."
SqlString = "SELECT UNITX,DESCRIPTION from UNIT"
Dim TargetRange As Range
Set TargetRange = Worksheets("Sheet1").Range("A1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set rs = New ADODB.Recordset
With rs
.Open SqlString, DBConnection
TargetRange.Offset(0, 0).CopyFromRecordset rs ' recordset data
End With
rs.Close
Set rs = Nothing