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

**DocAElstein** · 03-27-2020, 04:36 PM

Originally Posted by fixer

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/showth...ll=1#post12565 , http://www.excelfox.com/forum/showth...ll=1#post12566
)
So I have merged all the Threads together.

Same macro as here: http://www.excelfox.com/forum/showth...ll=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

Code:

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

Code:

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.xlsx")
    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.

Originally Posted by fixer

....
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/showthrea...ory-to-another

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

Code:

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

Or

Code:

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

Code:

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.xlsx")
    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

Code:

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.xlsx")
    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

Originally Posted by fixer

....
but it has issue when the file is not present it shows me a msg that files is not present ..

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,

Code:

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....)-by-vba-macro
https://chandoo.org/forum/threads/co...a-macro.44815/
https://www.excelforum.com/excel-pro...ml#post5378034
https://www.ozgrid.com/forum/index.p...-by-vba-macro/