Need to modify an existing code to STOP it from overwriting an existing file, if present
0
I need to modify this code to first search if a file exists, if so, do nothing but show a message, if not, the code below will automatically create the file. Thanks in advance.
Option Explicit
Public WithEvents MonitorApp As Application
Private Sub Workbook_Open()
Dim strGenericFilePath As String: strGenericFilePath = "\Server2016CommonRegister"
Dim strYear As String: strYear = Year(Date) & ""
Dim strMonth As String: strMonth = MonthName(Month(Date)) & ""
Dim strFileName As String: strFileName = "Register Sheet " & Format(Date, "mmm dd yyyy")
Application.DisplayAlerts = False
If Len(Dir(strGenericFilePath & strYear, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear
End If
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonth
End If
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonth
End If
ActiveWorkbook.SaveAs Filename:= strGenericFilePath & strYear & strMonth & strFileName, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
MsgBox "File Saved As: " & vbNewLine & strGenericFilePath & strYear & strMonth & strFileName
End Sub
excel vba
edited Nov 16 '18 at 1:28
urdearboy
8,0023731
asked Nov 15 '18 at 23:24
Henry NavarroHenry Navarro
34
|
show 2 more comments
0
I need to modify this code to first search if a file exists, if so, do nothing but show a message, if not, the code below will automatically create the file. Thanks in advance.
Option Explicit
Public WithEvents MonitorApp As Application
Private Sub Workbook_Open()
Dim strGenericFilePath As String: strGenericFilePath = "\Server2016CommonRegister"
Dim strYear As String: strYear = Year(Date) & ""
Dim strMonth As String: strMonth = MonthName(Month(Date)) & ""
Dim strFileName As String: strFileName = "Register Sheet " & Format(Date, "mmm dd yyyy")
Application.DisplayAlerts = False
If Len(Dir(strGenericFilePath & strYear, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear
End If
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonth
End If
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonth
End If
ActiveWorkbook.SaveAs Filename:= strGenericFilePath & strYear & strMonth & strFileName, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
MsgBox "File Saved As: " & vbNewLine & strGenericFilePath & strYear & strMonth & strFileName
End Sub
excel vba
edited Nov 16 '18 at 1:28
urdearboy
8,0023731
asked Nov 15 '18 at 23:24
Henry NavarroHenry Navarro
34
I don't see any "code below".
– user2864740
Nov 15 '18 at 23:25
1
This is almost impossible to read. Please edit your original post with the code and someone will format it for you. stackoverflow.com/posts/53329272/edit
– Rey Juna
Nov 15 '18 at 23:40
ok. I figured out how to post it correctly. Let me know if you can help me with the code.
– Henry Navarro
Nov 15 '18 at 23:50
This may help: stackoverflow.com/questions/34974950/… You could leave out the part of checking if the file is newer.
– Rey Juna
Nov 15 '18 at 23:59
I've edited the code just needs peer review. Please please please write code neater. I highly suggest reading: experts-exchange.com/articles/21759/…
– alowflyingpig
Nov 16 '18 at 0:09
|
show 2 more comments
0
0
0
I need to modify this code to first search if a file exists, if so, do nothing but show a message, if not, the code below will automatically create the file. Thanks in advance.
Option Explicit
Public WithEvents MonitorApp As Application
Private Sub Workbook_Open()
Dim strGenericFilePath As String: strGenericFilePath = "\Server2016CommonRegister"
Dim strYear As String: strYear = Year(Date) & ""
Dim strMonth As String: strMonth = MonthName(Month(Date)) & ""
Dim strFileName As String: strFileName = "Register Sheet " & Format(Date, "mmm dd yyyy")
Application.DisplayAlerts = False
If Len(Dir(strGenericFilePath & strYear, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear
End If
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonth
End If
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonth
End If
ActiveWorkbook.SaveAs Filename:= strGenericFilePath & strYear & strMonth & strFileName, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
MsgBox "File Saved As: " & vbNewLine & strGenericFilePath & strYear & strMonth & strFileName
End Sub
excel vba
edited Nov 16 '18 at 1:28
urdearboy
8,0023731
asked Nov 15 '18 at 23:24
Henry NavarroHenry Navarro
34
I need to modify this code to first search if a file exists, if so, do nothing but show a message, if not, the code below will automatically create the file. Thanks in advance.
Option Explicit
Public WithEvents MonitorApp As Application
Private Sub Workbook_Open()
Dim strGenericFilePath As String: strGenericFilePath = "\Server2016CommonRegister"
Dim strYear As String: strYear = Year(Date) & ""
Dim strMonth As String: strMonth = MonthName(Month(Date)) & ""
Dim strFileName As String: strFileName = "Register Sheet " & Format(Date, "mmm dd yyyy")
Application.DisplayAlerts = False
If Len(Dir(strGenericFilePath & strYear, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear
End If
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonth
End If
If Len(Dir(strGenericFilePath & strYear & strMonth, vbDirectory)) = 0 Then
MkDir strGenericFilePath & strYear & strMonth
End If
ActiveWorkbook.SaveAs Filename:= strGenericFilePath & strYear & strMonth & strFileName, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
MsgBox "File Saved As: " & vbNewLine & strGenericFilePath & strYear & strMonth & strFileName
End Sub
excel vba
excel vba
edited Nov 16 '18 at 1:28
urdearboy
8,0023731
asked Nov 15 '18 at 23:24
Henry NavarroHenry Navarro
34
edited Nov 16 '18 at 1:28
urdearboy
8,0023731
asked Nov 15 '18 at 23:24
Henry NavarroHenry Navarro
34
edited Nov 16 '18 at 1:28
urdearboy
8,0023731
edited Nov 16 '18 at 1:28
urdearboy
8,0023731
edited Nov 16 '18 at 1:28
urdearboy
8,0023731
8,0023731
asked Nov 15 '18 at 23:24
Henry NavarroHenry Navarro
34
asked Nov 15 '18 at 23:24
Henry NavarroHenry Navarro
34
asked Nov 15 '18 at 23:24
Henry NavarroHenry Navarro
34
34
I don't see any "code below".
– user2864740
Nov 15 '18 at 23:25
1
This is almost impossible to read. Please edit your original post with the code and someone will format it for you. stackoverflow.com/posts/53329272/edit
– Rey Juna
Nov 15 '18 at 23:40
ok. I figured out how to post it correctly. Let me know if you can help me with the code.
– Henry Navarro
Nov 15 '18 at 23:50
This may help: stackoverflow.com/questions/34974950/… You could leave out the part of checking if the file is newer.
– Rey Juna
Nov 15 '18 at 23:59
I've edited the code just needs peer review. Please please please write code neater. I highly suggest reading: experts-exchange.com/articles/21759/…
– alowflyingpig
Nov 16 '18 at 0:09
|
show 2 more comments
I don't see any "code below".
– user2864740
Nov 15 '18 at 23:25
1
This is almost impossible to read. Please edit your original post with the code and someone will format it for you. stackoverflow.com/posts/53329272/edit
– Rey Juna
Nov 15 '18 at 23:40
ok. I figured out how to post it correctly. Let me know if you can help me with the code.
– Henry Navarro
Nov 15 '18 at 23:50
This may help: stackoverflow.com/questions/34974950/… You could leave out the part of checking if the file is newer.
– Rey Juna
Nov 15 '18 at 23:59
I've edited the code just needs peer review. Please please please write code neater. I highly suggest reading: experts-exchange.com/articles/21759/…
– alowflyingpig
Nov 16 '18 at 0:09
I don't see any "code below".
– user2864740
Nov 15 '18 at 23:25
I don't see any "code below".
– user2864740
Nov 15 '18 at 23:25
1
1
This is almost impossible to read. Please edit your original post with the code and someone will format it for you. stackoverflow.com/posts/53329272/edit
– Rey Juna
Nov 15 '18 at 23:40
This is almost impossible to read. Please edit your original post with the code and someone will format it for you. stackoverflow.com/posts/53329272/edit
– Rey Juna
Nov 15 '18 at 23:40
ok. I figured out how to post it correctly. Let me know if you can help me with the code.
– Henry Navarro
Nov 15 '18 at 23:50
ok. I figured out how to post it correctly. Let me know if you can help me with the code.
– Henry Navarro
Nov 15 '18 at 23:50
This may help: stackoverflow.com/questions/34974950/… You could leave out the part of checking if the file is newer.
– Rey Juna
Nov 15 '18 at 23:59
This may help: stackoverflow.com/questions/34974950/… You could leave out the part of checking if the file is newer.
– Rey Juna
Nov 15 '18 at 23:59
I've edited the code just needs peer review. Please please please write code neater. I highly suggest reading: experts-exchange.com/articles/21759/…
– alowflyingpig
Nov 16 '18 at 0:09
I've edited the code just needs peer review. Please please please write code neater. I highly suggest reading: experts-exchange.com/articles/21759/…
– alowflyingpig
Nov 16 '18 at 0:09
|
show 2 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53329272%2fneed-to-modify-an-existing-code-to-stop-it-from-overwriting-an-existing-file-if%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53329272%2fneed-to-modify-an-existing-code-to-stop-it-from-overwriting-an-existing-file-if%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I don't see any "code below".
– user2864740
Nov 15 '18 at 23:25
1
This is almost impossible to read. Please edit your original post with the code and someone will format it for you. stackoverflow.com/posts/53329272/edit
– Rey Juna
Nov 15 '18 at 23:40
ok. I figured out how to post it correctly. Let me know if you can help me with the code.
– Henry Navarro
Nov 15 '18 at 23:50
This may help: stackoverflow.com/questions/34974950/… You could leave out the part of checking if the file is newer.
– Rey Juna
Nov 15 '18 at 23:59
I've edited the code just needs peer review. Please please please write code neater. I highly suggest reading: experts-exchange.com/articles/21759/…
– alowflyingpig
Nov 16 '18 at 0:09