Save File based on Cell Date value

melidrie

New Member
Joined
Dec 2, 2016
Messages
8
Hi All,

I am new to this forum and i need some help.

every week we get a xls file that we have to adjust and save as a csv.
In cell D3 we put in the date for example: "31/12/2015".

Based on the date we save the file in the correct folder for example:
the date is "31/12/2015" so i want the file be saved in Folder "2015" and in that folder a subfolder called 12.
The day we use as name. "DD31.csv"

Can someone help me how to write this code? :confused:

Thnx!!

Regards Meli
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

cpatel13

Board Regular
Joined
Jan 4, 2016
Messages
59
Hi All,

I am new to this forum and i need some help.

every week we get a xls file that we have to adjust and save as a csv.
In cell D3 we put in the date for example: "31/12/2015".

Based on the date we save the file in the correct folder for example:
the date is "31/12/2015" so i want the file be saved in Folder "2015" and in that folder a subfolder called 12.
The day we use as name. "DD31.csv"

Can someone help me how to write this code? :confused:

Thnx!!

Regards Meli

Hie Meli,

In DD31.csv is the 31 the date which is in d3 31/12/2015?
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
Hello and welcome.

This code should achieve what you want. Just paste it in the code module for the sheet with the date:

Just ensure you insert the path of your folders where needed

Code:
Public Sub SaveDoc()

Dim sPath As String 'Path of where to save file


'Variables to hold the day month & year of the date in D3
Dim sYear As String
Dim sMonth As String
Dim sDay As String


Dim sFileName As String
Dim d As Date


    'is it a valid date in cell D3?
    If IsDate(Range("D3").Value) Then
        'store the date in a variable for easy access
        d = Sheet2.Range("_WC").Value
    Else
        MsgBox "Can not save. Please enter a valid date", vbCritical, "Save Error"
        Range("D3").Select
        Exit Sub
    End If


    'Get the year, month and day in the correct format from the date in cell D3:
    sYear = Format(d, "yyyy")
    sMonth = Format(d, "mm")
    sDay = Format(d, "dd")
    
    'insert path of where your yearly folders are eg "C:\ExcelFiles\" and then append the 2 folders for the date
    sPath = "***INSERT YOUR PATH***" & sYear & Application.PathSeparator & sMonth & Application.PathSeparator
    
    sFileName = "DD" & sDay
    
    'save the file
    ThisWorkbook.SaveAs FikleName:=sPath & sFileName, FileFormat:=xlCSV


End Sub
 

melidrie

New Member
Joined
Dec 2, 2016
Messages
8
Correct.

the value in cell D3 is 31/12/2015.
so what i want is that the file will be saved as DD31.csv in folder 2015/12/
 

melidrie

New Member
Joined
Dec 2, 2016
Messages
8

ADVERTISEMENT

thnx Galles but is gives me a error.
"d = Sheet2.Range("_WC").Value"
 

melidrie

New Member
Joined
Dec 2, 2016
Messages
8
Hello and welcome.

This code should achieve what you want. Just paste it in the code module for the sheet with the date:

Just ensure you insert the path of your folders where needed

Code:
Public Sub SaveDoc()

Dim sPath As String 'Path of where to save file


'Variables to hold the day month & year of the date in D3
Dim sYear As String
Dim sMonth As String
Dim sDay As String


Dim sFileName As String
Dim d As Date


    'is it a valid date in cell D3?
    If IsDate(Range("D3").Value) Then
        'store the date in a variable for easy access
        d = Sheet2.Range("_WC").Value
    Else
        MsgBox "Can not save. Please enter a valid date", vbCritical, "Save Error"
        Range("D3").Select
        Exit Sub
    End If


    'Get the year, month and day in the correct format from the date in cell D3:
    sYear = Format(d, "yyyy")
    sMonth = Format(d, "mm")
    sDay = Format(d, "dd")
    
    'insert path of where your yearly folders are eg "C:\ExcelFiles\" and then append the 2 folders for the date
    sPath = "***INSERT YOUR PATH***" & sYear & Application.PathSeparator & sMonth & Application.PathSeparator
    
    sFileName = "DD" & sDay
    
    'save the file
    ThisWorkbook.SaveAs FikleName:=sPath & sFileName, FileFormat:=xlCSV


End Sub

Almost got it working. :)
Except.. when it wants to save it says "Cannot be accessed. The file may be corrupted, Location on a server that is not responding, or read only"

save location is C:\New folder\
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006

ADVERTISEMENT

thnx Galles but is gives me a error.
"d = Sheet2.Range("_WC").Value"

My bad, it was code I amended from one of my sheets. Change it to:

Code:
d = Range("D3").Value
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
Almost got it working. :)
Except.. when it wants to save it says "Cannot be accessed. The file may be corrupted, Location on a server that is not responding, or read only"

save location is C:\New folder\

Make sure the folder with the year and the month exists. I've tested on my machine in to the path : C:\NewFolder\2016\12\DD02.csv and it worked fine.
 

melidrie

New Member
Joined
Dec 2, 2016
Messages
8
Make sure the folder with the year and the month exists. I've tested on my machine in to the path : C:\NewFolder\2016\12\DD02.csv and it worked fine.

it works like a charm! Thank u very much
Only there is one issue..
sometimes it saves the date wrong. 31/12/1899...


i put a ' in front of the date but after a few times the date just changes back to 1899
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
it works like a charm! Thank u very much
Only there is one issue..
sometimes it saves the date wrong. 31/12/1899...


i put a ' in front of the date but after a few times the date just changes back to 1899

Sorry for late reply.

That's strange if it is 'sometimes' unless the date entered to the cell has been entered incorrectly.

When it happens, step through each line of code and see what is being put in to each variable on these lines:

Code:
[COLOR=#574123][I]    'Get the year, month and day in the correct format from the date in cell D3:
[/I][/COLOR]    sYear = Format(d, "yyyy")
    sMonth = Format(d, "mm")
 [COLOR=#574123][I]   sDay = Format(d, "dd")[/I][/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,371
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top