PDA

View Full Version : VBA Dir File Copy and Paste: Check if file exists then copy file from one directory to another. Bat File



fixer
03-04-2020, 02:58 PM
“Moderator” Notice
September 2020
Given up with this “OP” , Avinash around September 2020. https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file-Export-Excel-cell-values-to-delimeted-text-File?p=14972&viewfull=1#post14972
https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file-Export-Excel-cell-values-to-delimeted-text-File?p=14972&viewfull=1#post14972
I am no longer monitoring what its doings. It had curiosity appeal for a while, but even that has worn off me now!
It’s getting worse by the Day. Its still doing whatever it is that it is doing and getting Replies and answers at excelforum.com and likely a few places I don’t know about.





C:\Users\WolfieeeStyle\Desktop\CD.xlsx

C:\Users\WolfieeeStyle\Desktop\DF.xlsx


my file is located here
i need a vba code that if the cd.xlsx & DF.xlsx file is not present C:\Users\WolfieeeStyle\Desktop\CD.xlsx &C:\Users\WolfieeeStyle\Desktop\DF.xlsx here then a msg box should came mentioning error that file is not present

i need vba code of the same
so plz help me in solving these problem

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_ (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxUbeYSvsBH2Gianox4AaABAg.9VYH-07VTyW9gJV5fDAZNe)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgyhQ73u0C3V4bEPhYB4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgzIElpI5OFExnUyrk14AaABAg.9fsvd9zwZii9gMUka-NbIZ)
https://www.youtube.com/watch?v=jdPeMPT98QU (https://www.youtube.com/watch?v=jdPeMPT98QU)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

DocAElstein
03-04-2020, 04:17 PM
Lots of ways to do this is, https://lmgtfy.com/?q=vba+check+if+file+exists ( https://lmgtfy.com/?qtype=search&q=vba+check+if+file+exists )


For example
Dir Function ( https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function )

If _ “MyFile.xls ”_ is at _ “C\Desktop” _ Then _ Dir(“ C\Desktop\MyFile.xls”, vbNormal ) = “MyFile.xls ”

If _ “MyFile.xls ” _is Not at_ “ C\Desktop ” _ Then _Dir(“ C\Desktop\MyFile.xls” , vbNormal ) = “”




' http://www.excelfox.com/forum/showthread.php/2423-Check-file-is-present-or-not
Sub VBACheckIfFileExists() ' https://lmgtfy.com/?q=vba+check+if+file+exists ( https://lmgtfy.com/?qtype=search&q=vba+check+if+file+exists )
Dim StrDirBack As String
Let StrDirBack = Dir(ThisWorkbook.path & "\" & ThisWorkbook.Name, vbNormal)
If StrDirBack = "" Then
MsgBox prompt:=ThisWorkbook.Name & " is not at " & ThisWorkbook.FullName
Else
MsgBox prompt:=ThisWorkbook.Name & " is at " & ThisWorkbook.FullName
MsgBox prompt:=Dir(ThisWorkbook.path & "\" & ThisWorkbook.Name, vbNormal) & " is at " & ThisWorkbook.FullName
End If

Let StrDirBack = Dir(ThisWorkbook.FullName, vbNormal)
If StrDirBack = "" Then
MsgBox prompt:=ThisWorkbook.Name & " is not at " & ThisWorkbook.FullName
Else
MsgBox prompt:=ThisWorkbook.Name & " is at " & ThisWorkbook.FullName
MsgBox prompt:=Dir(ThisWorkbook.FullName, vbNormal) & " is at " & ThisWorkbook.FullName
End If

Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\CD.xlsx", vbNormal)
If StrDirBack = "" Then
MsgBox prompt:="CD.xlsx is not at C:\Users\WolfieeeStyle\Desktop\CD.xlsx"
Else
MsgBox prompt:="CD.xlsx is at C:\Users\WolfieeeStyle\Desktop\CD.xlsx"
MsgBox prompt:=Dir("C:\Users\WolfieeeStyle\Desktop\CD.xlsx", vbNormal) & " is at C:\Users\WolfieeeStyle\Desktop\CD.xlsx"
End If

Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\DF.xlsx", vbNormal)
If StrDirBack = "" Then
MsgBox prompt:="DF.xlsx is not at C:\Users\WolfieeeStyle\Desktop\DF.xlsx"
Else
MsgBox prompt:="DF.xlsx is at C:\Users\WolfieeeStyle\Desktop\DF.xlsx"
MsgBox prompt:=Dir("C:\Users\WolfieeeStyle\Desktop\DF.xlsx", vbNormal) & " is at C:\Users\WolfieeeStyle\Desktop\DF.xlsx"
End If

End Sub


Alan




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/@alanelston2330 (https://www.youtube.com/@alanelston2330)
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z- (https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-)
https://eileenslounge.com/viewtopic.php?p=316154#p316154 (https://eileenslounge.com/viewtopic.php?p=316154#p316154)
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg (https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg)
https://teylyn.com/2017/03/21/dollarsigns/#comment-191 (https://teylyn.com/2017/03/21/dollarsigns/#comment-191)
https://eileenslounge.com/viewtopic.php?p=317050#p317050 (https://eileenslounge.com/viewtopic.php?p=317050#p317050)
https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854 (https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854)
https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875 (https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875)
https://eileenslounge.com/viewtopic.php?p=316057#p316057 (https://eileenslounge.com/viewtopic.php?p=316057#p316057)
https://eileenslounge.com/viewtopic.php?p=316705#p316705 (https://eileenslounge.com/viewtopic.php?p=316705#p316705)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (https://eileenslounge.com/viewtopic.php?p=316704#p316704)
https://eileenslounge.com/viewtopic.php?p=176255#p176255 (https://eileenslounge.com/viewtopic.php?p=176255#p176255)
https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597 (https://eileenslounge.com/viewtopic.php?f=27&t=40919&p=316597#p316597)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316280#p316280 (https://eileenslounge.com/viewtopic.php?p=316280#p316280)
https://eileenslounge.com/viewtopic.php?p=315915#p315915 (https://eileenslounge.com/viewtopic.php?p=315915#p315915)
https://eileenslounge.com/viewtopic.php?p=315512#p315512 (https://eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315744#p315744 (https://eileenslounge.com/viewtopic.php?p=315744#p315744)
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512 (https://www.eileenslounge.com/viewtopic.php?p=315512#p315512)
https://eileenslounge.com/viewtopic.php?p=315680#p315680 (https://eileenslounge.com/viewtopic.php?p=315680#p315680)
https://eileenslounge.com/viewtopic.php?p=315743#p315743 (https://eileenslounge.com/viewtopic.php?p=315743#p315743)
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326 (https://www.eileenslounge.com/viewtopic.php?p=315326#p315326)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40752)
https://eileenslounge.com/viewtopic.php?p=314950#p314950 (https://eileenslounge.com/viewtopic.php?p=314950#p314950)
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940 (https://www.eileenslounge.com/viewtopic.php?p=314940#p314940)
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926 (https://www.eileenslounge.com/viewtopic.php?p=314926#p314926)
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920 (https://www.eileenslounge.com/viewtopic.php?p=314920#p314920)
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 (https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

fixer
03-04-2020, 04:36 PM
Sub VBACheckIfFileExists()
Dim StrDirBack As String
Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb", vbNormal)
If StrDirBack = "" Then
MsgBox prompt:="Download2"
Else

End If

Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\1.xls", vbNormal)
If StrDirBack = "" Then
MsgBox prompt:="Download1"
Else

End If

End Sub




Awesome Doc Sir thats what i like about u
Pefect code


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185 (https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276185#p276185 (https://eileenslounge.com/viewtopic.php?p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276185#p276185 (https://eileenslounge.com/viewtopic.php?p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276673#p276673 (https://eileenslounge.com/viewtopic.php?p=276673#p276673)
https://eileenslounge.com/viewtopic.php?p=276751#p276751 (https://eileenslounge.com/viewtopic.php?p=276751#p276751)
https://eileenslounge.com/viewtopic.php?p=276754#p276754 (https://eileenslounge.com/viewtopic.php?p=276754#p276754)
https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367 (https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367)
https://eileenslounge.com/viewtopic.php?p=274368#p274368 (https://eileenslounge.com/viewtopic.php?p=274368#p274368)
https://eileenslounge.com/viewtopic.php?p=274370#p274370 (https://eileenslounge.com/viewtopic.php?p=274370#p274370)
https://eileenslounge.com/viewtopic.php?p=274578#p274578 (https://eileenslounge.com/viewtopic.php?p=274578#p274578)
https://eileenslounge.com/viewtopic.php?p=274577#p274577 (https://eileenslounge.com/viewtopic.php?p=274577#p274577)
https://eileenslounge.com/viewtopic.php?p=274474#p274474 (https://eileenslounge.com/viewtopic.php?p=274474#p274474)
https://eileenslounge.com/viewtopic.php?p=274579#p274579 (https://eileenslounge.com/viewtopic.php?p=274579#p274579)
https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864 (https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185 (https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276185#p276185 (https://eileenslounge.com/viewtopic.php?p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276185#p276185 (https://eileenslounge.com/viewtopic.php?p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276673#p276673 (https://eileenslounge.com/viewtopic.php?p=276673#p276673)
https://eileenslounge.com/viewtopic.php?p=276751#p276751 (https://eileenslounge.com/viewtopic.php?p=276751#p276751)
https://eileenslounge.com/viewtopic.php?p=276754#p276754 (https://eileenslounge.com/viewtopic.php?p=276754#p276754)
https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367 (https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367)
https://eileenslounge.com/viewtopic.php?p=274368#p274368 (https://eileenslounge.com/viewtopic.php?p=274368#p274368)
https://eileenslounge.com/viewtopic.php?p=274370#p274370 (https://eileenslounge.com/viewtopic.php?p=274370#p274370)
https://eileenslounge.com/viewtopic.php?p=274578#p274578 (https://eileenslounge.com/viewtopic.php?p=274578#p274578)
https://eileenslounge.com/viewtopic.php?p=274577#p274577 (https://eileenslounge.com/viewtopic.php?p=274577#p274577)
https://eileenslounge.com/viewtopic.php?p=274474#p274474 (https://eileenslounge.com/viewtopic.php?p=274474#p274474)
https://eileenslounge.com/viewtopic.php?p=274579#p274579 (https://eileenslounge.com/viewtopic.php?p=274579#p274579)
https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864 (https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

DocAElstein
03-04-2020, 04:45 PM
Your macro is good :)

fixer
03-04-2020, 04:54 PM
Bcoz of u Yesterday u taught me about else so i done the same

DocAElstein
03-04-2020, 05:06 PM
This will also work

Sub VBACheckIfFileExists()
Dim StrDirBack As String
Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb", vbNormal)
If StrDirBack = "" Then
MsgBox prompt:="Download2"
End If

Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\1.xls", vbNormal)
If StrDirBack = "" Then
MsgBox prompt:="Download1"
End If
End Sub

And also this will work.


Sub VBACheckIfFileExists()
Dim StrDirBack As String
Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb", vbNormal)
If StrDirBack = "" Then MsgBox prompt:="Download2"

Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\1.xls", vbNormal)
If StrDirBack = "" Then MsgBox prompt:="Download1"

End Sub
You can choose.

I like to use Else and also EndIf

It is just a personal choice.
Just my personal preference

With Else is good to add ' notes . Later I can remember what is all about

Sub VBACheckIfFileExists()
Dim StrDirBack As String
Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb", vbNormal)
If StrDirBack = "" Then
MsgBox prompt:="Download2"
Else ' My StrDirBack is = "FundsCheck.xlsb" so ...
' ... I do not do anything here
End If

Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\1.xls", vbNormal)
If StrDirBack = "" Then
MsgBox prompt:="Download1"
Else ' My StrDirBack is = "1.xls" so ...
' ... I do not do anything here
End If

End Sub

Sub VBACheckIfFileExists()
Dim StrDirBack As String
Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\FundsCheck.xlsb", vbNormal)
If StrDirBack = "" Then ' My StrDirBack is = "" ===='_-
MsgBox prompt:="Download2"
Else ' My StrDirBack is = "FundsCheck.xlsb" so ...
' ... I do not do anything here
End If ' End If StrDirBack = "" =================='_-

Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\1.xls", vbNormal)
If StrDirBack = "" Then ' My StrDirBack is = "" ====|||
MsgBox prompt:="Download1"
Else ' My StrDirBack is = "1.xls" so ...
' ... I do not do anything here
End If ' End If StrDirBack = "" ==================|||

End Sub

But you can choose.
It is free world. Stay cool and do what you want - do anything you wanna do https://listenonrepeat.com/watch/?v=44JomxG4it8#Eddie_%26_The_Hot_Rods_-_Do_Anything_You_Wanna_Do_-_1977_45rpm

fixer
03-04-2020, 05:09 PM
Sure Sir & thnx for ur Great Support & for making my Journey Simpler

fixer
03-05-2020, 09:41 AM
vba is placed in a seperate file
and the file which we have to copy is located in C:\Users\WolfieeeStyle\Desktop\Files and the file name is 1.xlsx
so we have to copy that file and paste it to (C:\Users\WolfieeeStyle\Desktop\save it) this location only if file is not present
and if file is present in (C:\Users\WolfieeeStyle\Desktop\save it) this location then we have to copy and paste the file to C:\Users\WolfieeeStyle\Desktop\save it\New folder

so plz help me in solving this problem sir

DocAElstein
03-05-2020, 03:30 PM
To search for:-
vba copy file from one directory to another
Try…-
https://lmgtfy.com/?q=vba+copy+file+from+one+directory+to+another
https://lmgtfy.com/?qtype=search&q=vba+copy+file+from+one+directory+to+another&rnd=0.5036912822145674
_.. then maybe https://www.rondebruin.nl/win/s3/win026.htm

Copy one file
For one file you can use the VBA FileCopy statement

Sub Copy_One_File() ' https://www.rondebruin.nl/win/s3/win026.htm FileCopy statement https://docs.microsoft.com/de-de/office/vba/language/reference/user-interface-help/filecopy-statement
FileCopy Source:="C:\Users\WolfieeeStyle\Desktop\Files\1.xlsx", Destination:="C:\Users\WolfieeeStyle\Desktop\save it\1.xlsx"
FileCopy Source:="C:\Users\WolfieeeStyle\Desktop\Files\1.xlsx", Destination:="C:\Users\WolfieeeStyle\Desktop\save it\New folder\1.xlsx"
End Sub




Combining the above with macros from here: http://www.excelfox.com/forum/showthread.php/2423-Check-file-is-present-or-not-VBA-Check-if-file-exists-at-given-path?p=12560&viewfull=1#post12560

For example:-
If the file with the next macro in it, is in the same Folder as the Folder with name .. save it
C:\Users\WolfieeeStyle\Desktop\ save it\New folder
save it New folder.JPG https://imgur.com/BUFvGRh


Sub VBACheckIfFileExists_ThenCopy() ' http://www.excelfox.com/forum/showthread.php/2424-VBA-Check-if-file-exists-then-copy-file-from-one-directory-to-another
Dim StrDirBack As String ' The file which we have to copy is located in C:\Users\WolfieeeStyle\Desktop\Files and the file name is 1.xlsx
' paste it to (C:\Users\WolfieeeStyle\Desktop\save it) this location only if file is not present
Let StrDirBack = Dir(ThisWorkbook.Path & "\save it\1.xlsx", vbNormal)
If StrDirBack = "" Then ' so we have to copy that file and paste it to (C:\Users\WolfieeeStyle\Desktop\save it) this location only if file is not present
FileCopy Source:=ThisWorkbook.Path & "\1.xlsx", Destination:=ThisWorkbook.Path & "\save it\1.xlsx"
Else 'and if file is present in (C:\Users\WolfieeeStyle\Desktop\save it) this location then we have to copy and paste the file to C:\Users\WolfieeeStyle\Desktop\save it\New folder
FileCopy Source:=ThisWorkbook.Path & "\1.xlsx", Destination:=ThisWorkbook.Path & "\save it\New folder\1.xlsx"
End If

End Sub





Copy and paste file , 1.xlsx , to a new location if it does not exist there, else copy and paste it to a different location.

The source file to be copied has the name , 1.xlsx .
It is in folder , Files , at the Desktop , C:\Users\WolfieeeStyle\Desktop
So it has the full path and file name of:-
C:\Users\WolfieeeStyle\Desktop\Files\1.xlsx

The file needs to be pasted to one of two locations…
The full path and file name for the file to be pasted ( the Destination ) is:-

Either

__ if file is not present at C:\Users\WolfieeeStyle\Desktop\save , it then paste it there:-
C:\Users\WolfieeeStyle\Desktop\save it\1.xlsx

__ Else if the file, 1.xlsx , is already present at C:\Users\WolfieeeStyle\Desktop\save it , then paste it at:
C:\Users\WolfieeeStyle\Desktop\save it\New folder\1.xlsx



Sub VBACheckIfFileExists_Then_Copy() ' http://www.excelfox.com/forum/showthread.php/2424-VBA-Check-if-file-exists-then-copy-file-from-one-directory-to-another
Dim StrDirBack As String '
Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\save it\1.xlsx", vbNormal) ' If 1.xlsx is not present at C:\Users\WolfieeeStyle\Desktop\save it then Dir( ) will return ""
If StrDirBack = "" Then ' copy 1.xlsx to (C:\Users\WolfieeeStyle\Desktop\save it) only if file is not present at C:\Users\WolfieeeStyle\Desktop\save it
'The file which we have to copy is located in C:\Users\WolfieeeStyle\Desktop\Files and the file name is 1.xlsx
FileCopy Source:="C:\Users\WolfieeeStyle\Desktop\Files\1.xlsx", Destination:="C:\Users\WolfieeeStyle\Desktop\save it\1.xlsx"
Else 'and if file is present in (C:\Users\WolfieeeStyle\Desktop\save it) this location then we have to copy and paste the file to C:\Users\WolfieeeStyle\Desktop\save it\New folder
'The file which we have to copy is located in C:\Users\WolfieeeStyle\Desktop\Files and the file name is 1.xlsx
FileCopy Source:="C:\Users\WolfieeeStyle\Desktop\Files\1.xlsx", Destination:="C:\Users\WolfieeeStyle\Desktop\save it\New folder\1.xlsx"
End If

End Sub




Simplified:-

Sub VBACheckIfFileExists_Then__Copy()
Dim StrDirBack As String
Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\save it\1.xlsx", vbNormal)
If StrDirBack = "" Then
FileCopy "C:\Users\WolfieeeStyle\Desktop\Files\1.xlsx", "C:\Users\WolfieeeStyle\Desktop\save it\1.xlsx"
Else
FileCopy "C:\Users\WolfieeeStyle\Desktop\Files\1.xlsx", "C:\Users\WolfieeeStyle\Desktop\save it\New folder\1.xlsx"
End If
End Sub

Sub VBACheckIfFileExists_Then___Copy()
If Dir("C:\Users\WolfieeeStyle\Desktop\save it\1.xlsx") = "" Then
FileCopy "C:\Users\WolfieeeStyle\Desktop\Files\1.xlsx", "C:\Users\WolfieeeStyle\Desktop\save it\1.xlsx"
Else
FileCopy "C:\Users\WolfieeeStyle\Desktop\Files\1.xlsx", "C:\Users\WolfieeeStyle\Desktop\save it\New folder\1.xlsx"
End If
End Sub






Alan





macros in first worksheets code module of process.xlsm ,
Right Mouse Click First Tab View Code.jpg : https://imgur.com/8cu1Mbn
2775

fixer
03-27-2020, 01:49 PM
my file name is 4.xlsx
i have to copy this file and paste it to another path( if 4.xlsx file doesn't exist then copy paste it to C:\Users\WolfieeeStyle\Desktop/sholtan , but if file is there in the path C:\Users\WolfieeeStyle\Desktop/sholtan then do nothing)
i want to do this by vba so plz have a look sir and help me in solving this problem sir
file is located at C:\Users\WolfieeeStyle\Desktop/4.xlsx and we have to copy this file and paste it to C:\Users\WolfieeeStyle\Desktop/sholtan

DocAElstein
03-27-2020, 04:36 PM
my file name is 4.xlsx
i have to copy this file and paste it to another path( if 4.xlsx file doesn't exist then copy paste it to C:\Users\WolfieeeStyle\Desktop/sholtan , but if file is there in the path C:\Users\WolfieeeStyle\Desktop/sholtan then do nothing)
i want to do this by vba so plz have a look sir and help me in solving this problem sir
file is located at C:\Users\WolfieeeStyle\Desktop/4.xlsx and we have to copy this file and paste it to C:\Users\WolfieeeStyle\Desktop/sholtan
Error you have
Desktop/

Should be
Desktop\


Very similar to your previous questions ( http://www.excelfox.com/forum/showthread.php/2424-VBA-Copy-and-Paste-Check-if-file-exists-then-copy-file-from-one-directory-to-another?p=12565&viewfull=1#post12565 , http://www.excelfox.com/forum/showthread.php/2424-VBA-Copy-and-Paste-Check-if-file-exists-then-copy-file-from-one-directory-to-another?p=12566&viewfull=1#post12566
)
So I have merged all the Threads together.

Same macro as here: http://www.excelfox.com/forum/showthread.php/2424-VBA-Copy-and-Paste-Check-if-file-exists-then-copy-file-from-one-directory-to-another?p=12566&viewfull=1#post12566


….file is located at C:\Users\WolfieeeStyle\Desktop\4.xlsx, so Source is C:\Users\WolfieeeStyle\Desktop\4.xlsx , and we have to copy this file and paste it to C:\Users\WolfieeeStyle\Desktop\sholtan so Destination is C:\Users\WolfieeeStyle\Desktop\sholtan\4.xlsx




Sub VBACheckIfFileExists_ThenCopy() ' http://www.excelfox.com/forum/showthread.php/2424-VBA-Copy-and-Paste-Check-if-file-exists-then-copy-file-from-one-directory-to-another?p=13010&viewfull=1#post13010 http://www.excelfox.com/forum/showthread.php/2424-VBA-Check-if-file-exists-then-copy-file-from-one-directory-to-another
Dim StrDirBack As String ' see if file, 4.xlsx , is there in the path C:\Users\WolfieeeStyle\Desktop\sholtan
Let StrDirBack = Dir("C:\Users\WolfieeeStyle\Desktop\sholtan\4.xlsx", vbNormal)
If StrDirBack = "" Then ' if Dir("C:\Users\WolfieeeStyle\Desktop\sholtan\4.xlsx", vbNormal) = "" then file 4.xlsx is not at C:\Users\WolfieeeStyle\Desktop\sholtan
' case StrDirBack = "" file is located at C:\Users\WolfieeeStyle\Desktop\4.xlsx, so Source is C:\Users\WolfieeeStyle\Desktop\4.xlsx , and we have to copy this file and paste it to C:\Users\WolfieeeStyle\Desktop\sholtan so Destination is C:\Users\WolfieeeStyle\Desktop\sholtan
FileCopy Source:="C:\Users\WolfieeeStyle\Desktop\4.xlsx", Destination:="C:\Users\WolfieeeStyle\Desktop\sholtan\4.xlsx"
Else ' case StrDirBack <> ""
' if StrDirBack <> "" , then file is already there, so do nothing .... ... if file is there in the path C:\Users\WolfieeeStyle\Desktop\sholtan then do nothing
End If

End Sub

fixer
03-29-2020, 05:05 PM
Thnx Alot Doc Sir for giving ur precious time & Great support for helping me in solving this Problem Sir
Problem Solved Sir

fixer
06-28-2020, 08:33 PM
Hi

Sub STEP12()
Dim Val As String, wb1 As Workbook, wb2 As Workbook, srcWS As Worksheet, desWS As Worksheet
Dim i As Long, v1 As Variant, v2 As Variant, rngList As Object
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\ap.xls")
Set wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xl sx")
Set desWS = wb1.Worksheets.Item(1)
Set srcWS = wb2.Worksheets.Item(1)
v1 = desWS.Range("E2", desWS.Range("E" & desWS.Rows.Count).End(xlUp)).Value
v2 = srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Resize(, 2).Value
Set rngList = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(v1, 1)
Val = v1(i, 1)
If Not rngList.exists(Val) Then
rngList.Add Key:=Val, Item:=i + 1
End If
Next i
For i = 1 To UBound(v2, 1)
Val = v2(i, 1)
If rngList.exists(Val) Then
desWS.Cells(rngList(Val), 25) = v2(i, 2)
End If
Next i
Application.ScreenUpdating = True

wb1.Save
wb2.Save
wb1.Close
wb2.Close

End Sub




This is the macro
I need a slight change in it
If ap.xls files is present then only this macro will work & if ap.xls file is not present then it should not do anything
I can use on error resume next code in this macro but u have suggested not to use that
So i need help for the same

DocAElstein
06-30-2020, 03:33 PM
Hi
On Error Resume Next is bad becauase it does not solve any problem. It does not stop any error. It does not stop anything going wrong. It just ignores errors. The error is still there. But On Error Resume Next tells VBA not to tell you about the error, and not to stop the macro with an error message, but instead to keep running the macro even if there was an error.
Also, even if there are more errors, they will all be ignored.
So this may cause a big chaos because you will never know where or what went wrong.


....
If ap.xls files is present then only this macro will work & if ap.xls file is not present then it should not do anything...
Probably you want something like we did here: https://excelfox.com/forum/showthread.php/2424-VBA-Dir-File-Copy-and-Paste-Check-if-file-exists-then-copy-file-from-one-directory-to-another

One simpla way, would be to just Exit the Sub at the start of the macro , if the File is not present

Sub STEP12() '
If Dir("C:\Users\WolfieeeStyle\Desktop\ap.xls", vbNormal) = "" Then Exit Sub





Or

Sub STEP12() '
If Dir("C:\Users\WolfieeeStyle\Desktop\ap.xls", vbNormal) = "" Then MsgBox Prompt:="The file is not present": Exit Sub






Alan

fixer
07-01-2020, 04:03 PM
Sub STEP12()
If Dir("C:\Users\WolfieeeStyle\Desktop\ap.xls", vbNormal) = "" Then Exit Sub
Dim Val As String, wb1 As Workbook, wb2 As Workbook, srcWS As Worksheet, desWS As Worksheet
Dim i As Long, v1 As Variant, v2 As Variant, rngList As Object
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\ap.xls")
Set wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xl sx")
Set desWS = wb1.Worksheets.Item(1)
Set srcWS = wb2.Worksheets.Item(1)
v1 = desWS.Range("E2", desWS.Range("E" & desWS.Rows.Count).End(xlUp)).Value
v2 = srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Resize(, 2).Value
Set rngList = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(v1, 1)
Val = v1(i, 1)
If Not rngList.exists(Val) Then
rngList.Add Key:=Val, Item:=i + 1
End If
Next i
For i = 1 To UBound(v2, 1)
Val = v2(i, 1)
If rngList.exists(Val) Then
desWS.Cells(rngList(Val), 25) = v2(i, 2)
End If
Next i
Application.ScreenUpdating = True

wb1.Save
wb2.Save
wb1.Close
wb2.Close

End Sub

According to U Doc Sir this will be the solution of my Problem
Thnx Alot Doc Sir for helping me in solving this problem
I haven't run the macro yet
I will run it soon & I will check it
Thnx Sir

fixer
07-04-2020, 07:38 PM
Doc Sir
This code is doing perfect thing
but it has issue when the file is not present it shows me a msg that files is not present & i don't want that
So plz have a relook Sir and plz help me out in solving this problem Doc Sir


Sub STEP12()
If Dir("C:\Users\WolfieeeStyle\Desktop\ap.xls", vbNormal) = "" Then Exit Sub
Dim Val As String, wb1 As Workbook, wb2 As Workbook, srcWS As Worksheet, desWS As Worksheet
Dim i As Long, v1 As Variant, v2 As Variant, rngList As Object
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\ap.xls")
Set wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\AlertCodes.xl sx")
Set desWS = wb1.Worksheets.Item(1)
Set srcWS = wb2.Worksheets.Item(1)
v1 = desWS.Range("E2", desWS.Range("E" & desWS.Rows.Count).End(xlUp)).Value
v2 = srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Resize(, 2).Value
Set rngList = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(v1, 1)
Val = v1(i, 1)
If Not rngList.exists(Val) Then
rngList.Add Key:=Val, Item:=i + 1
End If
Next i
For i = 1 To UBound(v2, 1)
Val = v2(i, 1)
If rngList.exists(Val) Then
desWS.Cells(rngList(Val), 25) = v2(i, 2)
End If
Next i
Application.ScreenUpdating = True

wb1.Save
wb2.Save
wb1.Close
wb2.Close

End Sub


I think we have to use Application.ScreenUpdating = false or Application.display - false something like that
i dont know what to do so plz guide

DocAElstein
07-05-2020, 07:48 PM
Hi

....
but it has issue when the file is not present it shows me a msg that files is not present ..
:confused:
I don’t know what you are trying to say…

Do you mean that you get an error?
If so, what does the error say exactly
?
Where does the error occur ?

Alan

fixer
07-05-2020, 08:04 PM
I am really Sorry Doc Sir
It is working perfect
I ran it & i check that
Actually it was not the error it was the popup stating that ap.xls is not present or has been moved
Probem Solved

fixer
07-05-2020, 08:05 PM
Thnx Alot Doc Sir for helping me in solving this problem
Have a Great Day

fixer
08-12-2020, 11:22 AM
Hi Experts,


Public Declare PtrSafe Function ShellExecute _
Lib "shell32.dll" _
Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long
Sub FFF()
Dim strFileName As String
Dim strFileExists As String

strFileName = "C:\Users\WolfieeeStyle\Desktop\Close Excel.lnk"
strFileExists = Dir(strFileName)

If strFileExists = "" Then
Set FSO = CreateObject("scripting.filesystemobject")
FSO.MoveFile Source:="C:\Users\WolfieeeStyle\Desktop\Close Excel.lnk", Destination:="C:\Users\WolfieeeStyle\Desktop\WolfieeeStyle\9.15\"
ShellExecute 0, "OPEN", "C:\Users\WolfieeeStyle\Desktop\WolfieeeStyle\9.15\ Close Excel.lnk", "", "", 1
Else

End If

End Sub

I am looking for a macro that will

First Check Close Excel.lnk is located or not & if it is located then move that Close Excel.lnk to C:\Users\WolfieeeStyle\Desktop\WolfieeeStyle\9.15\ & then run that Close Excel.lnk

& If the Close Excel.lnk file is not located then do nothing
This macro is not working i think it has issues

Thnx

https://www.vbforums.com/showthread.php?888171-Conditionally-Run-amp-move-the-bat-file-shortcut(i-e-lnk-file)-by-vba-macro
https://chandoo.org/forum/threads/conditionally-run-move-the-bat-file-shortcut-i-e-lnk-file-by-vba-macro.44815/
https://www.excelforum.com/excel-programming-vba-macros/1324191-conditionally-run-and-move-the-bat-file-shortcut-i-e-lnk-file-by-vba-macro.html#post5378034
https://www.ozgrid.com/forum/index.php?thread/1228046-conditionally-run-move-the-bat-file-shortcut-i-e-lnk-file-by-vba-macro/

mail
08-14-2020, 12:55 AM
Problem Solved

fixer
08-14-2020, 02:36 PM
This Problem is not Solved, I have this macro & it works sometimes & sometimes it doesnt works

Public Declare PtrSafe Function ShellExecute _
Lib "shell32.dll" _
Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long


Sub STEP2()
Dim strFileName As String
Dim strFileExists As String

strFileName = "C:\Users\WolfieeeStyle\Desktop\Close Excel.lnk"
strFileExists = Dir(strFileName)

If strFileExists > "" Then
Set fso = CreateObject("scripting.filesystemobject")
fso.MoveFile Source:="C:\Users\WolfieeeStyle\Desktop\Close Excel.lnk", Destination:="C:\Users\WolfieeeStyle\Desktop\WolfieeeStyle\9.15\"
ShellExecute 0, "OPEN", "C:\Users\WolfieeeStyle\Desktop\WolfieeeStyle\9.15\ Close Excel.lnk", "", "", 1
Else

End If

End Sub

fixer
08-14-2020, 07:49 PM
Problem Solved
Another macro has made a mistake & it is working perfect
Thnx Alot Sir

fixer
08-16-2020, 02:34 PM
“Moderator” Notice
September 2020
Given up with this “OP” , Avinash around September 2020. https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file-Export-Excel-cell-values-to-delimeted-text-File?p=14972&viewfull=1#post14972
https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file-Export-Excel-cell-values-to-delimeted-text-File?p=14972&viewfull=1#post14972
I am no longer monitoring what its doings. It had curiosity appeal for a while, but even that has worn off me now!
It’s getting worse by the Day. Its still doing whatever it is that it is doing and getting Replies and answers at excelforum.com and likely a few places I don’t know about.






Hi Experts,


Option Explicit

Sub STEP2()
Dim wkb As Workbook
Set wkb = Workbooks.Add
wkb.SaveAs Filename:="C:\Users\WolfieeeStyle\Desktop\FALSEFALSEFALSE.xls x"
wkb.Close
Dim fso As Object
Dim strFileName As String
Dim strFileExists As String
Set fso = CreateObject("scripting.filesystemobject")

strFileName = "C:\Users\WolfieeeStyle\Desktop\FALSEFALSEFALSE.xls x"
strFileExists = Dir(strFileName)

If strFileExists > "" Then
fso.movefile Source:="C:\Users\WolfieeeStyle\Desktop\FALSEFALSEFALSE.xls x", _
Destination:="C:\Users\WolfieeeStyle\Desktop\WolfieeeStyle\9.15\ FALSEFALSEFALSE.xlsx"
Shell "taskkill /F /IM EXCEL.EXE"
Else

End If

End Sub
This macro does what i want & I need a confirmation that this macro is perfect & it has no errors & if there is anything which needs to be modified then plz modify the same & let me know


Thnx




Cross Post

https://eileenslounge.com/viewtopic.php?p=273280#p273280

fixer
08-20-2020, 02:28 AM
Hello Guys,

If column M data of ap.xls matches with column A data of sample2.xlsx (sheet2) (May be Column M data will be blank or 0 so ignore that)& column G data of oo.xlsx matches with column A data of sample2.xlsx (sheet2) then move the file C:\Users\WolfieeeStyle\Desktop\sample5.xlsx to C:\Users\WolfieeeStyle\Desktop

Macro will be placed in a seperate file macro.xlsm

(Number will be positive or negatiove , No issue with the positive or negative , the number should be present is must)



Thnx Alot

fixer
08-20-2020, 02:06 PM
Problem Solved

DocAElstein
09-26-2020, 05:56 PM
As almost always you forgot to tell us how/ where it was solved