Gringoire
Board Regular
- Joined
- Nov 18, 2016
- Messages
- 71
- Office Version
- 365
- Platform
- Windows
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:
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.
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: