Trailing spaces in values on extracting data from Db2 using VBA Queries

[Praneeth30]

Hey guys,

I was trying to extract data from Mainframe Db2 using VB and have some issue in the way it is pulling data.

This is the query im using:
SQLSelectStatement = "SELECT" & " " & strColumnD & " " & " " & "FROM" & " " & strdb & strtable & " " & "WHERE " & "INVN_CTL_NBR= " & "'" & strICNval & "'" & " " & " WITH UR"

adoDB2RecordSet.Open SQLSelectStatement, db2con
Set adoDB2RecordSet = db2con.Execute(SQLSelectStatement)
k = 0
Do While adoDB2RecordSet.EOF = False
arrayval = varcol(k)
Cells(index1, arrayval).Value = adoDB2RecordSet(0).Value

But when it is returning the data for columns which are defined as CHAR(X) in Db2 - Trailing spaces are returned as shown below:
(Spaces are represeneted in double quotes " "")

For example:
Corresponding DB2 Table Corresponding DB2 Column Db2 Value 1 Db2 Value 2
ABCD2 EE_NM
ELLIS" "
SAADIA" "

 

[Chihiro]

Not a direct solution, but you can use Trim() to get rid of trailing space(s).

Alternately, try using MS Query instead of VBA, it's much simpler to maintain and you can see preview of data before returning data to Excel.

 

[Praneeth30]

I have tried using Replace as below
Cells(index1, arrayval).Value = Replace(Cells(index1, arrayval).Value, " ", "")
but it is throwing me Runtime error 6: Overflow.

 

[Chihiro]

What did you define variable index1 & arrayval as?

If you used integer, that may cause issue. Try using, long instead.

Also, try using Range instead of cells. Something like below.

Range("your range").Replace " ", "", xlPart