Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using MyValue in SaveAs Fname
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using MyValue in SaveAs Fname
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 "Helmut" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using MyValue in SaveAs Fname
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 On Jul 3, 10:10 pm, Helmut wrote: 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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using MyValue in SaveAs Fname
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 "Helmut" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create user id fm LName, FName | Excel Worksheet Functions | |||
change lname, fname to fname last name | Excel Discussion (Misc queries) | |||
how do i reference data in a file with the fname in another cell? | Excel Worksheet Functions | |||
MyValue | Excel Programming | |||
How to separate fname and fname in cell in excel | Excel Programming |