Nicky Raven
asked on
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.
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
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
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
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 )
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
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 )
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
ASKER
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
The same error message pops up and its highlights the same line..
Thank you for your continued help on this.
Regards
Nicky Raven
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
or
If FileFolderExists("ThisWork book.Path & "\Daily Time Sheet\" & Year(Date) & "\" & Month(Date) & "\" & Format(Now(), "dd-mm-yyyy") & ".pdf") Then
If FileFolderExists("ThisWork
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
pls try
Open in new window
Regards