Yeah, I've figured it out just seconds ago. But I still don't know how to get the year from the file name:
See, my Excel file is a table, which is created once in a month, and then has to be imported to Access. It's full name is
IT_IS Verrechnung Oktober 2008.xls or
IT_IS Verrechnung November 2008.xls or
IT_IS Verrechnung Mai 2008.xls
-- you get the picture.
At the very beginning of the code I'm opening the File Dialog window and choose the table that has to be imported, and set the value of sJahr:
.AllowMultiSelect = True
.Filters.Add "Excel", "*.xls"
If .Show = -1 Then
'Durchläuft die ausgewählten Dateien
For Each vrtSelectedItem In .SelectedItems
sJahr = Right(Dir(vrtSelectedItem), 4)
'outputs .xls instead of needed 2008
Maybe, there's a way to cut off the .xls from the String, or somehow yield the needed 2008?
I get the following output to the Immediate Window:
UPDATE [IS] SET [Monat]= #11/30/.xls#;
Doesn't ring a bell for me.
Just so the lesson is not lost in the detail - your first port of call whenever you get any problems like this (where a dynamically created bit of code does not execute as expected) should be to see what is actually being created & executed. Your next step should be to read up on more advanced debugging techniques (breakpoints, stepping through code, watches etc)
Or just sJahr = Left(Right(Dir(vrtSelectedItem), 8),4)
Indeed you can; I just wanted to split it up to show the steps needed to do this. I had considered using the right, 8; but chose not to incase we were dealing with files with more than 3 character extensions (in which cans the len() and instr() functions come in to play).
for example, I opened a file yesterday with the extension .ssmssln