What I'm trying to do is creat a macro that will acess the information
form another excel file but i would like it to axcess a file of my
choesing not a designated one because the file will change locations
and names on different computers
i used the record a macro to start this one but now i'm stuck

I apperciate the help

Greg


Sub Import_List()
'
'to import the active crew list from another excel file
'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and Settings\Greg\Desktop\ILO
List.xls;Mode=" _
, _
"Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database Passw" _
, _
"ord="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Tran"
_
, _
"sactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDB:" _
, _
"Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("AE6"))
.CommandType = xlCmdTable
.CommandText = Array("'ILO List$'")
.Name = "ILO List 'ILO List$'"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Documents and Settings\Greg\My Documents\My Data
Sources\ILO List 'ILO List$'.odc"
.SourceDataFile = "C:\Documents and Settings\Greg\Desktop\ILO
List.xls"
.Refresh BackgroundQuery:=False
Range("B6").Select
End With

Hi Gregers

Try adding the below to the start of you code

Dim fileToOpen As Variant
Do While fileToOpen = ""
fileToOpen = Application.GetOpenFilename("Excel Workbooks (*.xls), *.xls")
Loop

this will let you select a filename and path

the change your code above to use the filename e.g.

"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User ID=Admin;Data
Source=" & fileToOpen & ";Mode=" _

--
Tony Green


" wrote:

What I'm trying to do is creat a macro that will acess the information
form another excel file but i would like it to axcess a file of my
choesing not a designated one because the file will change locations
and names on different computers
i used the record a macro to start this one but now i'm stuck

I apperciate the help

Greg


Sub Import_List()
'
'to import the active crew list from another excel file
'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and Settings\Greg\Desktop\ILO
List.xls;Mode=" _
, _
"Share Deny Write;Extended Properties=""HDR=YES;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Database Passw" _
, _
"ord="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Tran"
_
, _
"sactions=1;Jet OLEDB:New Database Password="""";Jet
OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet
OLEDB:" _
, _
"Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("AE6"))
.CommandType = xlCmdTable
.CommandText = Array("'ILO List$'")
.Name = "ILO List 'ILO List$'"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Documents and Settings\Greg\My Documents\My Data
Sources\ILO List 'ILO List$'.odc"
.SourceDataFile = "C:\Documents and Settings\Greg\Desktop\ILO
List.xls"
.Refresh BackgroundQuery:=False
Range("B6").Select
End With