asked on

Convert a date to remove slashes

Hi All,

I want to save an excel file including the date range selected by two combo boxes on a form. The code looks like this:

xls.SaveAs "j:\tracking programs\excel_data\lineobservation " & cbofrom.value & "to " & cboto.value & ".xls"

Problem is, the dates stored in the drop downs include slashes, and those slashes cannot be in the name of the file. How can I convert the dates to the mm-dd-yyyy format? I was thinking something like...

dim datefrom as string
dim dateto as string

datefrom = some-function-that-I-dont-know(cbofrom, "dd-mm-yyyy")
dateto = some-function-that-I-dont-know(cboto, "dd-mm-yyyy")

xls.SaveAs "j:\tracking programs\excel_data\lineobservation " & datefrom & "to " & dateto & ".xls"

Thanks for the assistance!

G

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

graysoc

ASKER

Tried that...no work....

G

graysoc

ASKER

I had format in mind when I wrote that...but format doesn't do what I want.

G

SidFishes

you can also use replace

datefrom = replace(cbofrom, "/", "-")

peter57r

Format is the function you want.

In what sense does it 'no work'.

Are the dates in your combos true dates or are they text fields?

Pete

graysoc

ASKER

Thanks, with a little tweaking, it works now. I'm not sure why format didn't work earlier, but it does now!

G

GRayL

If you use the format function be sure to use the yyyymmdd format to ensure your files will be sorted by Windows correctly. Assuming your example is correct in terms of context try:

xls.SaveAs "j:\tracking programs\excel_data\lineobservation" & Format( cbofrom.value, "yyyymmdd") & "to" & Format(cboto.value, "yyyymmdd") & ".xls"

Notice I removed the spaces and the "-" as they would then make it necessary to surround the file name with square brackets.

graysoc

ASKER

It seems to work with the spaces and the dashes intact...

Thanks though. :)

G