Using MyValue in SaveAs Fname

[Guest]

I have the following:
-----------
' Input the Mont End date in Column E

Range("E1").Select

Dim Message, Title, Default, MyValue

Message = "Enter MonthEnd date i.e. 31/01/2006 or 28/02/2006" ' Set
prompt.
Title = "Input Box" ' Set title.
Default = "31/01/2007" ' Set default.

' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

ActiveCell.FormulaR1C1 = MyValue

Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("A1").Select
--------------------------------
I would like to now save the worksheet as CSV using the above MyValue date
as part of the fixed filename such as:
if MyValue is 30/06/2007 the filename should be "diur0607.csv"
and the file should be saved in this path: //cav-new/files
========
thanks

 

[Bernie Deitrick]

Dim myFilename As String
myFilename = "diur" & Format(DateValue(myValue), "mmyy") & ".csv"
ActiveWorkbook.SaveAs Filename:= _
\\cav-new\files\ & myFilename, _
FileFormat:=xlCSV

HTH,
Bernie
MS Excel MVP

 

[david.ritchie.au]

Hi Helmut,

Perhaps this would work:

ActiveWorkbook.SaveAs filename:="\\cav-new\files\diur" & mid(myvalue,
4,2)& right(myvalue,2) & ".csv",FileFormat:=xlCSV

assuming cav-new is a network share and the date is entered as dd/mm/
yyyy

Cheers
David

I have the following:
-----------
' Input the Mont End date in Column E

Range("E1").Select

Dim Message, Title, Default, MyValue

Message = "Enter MonthEnd date i.e. 31/01/2006 or 28/02/2006" ' Set
prompt.
Title = "Input Box" ' Set title.
Default = "31/01/2007" ' Set default.

' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

ActiveCell.FormulaR1C1 = MyValue

Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("A1").Select
--------------------------------
I would like to now save the worksheet as CSV using the above MyValue date
as part of the fixed filename such as:
if MyValue is 30/06/2007 the filename should be "diur0607.csv"
and the file should be saved in this path: //cav-new/files
========
thanks

activesheet.saveas name:="//cav-new/files/diur" & month(myValue) &
year(myValue) & ".csv"

 

[Bernie Deitrick]

Sorry,

The path should be in double quotes - "\ \ cav-new \ files \ " (Without the spaces, used to keep
the autoconversion in check....)

HTH,
Bernie
MS Excel MVP