Hello,
I want to import a variable .xls file with a macro into another .xls file.
i record this macro manually:
Sub Import_daily()
'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=F:\Daily history\2011\11. November\01.11.11.xls;Mode=Shar" _
, _
"e 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:Global Bulk Transact" _
, _
"ions=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("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Daily$")
.Name = "01.11.11"
.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 = "F:\Daily history\2011\11. November\01.11.11.xls"
.Refresh BackgroundQuery:=False
End With
Sheets("Data").Select
End Sub
the folders 2011, 11.november and 01.11.11 are variable files.
i already got those names in a seperate cell. that automatically changes.
Day (dd) Month (mm) Year (jj) Date Month with number
22 1 2011 22.01.11 01. Januari
can somebody tell me how to put those into a macro to import automatically the .xls file? and what is wrong with the macro.
I have also made this:
im MyYear As String
Dim MyMonth As String
Dim MyDay As String
Dim MyFile As String
Dim MyFileLocation As String
MyYear = Data!A2
MyMonth = Data!B2
MyDay = Data!C2
MyFile = Data!D2
MyFileLocation = "H:\Daily History\&MyYear&\&MyMonth&\&MyFile&.xls"
but when i put this into the macro i get error 400.
greetings Robert
I want to import a variable .xls file with a macro into another .xls file.
i record this macro manually:
Sub Import_daily()
'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=F:\Daily history\2011\11. November\01.11.11.xls;Mode=Shar" _
, _
"e 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:Global Bulk Transact" _
, _
"ions=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("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Daily$")
.Name = "01.11.11"
.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 = "F:\Daily history\2011\11. November\01.11.11.xls"
.Refresh BackgroundQuery:=False
End With
Sheets("Data").Select
End Sub
the folders 2011, 11.november and 01.11.11 are variable files.
i already got those names in a seperate cell. that automatically changes.
Day (dd) Month (mm) Year (jj) Date Month with number
22 1 2011 22.01.11 01. Januari
can somebody tell me how to put those into a macro to import automatically the .xls file? and what is wrong with the macro.
I have also made this:
im MyYear As String
Dim MyMonth As String
Dim MyDay As String
Dim MyFile As String
Dim MyFileLocation As String
MyYear = Data!A2
MyMonth = Data!B2
MyDay = Data!C2
MyFile = Data!D2
MyFileLocation = "H:\Daily History\&MyYear&\&MyMonth&\&MyFile&.xls"
but when i put this into the macro i get error 400.
greetings Robert