Ignore table formatting when importing data using OLEDB

S

support

I am using Excel 2010. I want to write a macro in a 'consolidate' XLSM
file that will query another selected XLSM file and import data from
one of the range names in that file, into the 'consolidate' file.

When the import occurs, it formats the imported table using the
default Excel 2010 table style (see screenshot), however i don't want
to format the cells - i just want to import raw data and leave the
cells formatted as they are in the 'consolidate' XLSM file.

The PreserveFormatting property has no impact, as the damage is
already done once the query is added.

Is there any way to import just raw data from another XLSM file?

Below is my code i am currently using:

With ActiveSheet.ListObjects.Add(SourceType:=0,
Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User
ID=Admin;Data Source=""" & strSelectedFile & """;Mode=Share Deny
None;Extended Properties=Excel 12.0;Jet" _
, _
" OLEDB:System database="""";Jet OLEDB:Registry
Path="""";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=Fal" _
, _
"se;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy
Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;" _
, "Jet OLEDB:SFP=False;Jet OLEDB:Support Complex
Data=False"), Destination:=Range("ExportTo")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("Export" & strOption)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top