• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2942
  • Last Modified:

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
0
graysoc
Asked:
graysoc
1 Solution
 
peter57rCommented:
Hi graysoc,
> some-function-that-I-dont-know
 is Format()

Pete
0
 
graysocAuthor Commented:
Tried that...no work....

G
0
 
graysocAuthor Commented:
I had format in mind when I wrote that...but format doesn't do what I want.

G
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
SidFishesCommented:
you can also use replace

datefrom = replace(cbofrom, "/", "-")
0
 
peter57rCommented:
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
0
 
graysocAuthor Commented:
Thanks, with a little tweaking, it works now.  I'm not sure why format didn't work earlier, but it does now!

G
0
 
GRayLCommented:
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.
0
 
graysocAuthor Commented:
It seems to work with the spaces and the dashes intact...  

Thanks though.  :)

G
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now