Excel Macro

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

Open in new window


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
LVL 1
Nicky RavenTechnical Services AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,
pls try
    ' Cheak if File exists
     If Len(Dir("ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date) & "\" & Format(Now(), "dd-mm-yyyy").pdf)) > 0  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

Open in new window

Regards
Nicky RavenTechnical Services AnalystAuthor Commented:
Hello Rgonzo1971

thank you for your reply.

I have tried what you have stated and I get a Error "Compile error: Syntax Error" and this line is then highlighted in the visual basic editor

     
If Len(Dir(FileFolderExists("ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date) & "\" & Format(Now(), "dd-mm-yyyy").pdf)) > 0  Then

Open in new window


Sorry I should have mentions I am using Microsoft office 2010 for reference... ( I will give myself a slap across the back of the head for that oversight )
Rgonzo1971Commented:
see my correct code
    ' Cheak if File exists
     If Len(Dir("ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date) & "\" & Format(Now(), "dd-mm-yyyy").pdf)) > 0  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

Open in new window

Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
Nicky RavenTechnical Services AnalystAuthor Commented:
Hello Rgonzo1971

The same error message pops up and its highlights the same line..

Thank you for your continued help on this.

Regards
Nicky Raven
Rgonzo1971Commented:
then try
    ' Cheak if File exists
     If Len(Dir("ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date) & "\" & Format(Now(), "dd-mm-yyyy") & ".pdf")) > 0  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

Open in new window

Rgonzo1971Commented:
or
     
If FileFolderExists("ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date) & "\" & Format(Now(), "dd-mm-yyyy") & ".pdf")  Then
Nicky RavenTechnical Services AnalystAuthor Commented:
Hello Rgonzo1971

Now I get the Error attached and the highlighted area is Time in the "\Daily Time Sheet\"

Error message
Thank you for your continued efforts

Regards
Nicky Raven
Rgonzo1971Commented:
then try
If FileFolderExists(ThisWorkbook.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date) & "\" & Format(Now(), "dd-mm-yyyy") & ".pdf")  Then

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nicky RavenTechnical Services AnalystAuthor Commented:
Hello Rgonzo1971

thanks for your help on this the line is now working fine as this is no longer brining up an error.

so that line is fine yet there is more for me to do as the message box is now coming up with an error so I will look into this before I bring up another question on these fine site.

you have been very helpfull

Regards
Nicky Raven
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.