Using MyValue in SaveAs Fname

H

Helmut

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
 
B

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
 
D

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"
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top