Save Location - Date Format
 

Hello all

This is how my button is programmed...

----------------------------------------------------------------------
------
----------------------------------------------------------------------
------
----------------------------


Private Sub UserForm_Initialize()
Me.TextBox1.Text = Date
End Sub

Private Sub CommandButton1_Click()
MyPath = "\\tprc1fanp001\shared\Enforcement, Enquiry &
Complaints\Manual
Payment History\" 'Set the Path
MyFileName = "Date" & Me.TextBox1.Text & ".xls" 'Create the
File Name
MySaveString = MyPath & MyFileName 'String the two together

ActiveWorkbook.SaveAs Filename:=MySaveString, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

----------------------------------------------------------------------
------
----------------------------------------------------------------------
------
----------------------------
This is my error message:



Run Time Error '1004':
Microsoft Excel cannot access the file
'\\tprc1fanp001\shared\Enforcement,
Enquiry & Complaints\Manual Payment History\Date06\02' There are
several
possible reasons:
-The file name or path name does not exist
-The file you're trying to open is being used by another program.

Close the document in the other program, and try again

End or Debug


I was advised:

Hyphens and underscores are valid. Underscores get hidden if hyperlinks
are involved. But would certainly suggest using four digit year and
placing year before month before day of month, so you can sort
on filename when looking at a directory. i.e.
format(now(),"yyyy-mm-dd") -or-
format(now(),"yyyy_mmdd")

But where do I put format(now(),"yyyy-mm-dd") ?

Thanks

Save Location - Date Format
 

Ravi


MyFileName = "Date" & Me.TextBox1.Text & ".xls" 'Create the
File Name

MyFileName = "Date" & Format(Me.TextBox.Text, "yyyy-mm-dd") & ".xls"

Please post to only one group.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Save Location - Date Format
 

[posted and mailed]

"Ravi Sandhu" wrote in news:dalWb.9486$q%6.2865826
@newsfep2-win.server.ntli.net:

Microsoft Excel cannot access the file
'\\tprc1fanp001\shared\Enforcement,
Enquiry & Complaints\Manual Payment History\Date06\02' There are


Your problem is that you are using the character "/" in the filename (it's
part of the date). This is an invalid character in a Windows filename.

Windows interprets the / as the end of the filename.

You have to replace the / with some other character (like -) in order for
it to work like you expect.


--
My email address has an extra @ (spell it out) and an extra invalid. Please
remove them if you are not a spammer or list broker and want to reply.