How to create a second folder with todays date and saving a file using a cell value.

[ma1sk1y]

Hi, Im trying to create a directory and then export a pdf into this directory. What I have currently works but I would like to avoid overwriting older files that may have same filename. I would like to add another folder with todays date.

Everything I tried gives me errors.
Thanks in advance!
Small portion of code

I want to end up having C:\LabReports\12Sep2013 "filename from cell c8 in current sheet"

On Error Resume Next
MkDir "C:\LabReports\"
On Error GoTo 0
Sheets("Single Well Data Input").Select
If ActiveSheet.Range("B8").Value = "" Then GoTo error
    Range("B8:Z8").Select
    Selection.Copy
    Range("Table14[Well Name]").Select
    ActiveSheet.Paste
    Sheets("Water Analysis").Select
    Application.CutCopyMode = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\LabReports\" & Range("c8").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False

 

[RickXL]

I have not replicated your code but I have included the folder processing parts.

Basically, you need three things: Part 1 on the path, part 2 of the path and the file name.
I made Part 1 = "C:\LabReports"; Part 2 = Today's Date (automatic); Part 3 = "Sample.pdf".
I then joined parts 1 and 2 together to make the file path.

All three need to be joined together for the line:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myPath & "/" & myFile ...

You can make any or all cell contents or hard-coded or whatever.

First the macro checks the base folder and creates it if it does not exist. Then it checks the second level folder and creates it if it does not exist. (I swapped the date format round because this will come out in date order when sorted - personnel preference YMMV.)

Sub myTest()
    Dim myFolder As String
    Dim myPath As String
    Dim myFile As String
    myFolder = "C:\LabReports"
        If Dir(myFolder, vbDirectory) = vbNullString Then
        MkDir myFolder
    End If
    myPath = myFolder & "/" & Format(Date, "yyyy-mm-dd")
    If Dir(myPath, vbDirectory) = vbNullString Then
        MkDir myPath
    End If
    myFile = "Sample.pdf"
    
    
    ' Use ...    Filename:= myPath & "/" & myFile
    
    
End Sub