File not moving VBA
Call MOVEDFFILES("C:TEMPMAIN FOLDERINVOICESCOUNTRYInvoices" & SEASON & " DF Invoices", "C:TEMPMAIN FOLDERINVOICESCOUNTRYInvoices" & SEASON & " DF InvoicesImported")
I have created the above code to call the below code and move files if they don't exist in the new folder, and delete them in the original folder if they do. however whilst I can use Name OldLocation & MyFile As NewLocation & MyFile to move the files, they dissapear when trying to use the code below. This code works else where for a different file path, the only difference is using *.csv as MyFile, could this cause an issue?
Private Sub MOVEDFFILES(OldLocation As Variant, NewLocation As Variant)
'Makes the file path if not there
If Dir(NewLocation, vbDirectory) = "" Then
MkDir NewLocation
End If
'Moves the files from one location to another
MyFile = Dir(OldLocation & "*.csv")
Do Until MyFile = ""
If Not NewLocation & MyFile > 0 Then
Name OldLocation & MyFile As NewLocation & MyFile
Else
Kill OldLocation & MyFile
End If
MyFile = Dir
Loop
End Sub
vba file access
asked Nov 15 '18 at 17:09
Carly DewdneyCarly Dewdney
133
add a comment |
Call MOVEDFFILES("C:TEMPMAIN FOLDERINVOICESCOUNTRYInvoices" & SEASON & " DF Invoices", "C:TEMPMAIN FOLDERINVOICESCOUNTRYInvoices" & SEASON & " DF InvoicesImported")
I have created the above code to call the below code and move files if they don't exist in the new folder, and delete them in the original folder if they do. however whilst I can use Name OldLocation & MyFile As NewLocation & MyFile to move the files, they dissapear when trying to use the code below. This code works else where for a different file path, the only difference is using *.csv as MyFile, could this cause an issue?
Private Sub MOVEDFFILES(OldLocation As Variant, NewLocation As Variant)
'Makes the file path if not there
If Dir(NewLocation, vbDirectory) = "" Then
MkDir NewLocation
End If
'Moves the files from one location to another
MyFile = Dir(OldLocation & "*.csv")
Do Until MyFile = ""
If Not NewLocation & MyFile > 0 Then
Name OldLocation & MyFile As NewLocation & MyFile
Else
Kill OldLocation & MyFile
End If
MyFile = Dir
Loop
End Sub
vba file access
asked Nov 15 '18 at 17:09
Carly DewdneyCarly Dewdney
133
the checkIf Not NewLocation & MyFile > 0 Thencannot work. You are comparing a (concatenated) string with a number!
– FunThomas
Nov 15 '18 at 17:19
add a comment |
Call MOVEDFFILES("C:TEMPMAIN FOLDERINVOICESCOUNTRYInvoices" & SEASON & " DF Invoices", "C:TEMPMAIN FOLDERINVOICESCOUNTRYInvoices" & SEASON & " DF InvoicesImported")
I have created the above code to call the below code and move files if they don't exist in the new folder, and delete them in the original folder if they do. however whilst I can use Name OldLocation & MyFile As NewLocation & MyFile to move the files, they dissapear when trying to use the code below. This code works else where for a different file path, the only difference is using *.csv as MyFile, could this cause an issue?
Private Sub MOVEDFFILES(OldLocation As Variant, NewLocation As Variant)
'Makes the file path if not there
If Dir(NewLocation, vbDirectory) = "" Then
MkDir NewLocation
End If
'Moves the files from one location to another
MyFile = Dir(OldLocation & "*.csv")
Do Until MyFile = ""
If Not NewLocation & MyFile > 0 Then
Name OldLocation & MyFile As NewLocation & MyFile
Else
Kill OldLocation & MyFile
End If
MyFile = Dir
Loop
End Sub
vba file access
asked Nov 15 '18 at 17:09
Carly DewdneyCarly Dewdney
133
Call MOVEDFFILES("C:TEMPMAIN FOLDERINVOICESCOUNTRYInvoices" & SEASON & " DF Invoices", "C:TEMPMAIN FOLDERINVOICESCOUNTRYInvoices" & SEASON & " DF InvoicesImported")
I have created the above code to call the below code and move files if they don't exist in the new folder, and delete them in the original folder if they do. however whilst I can use Name OldLocation & MyFile As NewLocation & MyFile to move the files, they dissapear when trying to use the code below. This code works else where for a different file path, the only difference is using *.csv as MyFile, could this cause an issue?
Private Sub MOVEDFFILES(OldLocation As Variant, NewLocation As Variant)
'Makes the file path if not there
If Dir(NewLocation, vbDirectory) = "" Then
MkDir NewLocation
End If
'Moves the files from one location to another
MyFile = Dir(OldLocation & "*.csv")
Do Until MyFile = ""
If Not NewLocation & MyFile > 0 Then
Name OldLocation & MyFile As NewLocation & MyFile
Else
Kill OldLocation & MyFile
End If
MyFile = Dir
Loop
End Sub
vba file access
vba file access
asked Nov 15 '18 at 17:09
Carly DewdneyCarly Dewdney
133
asked Nov 15 '18 at 17:09
Carly DewdneyCarly Dewdney
133
edited Nov 16 '18 at 10:19
Carly Dewdney
asked Nov 15 '18 at 17:09
Carly DewdneyCarly Dewdney
133
asked Nov 15 '18 at 17:09
Carly DewdneyCarly Dewdney
133
asked Nov 15 '18 at 17:09
Carly DewdneyCarly Dewdney
133
133
the checkIf Not NewLocation & MyFile > 0 Thencannot work. You are comparing a (concatenated) string with a number!
– FunThomas
Nov 15 '18 at 17:19
add a comment |
the checkIf Not NewLocation & MyFile > 0 Thencannot work. You are comparing a (concatenated) string with a number!
– FunThomas
Nov 15 '18 at 17:19
the check
If Not NewLocation & MyFile > 0 Then cannot work. You are comparing a (concatenated) string with a number!– FunThomas
Nov 15 '18 at 17:19
the check
If Not NewLocation & MyFile > 0 Then cannot work. You are comparing a (concatenated) string with a number!– FunThomas
Nov 15 '18 at 17:19
add a comment |
1 Answer
1
active
oldest
votes
The problem is that your check if the file exists in the new location is wrong.
Easiest way to check it would be to issue a Dir-command, but that would break your loop. You can have only one Dir command open, issuing a Dir within the loop to check if the file exists in the new location would cause the command MyFile = Dir fail to check for the next file in the old location.
Turns out that you don't have to do the check at all: Simply issue both, the Name and the Kill command. Trick is to ignore any errors. If the file doesn't exists in the new location, the Name would move it and the Kill doesn't have to delete anything because the file is already gone.. If the file already exists in the new location, the Name will fail and the Kill will do it's job...
So, this is one of the really few situations to use the infamous On Error Resume Next:
f = Dir(OldLocation & "*.csv")
Do Until f = ""
On Error Resume Next
Name OldLocation & f As NewLocation & f
Kill OldLocation & f
On Error GoTo 0
f = Dir
Loop
answered Nov 15 '18 at 17:39
FunThomasFunThomas
5,0661626
Thomas you have made my morning - it works perfectly! I'm still fairly new to coding so I try and take the simple options, you have written me some beautiful code there :) I did try the on error resume next process myself but I didn't quite grasp how it worked and crashed the whole database.... the on error goto 0 part is clearly important!
– Carly Dewdney
Nov 16 '18 at 10:17
Generally, you should never use theOn Error Resume Next- except for the (rare) specific cases where you know that a statement may issue an error, and you will take care about that by yourself (see for example stackoverflow.com/a/6688482/7599798). But you always should issue theOn Error Goto 0immediately after that statement.
– FunThomas
Nov 16 '18 at 10:42
add a comment |
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
});
}
});
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%2f53324633%2ffile-not-moving-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The problem is that your check if the file exists in the new location is wrong.
Easiest way to check it would be to issue a Dir-command, but that would break your loop. You can have only one Dir command open, issuing a Dir within the loop to check if the file exists in the new location would cause the command MyFile = Dir fail to check for the next file in the old location.
Turns out that you don't have to do the check at all: Simply issue both, the Name and the Kill command. Trick is to ignore any errors. If the file doesn't exists in the new location, the Name would move it and the Kill doesn't have to delete anything because the file is already gone.. If the file already exists in the new location, the Name will fail and the Kill will do it's job...
So, this is one of the really few situations to use the infamous On Error Resume Next:
f = Dir(OldLocation & "*.csv")
Do Until f = ""
On Error Resume Next
Name OldLocation & f As NewLocation & f
Kill OldLocation & f
On Error GoTo 0
f = Dir
Loop
answered Nov 15 '18 at 17:39
FunThomasFunThomas
5,0661626
Thomas you have made my morning - it works perfectly! I'm still fairly new to coding so I try and take the simple options, you have written me some beautiful code there :) I did try the on error resume next process myself but I didn't quite grasp how it worked and crashed the whole database.... the on error goto 0 part is clearly important!
– Carly Dewdney
Nov 16 '18 at 10:17
Generally, you should never use theOn Error Resume Next- except for the (rare) specific cases where you know that a statement may issue an error, and you will take care about that by yourself (see for example stackoverflow.com/a/6688482/7599798). But you always should issue theOn Error Goto 0immediately after that statement.
– FunThomas
Nov 16 '18 at 10:42
add a comment |
The problem is that your check if the file exists in the new location is wrong.
Easiest way to check it would be to issue a Dir-command, but that would break your loop. You can have only one Dir command open, issuing a Dir within the loop to check if the file exists in the new location would cause the command MyFile = Dir fail to check for the next file in the old location.
Turns out that you don't have to do the check at all: Simply issue both, the Name and the Kill command. Trick is to ignore any errors. If the file doesn't exists in the new location, the Name would move it and the Kill doesn't have to delete anything because the file is already gone.. If the file already exists in the new location, the Name will fail and the Kill will do it's job...
So, this is one of the really few situations to use the infamous On Error Resume Next:
f = Dir(OldLocation & "*.csv")
Do Until f = ""
On Error Resume Next
Name OldLocation & f As NewLocation & f
Kill OldLocation & f
On Error GoTo 0
f = Dir
Loop
answered Nov 15 '18 at 17:39
FunThomasFunThomas
5,0661626
Thomas you have made my morning - it works perfectly! I'm still fairly new to coding so I try and take the simple options, you have written me some beautiful code there :) I did try the on error resume next process myself but I didn't quite grasp how it worked and crashed the whole database.... the on error goto 0 part is clearly important!
– Carly Dewdney
Nov 16 '18 at 10:17
Generally, you should never use theOn Error Resume Next- except for the (rare) specific cases where you know that a statement may issue an error, and you will take care about that by yourself (see for example stackoverflow.com/a/6688482/7599798). But you always should issue theOn Error Goto 0immediately after that statement.
– FunThomas
Nov 16 '18 at 10:42
add a comment |
The problem is that your check if the file exists in the new location is wrong.
Easiest way to check it would be to issue a Dir-command, but that would break your loop. You can have only one Dir command open, issuing a Dir within the loop to check if the file exists in the new location would cause the command MyFile = Dir fail to check for the next file in the old location.
Turns out that you don't have to do the check at all: Simply issue both, the Name and the Kill command. Trick is to ignore any errors. If the file doesn't exists in the new location, the Name would move it and the Kill doesn't have to delete anything because the file is already gone.. If the file already exists in the new location, the Name will fail and the Kill will do it's job...
So, this is one of the really few situations to use the infamous On Error Resume Next:
f = Dir(OldLocation & "*.csv")
Do Until f = ""
On Error Resume Next
Name OldLocation & f As NewLocation & f
Kill OldLocation & f
On Error GoTo 0
f = Dir
Loop
answered Nov 15 '18 at 17:39
FunThomasFunThomas
5,0661626
The problem is that your check if the file exists in the new location is wrong.
Easiest way to check it would be to issue a Dir-command, but that would break your loop. You can have only one Dir command open, issuing a Dir within the loop to check if the file exists in the new location would cause the command MyFile = Dir fail to check for the next file in the old location.
Turns out that you don't have to do the check at all: Simply issue both, the Name and the Kill command. Trick is to ignore any errors. If the file doesn't exists in the new location, the Name would move it and the Kill doesn't have to delete anything because the file is already gone.. If the file already exists in the new location, the Name will fail and the Kill will do it's job...
So, this is one of the really few situations to use the infamous On Error Resume Next:
f = Dir(OldLocation & "*.csv")
Do Until f = ""
On Error Resume Next
Name OldLocation & f As NewLocation & f
Kill OldLocation & f
On Error GoTo 0
f = Dir
Loop
answered Nov 15 '18 at 17:39
FunThomasFunThomas
5,0661626
answered Nov 15 '18 at 17:39
FunThomasFunThomas
5,0661626
answered Nov 15 '18 at 17:39
FunThomasFunThomas
5,0661626
answered Nov 15 '18 at 17:39
FunThomasFunThomas
5,0661626
5,0661626
Thomas you have made my morning - it works perfectly! I'm still fairly new to coding so I try and take the simple options, you have written me some beautiful code there :) I did try the on error resume next process myself but I didn't quite grasp how it worked and crashed the whole database.... the on error goto 0 part is clearly important!
– Carly Dewdney
Nov 16 '18 at 10:17
Generally, you should never use theOn Error Resume Next- except for the (rare) specific cases where you know that a statement may issue an error, and you will take care about that by yourself (see for example stackoverflow.com/a/6688482/7599798). But you always should issue theOn Error Goto 0immediately after that statement.
– FunThomas
Nov 16 '18 at 10:42
add a comment |
Thomas you have made my morning - it works perfectly! I'm still fairly new to coding so I try and take the simple options, you have written me some beautiful code there :) I did try the on error resume next process myself but I didn't quite grasp how it worked and crashed the whole database.... the on error goto 0 part is clearly important!
– Carly Dewdney
Nov 16 '18 at 10:17
Generally, you should never use theOn Error Resume Next- except for the (rare) specific cases where you know that a statement may issue an error, and you will take care about that by yourself (see for example stackoverflow.com/a/6688482/7599798). But you always should issue theOn Error Goto 0immediately after that statement.
– FunThomas
Nov 16 '18 at 10:42
Thomas you have made my morning - it works perfectly! I'm still fairly new to coding so I try and take the simple options, you have written me some beautiful code there :) I did try the on error resume next process myself but I didn't quite grasp how it worked and crashed the whole database.... the on error goto 0 part is clearly important!
– Carly Dewdney
Nov 16 '18 at 10:17
Thomas you have made my morning - it works perfectly! I'm still fairly new to coding so I try and take the simple options, you have written me some beautiful code there :) I did try the on error resume next process myself but I didn't quite grasp how it worked and crashed the whole database.... the on error goto 0 part is clearly important!
– Carly Dewdney
Nov 16 '18 at 10:17
Generally, you should never use the
On Error Resume Next - except for the (rare) specific cases where you know that a statement may issue an error, and you will take care about that by yourself (see for example stackoverflow.com/a/6688482/7599798). But you always should issue the On Error Goto 0 immediately after that statement.– FunThomas
Nov 16 '18 at 10:42
Generally, you should never use the
On Error Resume Next - except for the (rare) specific cases where you know that a statement may issue an error, and you will take care about that by yourself (see for example stackoverflow.com/a/6688482/7599798). But you always should issue the On Error Goto 0 immediately after that statement.– FunThomas
Nov 16 '18 at 10:42
add a comment |
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.
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%2f53324633%2ffile-not-moving-vba%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
the check
If Not NewLocation & MyFile > 0 Thencannot work. You are comparing a (concatenated) string with a number!– FunThomas
Nov 15 '18 at 17:19