troubleshooting Question

Excel Macro

Avatar of Nicky Raven
Nicky RavenFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft ExcelMicrosoft Office
9 Comments1 Solution121 ViewsLast Modified:
Hello Everyone

I have a bit of a quandary as I like to keep my time sheets of time I have assigned to calls and I cannot get one particular part of the macro working ... I'm probably doing wrong or implementing the wrong code so I kindly ask you to have a look and let me leach a bit of knowledge from your grey matter.

the code in question is to cheek if the file exists and give me a message as this is ran in a sequence and this is the first macro of 4 macros.

as you can see I have it cheeking for year folder then month folder and creating if necessary that's working fine and the saving to .pdf is also working fine for me as well the only part is as explained above the cheeking if a the exists.

Sub Save()
'
' Save Macro
' Save Data Range B2 to D28
'

    With ActiveSheet.PageSetup
        .CenterHorizontally = True
        .CenterVertically = True
    End With

     ' Check for year folder and create if needed
    If Len(Dir(ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date), vbDirectory)) = 0 Then
        MkDir CurDir & "\Daily Time Sheet\" & Year(Date)
    End If

     ' Check for Month folder and create if needed
    If Len(Dir(ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date), vbDirectory)) = 0 Then
        MkDir CurDir & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date)
    End If

    ' Cheak if File exists
    ' If FileFolderExists("ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date) & "\" & Format(Now(), "dd-mm-yyyy").pdf Then
    ' MsgBox ("File exists do you want to overwrite!", vbYesNo + vbCritical) = vbNo Then End
    '  Else
    ' MsgBox "File does not exist! Creating new file for date "
    ' End If
    'End Sub

    ' Save File
    ConflictResolution = xlUserResolution
    Sheets("Time").Range("B2:D28").ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date) & "\" & Format(Now(), "dd-mm-yyyy"), _
    Quality:=xlQualityMinimum, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

End Sub

Thank all in advance
Nicky Raven

addendum
if they are any queries please let me know and I will respond as soon as I am able
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros