Convert VBScript to VBA (Copy and Replace Activeworkbook)

[YasserKhalil]

Hello everyone

I have following code (VBScript) in vbs file named "Update.vbs"

Code: Select all

SourceFile =WScript.Arguments.Item(0)
DestinationFile=WScript.Arguments.Item(1)
DestinationFolder=WScript.Arguments.Item(2)

'Close Existing Workbook
Set objXl = GetObject(, "Excel.Application")
on Error Resume Next
objXL.Workbooks("MyProject.xlsm").Close(False)

'Install New XLSM Workbook
Set fso = CreateObject("Scripting.FileSystemObject")
    'Check To See If The File Already Exists In The Destination Folder
    If fso.FileExists(DestinationFile) Then
        'Check To See If The File Is Read-Only
        If Not fso.GetFile(DestinationFile).Attributes And 1 Then 
            'The File Exists And Is Not Read-Only.  Safe To Replace The File
            fso.CopyFile SourceFile, DestinationFolder, True
        Else 
            'The File Exists And Is Read-Only, Remove The Read-Only Attribute
            fso.GetFile(DestinationFile).Attributes = fso.GetFile(DestinationFile).Attributes - 1
            'Replace The File
            fso.CopyFile SourceFile, DestinationFolder, True
            'Reapply The Read-Only Attribute
            fso.GetFile(DestinationFile).Attributes = fso.GetFile(DestinationFile).Attributes + 1
        End If
    Else
        'The File Does Not Exist In The Destination Folder.  Safe To Copy File To This Folder
        fso.CopyFile SourceFile, DestinationFolder, True
    End If
Set fso = Nothing

objXL.Workbooks.Open(DestinationFile)

The code is executed in Activeworkbook in workbook_Open event like that

Code: Select all

                    strCopyTo = ThisWorkbook.FullName
                    strCurrPath = ThisWorkbook.Path & "\"
                    strCopyFrom = dlPath & "\MyProject.xlsm"
                    Shell "wscript.exe """ & dlPath & "\Update.vbs""" & " """ & strCopyFrom & """ """ & strCopyTo & """ """ & strCurrPath & """"

It is supposed that the vbs code will copy the workbook named "MyProject.xlsm" from dlPath to the ActiveWorkbook path (activeworkbook with the same name MyProject) and replace the activeworkbook with the new one from dlPath

Is it possible to do that without the vbs file??

Simply I mean I need to copy and replace the existing opened workbook with a workbook from specific path

* The activeworkbook may be with a different name

Posted here too
https://chandoo.org/forum/threads/conve ... ook.42487/" onclick="window.open(this.href);return false;

[HansV]

Since you want to replace the workbook, you have to run some code from outside the workbook.

[YasserKhalil]

The problem with that approach is that the Windows Defender blocks the vbs file. Is there a way to enable such files?

[HansV]

I don't know, sorry.

[YasserKhalil]

Never mind my tutor .. I will keep searching and if I found a solution, I will post it here