Hide all the tasks happening during the Macro Process!!

Hi Guys,

I want to hide the following tasks that happens in front of the Excel workbook:

1. Hide the file copying process window (from the unzip process) happening in front of the Excel workbook
(###Note: The window for copying process comes up sometimes and sometimes not).. Please find the sample screenshot for it below:
sample_copying_process.png
2. Hide the cmd prompt process window (from the .bat file) happening in front of the Excel workbook

How can we hide the above two tasks and somehow put it behind the workbook.

Part of my full code is given below:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
    (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As LongPtr
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
    (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Sub Open_Dialog()

'Disable Screen Updating and Events
Application.EnableEvents = False
Application.ScreenUpdating = False
    
    Dim fd As Office.FileDialog
    Dim sFolderName As String
    Dim DownloadFile$
    Dim URL$
    Dim LocalFilename$
    Dim done
    Dim ZipFolderAndFileName As Variant
    Dim FileNameFolder As Variant
    Dim FSO As Object
    Dim oApp As Object
    Dim aFile As String
    Dim txtFileName As String
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Use File Picker To Pick a File Name
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd

      .AllowMultiSelect = False

      ' Set the title of the dialog box.
      .Title = "Please select a file."

      ' Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Executable File", "*.exe"
      .Filters.Add "Word 97-2003 Doc File", "*.doc"
      .Filters.Add "Word Doc File", "*.docx"
      .Filters.Add "Text File", "*.txt"
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show <> -1 Then
        Exit Sub
      End If
      
      txtFileName = .SelectedItems(1) 'replace txtFileName with your textbox
    End With
      
    'Get the Folder Name from the file name (the file name itself is not used)
    'Append a trailing backslash to the Folder Name if needed
    sFolderName = LjmExtractPath(txtFileName)
    If Right(sFolderName, 1) <> "\" Then
      sFolderName = sFolderName & "\"
    End If
      
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Download the .zip file to the destination folder
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    DownloadFile$ = "pads_strings.zip"
    URL$ = "http://sagamusix.de/sample_collection/" & DownloadFile
    LocalFilename$ = sFolderName & DownloadFile
    done = URLDownloadToFile(0, URL, LocalFilename, 0, 0)
        
    If done = 0 Then
      'Do nothing
    Else
      MsgBox "Couldn't connect to the internet. Please check you internet connection!"
      Exit Sub
    End If

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Extract the files from the zip file to the Destination Folder
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    'Create the 'Variant' names required by oApp.Namespace
    FileNameFolder = sFolderName
    ZipFolderAndFileName = LocalFilename$
        
        
    Set oApp = CreateObject("Shell.Application")
    oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(ZipFolderAndFileName).items

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Delete the temporary files
    'Delete the downloaded .zip file
    'Clear object pointers
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
        
    aFile = LocalFilename
    If Len(Dir$(aFile)) > 0 Then
      Kill aFile
    End If
    
    Set fd = Nothing
    Set oApp = Nothing
    
    Dim Batch_File As String
    Batch_File = FreeFile()
    Open ThisWorkbook.path & "BatchFile.bat" For Output As #Batch_File
    Print #Batch_File, "cd "
    Print #Batch_File, "waitfor /t 5 simon"
    Close #Batch_File
    Batch_File = Shell(ThisWorkbook.path & "BatchFile.bat", vbMaximizedFocus)
    
'Disable Screen Updating and Events
Application.EnableEvents = True
Application.ScreenUpdating = True
        
End Sub

Public Function LjmExtractPath(sPathAndName As String)
  'This extracts the path with a trailing '\'
  
  LjmExtractPath = Left(sPathAndName, InStrRev(sPathAndName, "\"))

End Function

Actually cmd prompt process has lot of other tasks in the .bat file. I have only provided some of it.

I have tried using the code below but in vain..it doesn't hide only those two tasks mentioned above:

Application.EnableEvents = False
Application.ScreenUpdating = False
'my code
Application.ScreenUpdating = True
Application.EnableEvents = True

Attached is my .bat file. Please find the link below for it.
https://www.dropbox.com/s/mldghi7ss8...hFile.bat?dl=0

Thanks in advance guys.

Cheers
Plasma33

Good morning plasma33

So you are trying to download a file, using Excel, and then hand control over to a batch file to unzip it?
It's not going to be possible to hide the batch file window that pops up - you're going to have a hard time to suppress from Excel VBA something that Windows wants to do.

Why not unzip via VBA? Ron de Bruin is the authority on this type of ttechnique, and discusses a number of them here. The bad news is, if you unzip using the default Windows unzipper, you cannot get around the information window. If you can't put up with that you may have to unzip using 7-Zip (free) or WinZip (not free) - but Mr de Bruin will still show you how to do this from within VBA.

HTH

DominicB

Good Morning, DominicB

Thank you for your kind reply.

I will do what is necessary right away. Thank you for the link.

Cheers
Plasma33