• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to code current date in file path

Kristina

New Member
Hello,

I'm using an xls merger code but the path where the files are being taken from ends with \yyyymmdd

Is there a code that will pull current date in that format always?

ex: G:\Files\20150615

Thanks so much.
 
Hi Kristina ,

In VBA , the Date function returns the system date ; the Format function can be used to format any numeric value , which a date is , the way you want ; thus , a construct such as :

Format(Date , "yyyymmdd")

will return the text string 20150615 , which can then be appended to a path to form what ever is your requirement.

Narayan
 
Thank you Narayan,

Do I need to have seperators? I'm getting an error that says Expected: list separator or )

This is the code I'm usingl; I would need "yyyymmdd" where here is currently.

Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here")

Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)


Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate


Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
 
I assume that this bit will need to be edited to include "\"

Code:
Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here")

So code will be something like

Code:
Set dirObj = mergeObj.Getfolder("D:\change\to\excel\files\path\here" & "\" & Format(Date , "yyyymmdd"))
 
Back
Top