VBA: OLEDB connection make me crazy...

Gringoire

New Member
Joined
Nov 18, 2016
Messages
29
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:

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.
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Gringoire

New Member
Joined
Nov 18, 2016
Messages
29
Hi,
I worked around the issue removing all the spaces in the sheet:

Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,146
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,136,864
Messages
5,678,214
Members
419,751
Latest member
TryingtoLearnVBA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top