The following code I recorded to import data from a closed workbook
("lala.xls") into the active sheet.

As I'm trying to import data from multiple closed workbooks, I need to
make the filename "lala.xls" and its folder path, variables.

But try as I might, I cannot make it work - it keeps complaining "Type
mismatch" or the second last line ( .Refresh BackgroundQuery:=False).

Appreciate your help. Thanks.

AP

----------------

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=P:\BUSINESS SYSTEMS\RECORDS\FIELD REPORTS
\lala.xls;Mode=S" _
, _
"hare Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database Passwo" _
, _
"rd="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Trans" _
, _
"actions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:D" _
, _
"on't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Summary$")
.Name = "lala"
.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 = "P:\BUSINESS SYSTEMS\RECORDS\FIELD REPORTS
\lala.xls"
.Refresh BackgroundQuery:=False
End With

This should get you started

sPath = "P:\BUSINESS SYSTEMS\RECORDS\FIELD REPORTS\"
sFilename = "lala.xls"

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=" & spath & sFilename & ";Mode=S" _


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ups.com...
The following code I recorded to import data from a closed workbook
("lala.xls") into the active sheet.

As I'm trying to import data from multiple closed workbooks, I need to
make the filename "lala.xls" and its folder path, variables.

But try as I might, I cannot make it work - it keeps complaining "Type
mismatch" or the second last line ( .Refresh BackgroundQuery:=False).

Appreciate your help. Thanks.

AP

----------------

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=P:\BUSINESS SYSTEMS\RECORDS\FIELD REPORTS
\lala.xls;Mode=S" _
, _
"hare Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database Passwo" _
, _
"rd="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Trans" _
, _
"actions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDB:D" _
, _
"on't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Summary$")
.Name = "lala"
.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 = "P:\BUSINESS SYSTEMS\RECORDS\FIELD REPORTS
\lala.xls"
.Refresh BackgroundQuery:=False
End With