Hi All,

I'm trying to import data from a closed excel wkbk to an open excel wkbk. I
recorded the macro, made some adjustments, and sometimes it works, but most
the time it does not. Not sure where my problem(s) are, and I'm hoping that
you can help me. This is what I wrote/recorded:

   '<<< ipath is the entire file path to the exported IMARC file
   Dim ipath As String
   ipath = Worksheets("mp").Range("i2")
   '<<< iname is the name of the file
   Dim iname As String
   iname = Worksheets("mp").Range("j2")
   '<<< noxls is the name of the file without the file extenstion
   Dim noxls As String
   noxls = Worksheets("mp").Range("j4")
   '<<< fpath is the folder path to the IMARC folder
   Dim fpath As String
   fpath = Worksheets("mp").Range("k2")
   
   Sheets("DI_Temp").Select
       With ActiveSheet.QueryTables.Add(Connection:=Array( _
       "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=" _
       & iname & ";Mode=Share Deny Write;Extended
Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDB", _
       ":Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database
Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:G" _
       , _
       "lobal Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=F" _
       , _
       "alse;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Repair=False;Jet OLEDB:SFP=False" _
       ), Destination:=Range("A1"))
       .CommandType = xlCmdTable
       .CommandText = Array(noxls & "$")
       .Name = noxls
       .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 = ipath
       .Refresh BackgroundQuery:=False
   End With

The debug error always goes to ".Refresh BackgroundQuery:=False"
I've also experimented with switching to True, but get the same error.

Other details. In my network, each of us have a username with a period that
separates the first and last name. So a file/folder path would be something
like C:\Documents and Settings\john.doe\My Documents. I've read that the
period might be the issue, but it doesn't explain why it works sometimes.

Thank you.