Add Copy file string

[juneau730]

Good morning all,

I have the following code that works perfectly for what it is intended for, but I need to add a string in it, that copies a file from a network share, to an application path folder and can't seem to find a working way to do so. Hoping you can help out.

It would need to go in before the Set objShell = VBA.CreateObject("wscript.shell") line.

Sub UpdateSeparatedUserInfo()
    Dim objShell As Object
    Dim strGLReport As String
    Dim blnContinue, strQuestion
    Dim objExcel
    
    strQuestion = "This action will restrict all database activity until completed. " _
        & "Do you wish to continue?"
    
    blnContinue = MsgBox(strQuestion, vbYesNo, "Update Separated User Info")
    
    If blnContinue = vbNo Then
        Forms![_Navigation Form].NavBtnSeparatedUsersMonitors.SetFocus
        Exit Sub
    End If
    
 Set objShell = VBA.CreateObject("wscript.shell")
    strGLReport = Application.CurrentProject.Path & "\G-L\Gains and Loss Report.xlsx"
    
 ' MyFunctions.SleepNow 3000
        
    Forms![_Navigation Form].NavigationSubform.Form.Requery
 
  DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM [tbl GainsandLoss]"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tbl GainsandLoss", strGLReport, True
    DoCmd.SetWarnings True
    Forms![_Navigation Form].NavigationSubform.Form.Requery
    
End Sub

 

[Micron]

You'd just need a line that does that? This one provides no options such as determining if it exists or is open. You'd need to use FileScripting object for that.

CopyFile method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[juneau730]

You'd just need a line that does that? This one provides no options such as determining if it exists or is open. You'd need to use FileScripting object for that.

CopyFile method (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Correct, I just need a line that will copy from the network share, to the application path folder. Though thinking about it, a function that replaces/overwrites the existing file in the application path folder would be good.

That is one I had tried using and was unsuccessful in getting it to work in the overall script.

 

[Micron]

There are tons of examples you can find for that. If you create a function for this and call it from your existing code, you might be able to use said function in other cases, making it more versatile. If you get stuck, post your code attempts?

 

[welshgasman]

I would use DIR() to see if it exists.?

Sub CopyTheFile()
Dim strSource As String, strTarget As String
Dim blnFileExists As Boolean

strSource = "c:\temp\invoice.pdf"
strTarget = "c:\temp\Test.pdf"

blnFileExists = Dir(strSource) <> ""
If blnFileExists Then
    FileCopy strSource, strTarget
End If
End Sub