automatic Import a closed variable .xls file in a variable folder

[Robert72]

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

 

[rosenbe]

While I am not entiry sure what you are doing...

The line...

MyFileLocation = "H:\Daily History\&MyYear&\&MyMonth&\&MyFile&.xls"

Should read...

MyFileLocation = "H:\Daily History\" & MyYear & "\"  & MyMonth & "\" & MyFile & ".xls"

Also, your code suggests you have this on your F drive not your H. Not sure if you have gotten that far yet or not, but it will be a problem if you are looking in the wrong location for the file. If these are network drives, you might want to use UNC path names to eliminate mapping issues.

 

[Robert72]

Thanks for you reaction,

I've changed all your notes.
but it isn't working yet,

this is what i got so far:



Dim 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"


Sub importdaily()


'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=MyFileLocation;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 = "MyFileLocation"
.Refresh BackgroundQuery:=False
End With
Sheets("Data").Select
End Sub

now i get this error:
compile error invalid outside procedure

it marks the red letters.
MyYear = (Data!A2)
MyMonth = (Data!B2)
MyDay = (Data!C2)
MyFile = (Data!D2)
MyFileLocation = "H:\Daily History\" & MyYear & "\" & MyMonth & "\" & MyFile & ".xls"

Data is the name of the sheet. could somebody tell me what i do wrong?

Kind regards
Robert

 

[rosenbe]

Data! is not a proper VBA reference, it only work in a Cell. To reference the sheet directly you need to use the CodeName; that is, the (Name) field in the properties window of the sheet, the defaults are Sheet1, Sheet2, Sheet3, etc.

You can change the CodeName in Visual Basic Editor by using F4 (Properties Window) and selecting the sheet you wish to change, then in the (Name) field type the new CodeName.

If you name the sheet "Data" again, you need to use the period (.) as a separator to access methods and properties. To reference B2 you would use the Range property:

Data.Range("B2")

To get the B2's value, you would use the Range's Value property:

Data.Range("B2").Value

Like most things in Office, there are number of ways of doing things, another way would be to use the Sheets collection where you would use the Name of the sheet (the one you type in outside of the Visual Basic Editor) so you would be able to simply type

Sheets("Data").Range("B2").Value

without have to setup Data as the CodeName. Because I like to explicitly reference things, I would probably do it this way:

MyYear = ThisWorkbook.Sheets("Data").Range("A2").Value
MyMonth = ThisWorkbook.Sheets("Data").Range("B2").Value
MyDay = ThisWorkbook.Sheets("Data").Range("C2").Value
MyFile = ThisWorkbook.Sheets("Data").Range("D2").Value

 

[Robert72]

Hello

I still got the same error, (compile error: Invalid outdside procedure) and it marks the cell name C2

MyYear = ThisWorkbook.Sheets("Data").Range("C2").Value
MyMonth = ThisWorkbook.Sheets("Data").Range("B2").Data
MyDay = ThisWorkbook.Sheets("Data").Range("A2").Data
MyFile = ThisWorkbook.Sheets("Data").Range("D2").Data
MyFileLocation = "H:\Daily History\" & MyYear & "\" & MyMonth & "\" & MyFile & ".xls"

whats wrong?
im using excel 2003.

Kind regards

 

[rosenbe]

Move the 'Sub importdaily()' line before the "MyYear = ..." statement. Placing the assignment inside the Sub block will get it to compile.

Also, make sure you change Range("xx").Data statements to Range("xx").Value statements. (where xx is your cell reference)