Change the Date Format VBA

[JoeSalmi]

In addition to this code which generates the save as name like I wanted it to I can not save the spreadsheet with the format of a date in the name.

[COLOR="Blue"]Sub[/COLOR] Saveme()
[COLOR="blue"]Dim[/COLOR] fName [COLOR="blue"]As String[/COLOR]
[COLOR="Blue"]Dim [/COLOR]weDate [COLOR="blue"]As String[/COLOR]

weDate = Sheet1.Range("E4")
 
 fName = Sheet1.Range("I7") & " - " & Sheet1.Range("C4") & " " & weDate
 Application.Dialogs(xlDialogSaveAs).Show (fName)
    
[COLOR="blue"]End Sub[/COLOR]

Let me try and explain myself a little better. The code grabs the contents from 3 cells which produces a "suggested" save as name like [DOCUMENT NAME - WEEK ENDING 01/01/01]

DOCUMENT NAME = Cell I7
WEEK ENDING = Cell C4
01/01/01 = Cell E4 or weDate

Or better yet I cannot save the file with a "/" in the name. So I need to change the "/" to a "-" before it suggests the name.

I tried changing the format of the date to show as mm-dd-yyyy, and it does show it as that but if you activate the cell that it's in (E4) then in the formula bar it shows mm/dd/yyyy.

Is there a way I can change the slash to a dash or try and grab the date as a text instead of the date???

 

[VoG]

Try

weDate = Sheet1.Range("E4").Text

 

[MrKowz]

Is the date in E4 stored as a date or a string?

if it is stored as a date, try:

[COLOR=#0000ff]Sub[/COLOR] Saveme()
[COLOR=blue]Dim[/COLOR] fName [COLOR=blue]As String[/COLOR]
[COLOR=blue]Dim [/COLOR]weDate [COLOR=blue]As Date[/COLOR]
[COLOR=#0000ff][/COLOR]
weDate = Sheet1.Range("E4")
 
 fName = Sheet1.Range("I7") & " - " & Sheet1.Range("C4") & " " & Format(weDate,"mm-dd-yyyy")
 Application.Dialogs(xlDialogSaveAs).Show (fName)
    
[COLOR=blue]End Sub[/COLOR]

If it is stored as a string, try:

[COLOR=#0000ff]Sub[/COLOR] Saveme()
[COLOR=blue]Dim[/COLOR] fName [COLOR=blue]As String[/COLOR]
[COLOR=blue]Dim [/COLOR]weDate [COLOR=blue]As String[/COLOR]

weDate = Sheet1.Range("E4")
 
 fName = Sheet1.Range("I7") & " - " & Sheet1.Range("C4") & " " & Application.Substitute(weDate,"/","-")
 Application.Dialogs(xlDialogSaveAs).Show (fName)
    
[COLOR=blue]End Sub[/COLOR]

 

[JoeSalmi]

[SOLVED] Change the Date Format VBA

Is the date in E4 stored as a date or a string?

if it is stored as a date, try:

[COLOR=#0000ff]Sub[/COLOR] Saveme()
[COLOR=blue]Dim[/COLOR] fName [COLOR=blue]As String[/COLOR]
[COLOR=blue]Dim [/COLOR]weDate [COLOR=blue]As Date[/COLOR]
[COLOR=#0000ff][/COLOR]
weDate = Sheet1.Range("E4")
 
 fName = Sheet1.Range("I7") & " - " & Sheet1.Range("C4") & " " & Format(weDate,"mm-dd-yyyy")
 Application.Dialogs(xlDialogSaveAs).Show (fName)
    
[COLOR=blue]End Sub[/COLOR]

If it is stored as a string, try:

[COLOR=#0000ff]Sub[/COLOR] Saveme()
[COLOR=blue]Dim[/COLOR] fName [COLOR=blue]As String[/COLOR]
[COLOR=blue]Dim [/COLOR]weDate [COLOR=blue]As String[/COLOR]

weDate = Sheet1.Range("E4")
 
 fName = Sheet1.Range("I7") & " - " & Sheet1.Range("C4") & " " & Application.Substitute(weDate,"/","-")
 Application.Dialogs(xlDialogSaveAs).Show (fName)
    
[COLOR=blue]End Sub[/COLOR]

Awesome man thanks works like a charm