Error msg on ODBC import

[Trudye]

Hi Guys, I have asked this question in another forum but did not receive and answer. I also ck'd FAQ, found the question from 3 differnt subscribers but not answers. And since I always get GREAT answers here I thought what the heck it can't hurt to ask.

I am receiving 2 error msgs:
[blue] Data file 'X:\Pre-Sale Inspections\outgoing\AT_20060320_042005.XML' not found would you like to connect to 'X:\Pre-Sale Inspections\outgoing\AT_20060320_042005.XML' instead?[/blue]

Weather I click yes/no a second msg appears. Dialog box [blue]'Pls Enter MS JET OLE DB Initialization Information'[/blue]

The msg is occruing on the last line of code (.Refresh BackgroundQuery:=False). The msg just started appearing when I changed the file path. I moved the file from drive 'M' to drive 'X'

Here is the code:
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=X:\Pre-Sale Inspections\outgoing\" & TodaysFile _
, _
";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bul" _
, _
"k Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet " _
, _
"OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Sheet1$")
.Name = TodaysFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"X:\Pre-Sale Inspections\outgoing\" & TodaysFile
.Refresh BackgroundQuery:=False
End With

OBTW: TodaysFile is defined globally.

Does anyone have any idea why I am getting this msg.

Thanx
Trudye

 

[Trudye]

I finally figured it out. THis is one way to pull an XML file into Excel you must be running MS Office 2003. I am sure there are better ways but here is what I did.


ActiveWorkbook.XmlImport URL:="X:\Pre-Sale Inspections\outgoing\" & TodaysFile, ImportMap _
:=Nothing, Overwrite:=True, Destination:=Range("$A$1")

Voila! It was that easy and Dum-Dum me spent 2 days trying to figure it out.