B
BigPig
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", _
"atabase Password="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase
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 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(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.
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", _
"atabase Password="""";Jet OLEDB:Engine Type=35;Jet OLEDBatabase
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 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(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.