Save File based on Cell Date value

[melidrie]

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?

Thnx!!

Regards Meli

 

[cpatel13]

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?

Thnx!!

Regards Meli

Hie Meli,

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

 

[gallen]

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

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]

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]

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

 

[melidrie]

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

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]

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:

d = Range("D3").Value

 

[gallen]

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]

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]

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:

[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]