I need to do a couple of things that seem to be easy, but I'm having a hard time figuring out how to implement.

I need to create a folder using today's date. That part's fine. Then I need to save a file using text and today's date (also fine).

The part that's got me is that I need to save the new file to the new folder. How can I have the file both save with today's date and save it to the new folder with today's date? (if the file path were constant, I'd be fine to do this, but when part of the path changes according to the date, I don't know how to do this)

This is what I tried, but it's not working. I'm getting the following error: 1004: Application-defined or object-defined error.

Sub filesave()

ActiveWorkbook.SaveAs ("S:\HR\TM\" & Format(Now(),"dd.mm.yyyy") & "\SOX recon " & Format(Now(), "dd.mm.yyyy") & ".xlsx")

End Sub

This is the code to create the folder (which works perfectly):

Sub FolderCreate()

MkDir "S:\HR\TM\" & Format(Now(), "dd.mm.yyyy")

End Sub

Please note that the file path/directory has been slightly modified/simplified for security.

  • Where do you create the folder? Have you tried to debug the code by displaying at least the string you generate? – harrymc Nov 28 '18 at 18:07
  • @harrymc - the folder is already created previously. That code works perfectly (it creates a new folder in the "TM" folder with the current date in that dd.mm.yyyy format). My issue is that each time this is run, the folder name will be different. It'll always be named after the current date, though. – Lenny Nov 28 '18 at 18:51
  • Different from what? Can you show the other code? Why do you need to recompute the name twice? – harrymc Nov 28 '18 at 19:32
  • @harrymc each week the folder will be created. The name will be different each time as it's a date folder (today it would be named 29.11.2018; next week it will be named 06.12.2018; and so one). As such, the folder in which the file will be saved will be different each week and not determined until the process it being completed. I'll add the folder creation code ... – Lenny Nov 29 '18 at 20:10

Just a guess:

fname = "S:\HR\TM\" & Format(Now(),"dd.mm.yyyy") & "\SOX recon " & Format(Now(), "dd.mm.yyyy") & ".xlsx"
MsgBox fname
ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlOpenXMLWorkbook

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.