VBA: OLEDB connection make me crazy...

[Gringoire]

Hi guys!
I'm using a few lines of VBA to import in a Excel client file (as a table) some data coming from another Excel data file (not managed by me).

Recently my supplier changed the way to produce the Excel data file:
originally it was an Excel output file coming from an Access query on a Oracle DB.
Now it is an Excel output file coming from some software (not Access) interacting with the same Oracle DB

The two excel output files (the old one and the new one) are apparently identical from the first to the last row: same worksheet (just one), same columns, same format for each column field, etc.

The only visible difference is that in the old data file the only worksheet was called "Consuntivo_commessa_DB"; in the new file the same worksheet is simply called "Sheet1"

So I had to modify just one row in my VBA code:

.CommandText = Array("Consuntivo_Commessa_Dett")
changed in:
.CommandText = Array("Sheet1$")

here's the code:

Sub Aggiornamento_connessione(FILE)
'
' FUNZIONALE: Aggiornamento connessione Dati da file Excel esterno
'
With ActiveWorkbook.Connections("Consuntivo").OLEDBConnection
    .BackgroundQuery = False 
   [COLOR=#ff0000] '.CommandText = Array("Consuntivo_Commessa_Dett")[/COLOR]
    [COLOR=#0000ff].CommandText = Array("Sheet1$")[/COLOR]
    .CommandType = xlCmdTable
    'Stringa di connessione
    .Connection = Array( _
    "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & FILE _
    , _
    ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path" _
    , _
    "="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=" _
    , _
    "2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encry" _
    , _
    "pt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=Fals" _
    , _
    "e;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:B" _
    , "ypass ChoiceField Validation=False")

    .RefreshOnFileOpen = True
    .SavePassword = False
    .SourceConnectionFile = ""
    .SourceDataFile = FILE
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    .AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Consuntivo")
    .Name = "Consuntivo"
    .Description = "Connessione a file Excel di dati esterno"
End With
ActiveWorkbook.Connections("Consuntivo").Refresh    
End Sub

The issue is that now in one (just one!) of the column fields in my table is appearing a trailing space. This space does not exist in the original data file received from my supplier.
all the data in this column simple general data like "B0100" or "FA020" are now changed in "B0100 " or "FA020 "

I guess this is in some way related with the fact that I must add a $ at the end of table name in order to import it into my file.
I tried with .CommandText = Array("Sheet1") without $ but it doesn't work.

I have no idea about how to solve this issue.
Any hint?

Sorry for the long post.

 

[Gringoire]

Any suggestion?
Here you can find the two version of data files, if it could help...

http://www.filedropper.com/consuntivoc05848new

http://www.filedropper.com/consuntivoc05848access

thanks

 

[Gringoire]

Hi,
I worked around the issue removing all the spaces in the sheet:

Sub Trimspaces()'
' Elimino gli spazi nella colonna CdC
'
'
Dim rng As Range
ultimariga = Worksheets("Dati").Cells(Rows.Count, "A").End(xlUp).Row
Col_number = Worksheets("Dati").Cells(1, Columns.Count).End(xlToLeft).Column
vArr = Split(Cells(1, Col_number).Address(True, False), "$")
ultimacolonna = vArr(0)
ultimacella = ultimacolonna & ultimariga
Set rng = Range("A1:" & ultimacella)
rng.Value = Application.Trim(rng)


End Sub

 

[RoryA]

It appears that opening the workbook in Excel and saving it also removes the trailing spaces. I guess that somewhere in the generated XML, it's making that column fixed length.