Importing data from excel to excel

[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.

 

[Jean-Yves]

Hi,

Maybe use ADO instead ?

Regards
JY

 

[Jean-Yves]

Hi,

Something like (make a ref to Microsft Activex Data Object library first)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Mydoc\STATIST\Current
Books\Statistics for the books.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With

rs.Open "Select [ColA name ], [ColB name] from [SheetName$] ORDER BY [ColA
name ];", cn, adOpenDynamic, adLockReadOnly
if rs.EOF <> true then
range("A1").CopyFromRecrodSet rs
rs.Close

Regards
JY

 

[BigPig]

Jean-Yves,

Merci beaucoup!

Thank you very much!

BigPig

Hi,

Something like (make a ref to Microsft Activex Data Object library first)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Mydoc\STATIST\Current
Books\Statistics for the books.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With

rs.Open "Select [ColA name ], [ColB name] from [SheetName$] ORDER BY [ColA
name ];", cn, adOpenDynamic, adLockReadOnly
if rs.EOF <> true then
range("A1").CopyFromRecrodSet rs
rs.Close

Regards
JY

Hi,

Maybe use ADO instead ?

Regards
JY