Problem with filename =Heute()

[Jamen Lone]

Hi,
I've got a problem by define a filename like this ...

code...

Sub myImport()
Dim myDat As String
Dim dieseDatei As String
Dim neueTabelle As String
Dim AktDatum As String

'Namen festlegen
dieseDatei = ActiveWorkbook.Name
myDat = Range("A1")
AktDatum = "DATEN_" & Range("A2") & ".xls" 'A2 is =Heute() in german
excel

.... and so on

The result is that "AktDatum" give me the following value ...
DATEN_Heute().xls and I want to have
DATEN_14.07.2009.xls

Can someone help me?

 

[Stefi]

Are you sure that A2 contains the equal sign preceding HEUTE()?
Or you can use this equivalent:
AktDatum = "DATEN_" & Date & ".xls"

Regards,
Stefi


„Jamen Lone†ezt írta:

 

[Dave Peterson]

Make sure you format that value (either the formula or Stefi's suggestion of
VBA's built in Date) the way you need:

AktDatum = "DATEN_" & format(Range("A2").value, "dd.mm.yyyy") & ".xls"
or
AktDatum = "DATEN_" & format(date, "dd.mm.yyyy") & ".xls"

 

[Dave Peterson]

ps. If the value in the cell were formatted the way you like, you could use:

AktDatum = "DATEN_" & format(Range("A2").Text, "dd.mm.yyyy") & ".xls"

 

[Stefi]

Hi Dave,

I tried the original
AktDatum = "DATEN_" & Range("A2") & ".xls"
with =TODAY() in A2 without any own formatting and it gave the desired
result, that's why I guessed that the equal sign is missing from A2. Another
possibility if A2 is formatted like text!

Stefi



„Dave Peterson†ezt írta:

ps. If the value in the cell were formatted the way you like, you could use:

AktDatum = "DATEN_" & format(Range("A2").Text, "dd.mm.yyyy") & ".xls"

 

[Dave Peterson]

With my USA settings, if I have my normal default date format (taken from the
windows regional settings) of mm/dd/yyyy, this filename will look like it's a
deeper path.

daten_07/14/2009.xls

I know that some use mm-dd-yyyy as their default date format and then this kind
of thing would work (if it matched that filename, of cource).

Hi Dave,

I tried the original
AktDatum = "DATEN_" & Range("A2") & ".xls"
with =TODAY() in A2 without any own formatting and it gave the desired
result, that's why I guessed that the equal sign is missing from A2. Another
possibility if A2 is formatted like text!

Stefi

„Dave Peterson†ezt írta:

 

[Dave Peterson]

ps. Are you in Europe? And is your short date windows format set to dd.mm.yyyy
(or a legal filename)?

Hi Dave,

I tried the original
AktDatum = "DATEN_" & Range("A2") & ".xls"
with =TODAY() in A2 without any own formatting and it gave the desired
result, that's why I guessed that the equal sign is missing from A2. Another
possibility if A2 is formatted like text!

Stefi

„Dave Peterson†ezt írta:

 

[Jamen Lone]

Thanks a lot for your help. Now it works fine for me.

 

[Stefi]

Yes, I'm in Europe, I use a Hungarian language version of Excel2003, my short
date windows format set to éééé.HH.nn. (that is yyyy.MM.dd.) and
AktDatum = "DATEN_" & Range("A2") & ".xls"
gave the desired result without any additional formatting or converting.

Unfortunately Jamen posted only that his problem was solved, he didn't write
what was the cause.

Regards,
Stefi

„Dave Peterson†ezt írta:

ps. Are you in Europe? And is your short date windows format set to dd.mm.yyyy
(or a legal filename)?

 

[Dave Peterson]

Those slashes commonly used in USA short date settings will cause lots of
problems in filenames.

Yes, I'm in Europe, I use a Hungarian language version of Excel2003, my short
date windows format set to éééé.HH.nn. (that is yyyy.MM.dd.) and
AktDatum = "DATEN_" & Range("A2") & ".xls"
gave the desired result without any additional formatting or converting.

Unfortunately Jamen posted only that his problem was solved, he didn't write
what was the cause.

Regards,
Stefi

„Dave Peterson†ezt írta:

 

[Dave Peterson]

Ps. My windows short date is mm/dd/yyyy (4 digit year). Lots of people (most?)
use mm/dd/yy (or another format).

I wouldn't trust my code to assume that the user's short date format matched the
name of the file even if the format always contained valid characters.

 

[Stefi]

Fullstops (.) used with us also causes problems when used in file names
(Windows considers them as extension separator).


„Dave Peterson†ezt írta:

 

[Dave Peterson]

Personally, I don't like filenames with spaces or more than one dot. But
Windows doesn't care.

In fact, I've given up my fight and embraced the dark side. I now use multiple
dots and spaces.

Fullstops (.) used with us also causes problems when used in file names
(Windows considers them as extension separator).

„Dave Peterson†ezt írta:

 

[Stefi]

Partly agreed! I don't use spaces and multiple dots but I'am receiving files
with such names.


„Dave Peterson†ezt írta: