Open_explorer|wait|run subfunction/rest of macro

[Karthik Thandapani]

Hi,

I have a requirement where a macro button opens up the file explorer(activity1). From the file explorer, I manually select the intended file and open it(activity2). Since the excel file is considerably huge, it will take it's own time to open up. Here I require a code snippet to pause/hold the remaining macro code and start running it once the sheet is open & active.

Can anyone please help to get this done. Presently I have the below code;

Option Explicit
Sub Open_Explorer()
    Dim stFolder As String
   
    stFolder = "C:\Users\v-kart\Documents"
   
    If Len(Dir(stFolder, vbDirectory)) <> 0 Then
        Shell "Explorer.exe /n,/e," & stFolder, vbNormalFocus
    Else
        MsgBox "The directory does not exist!", vbCritical
    End If
   
   
   
   
With ActiveSheet
    Sheet1.Cells.Clear

'other macro to call

End With

   
End Sub

 

[Deepak]

In a first approach you could try something like as below.

Option Explicit
Sub Open_Explorer()
    Dim stFolder As String
   
    stFolder = "C:\Users\v-kart\Documents"
   
    If Len(Dir(stFolder, vbDirectory)) <> 0 Then
        Shell "Explorer.exe /n,/e," & stFolder, vbNormalFocus
    Else
        MsgBox "The directory does not exist!", vbCritical
    End If
   
 Application.Wait "00:00:10" ' Wait time 10 seconds

With ActiveSheet
    Sheet1.Cells.Clear

'other macro to call
End With
End Sub

or

Option Explicit
Sub Open_Explorer()
    Dim stFolder As String, Start As Long
   
    stFolder = "C:\Users\v-kart\Documents"
   
    If Len(Dir(stFolder, vbDirectory)) <> 0 Then
        Shell "Explorer.exe /n,/e," & stFolder, vbNormalFocus
    Else
        MsgBox "The directory does not exist!", vbCritical
    End If
   
Start = Timer
Do While Timer < Start + 10 'pause of 10 seconds
       DoEvents
Loop

With ActiveSheet
    Sheet1.Cells.Clear
'other macro to call
End With
End Sub

 

[Debraj]

Hi Karthik Thandapani
there is lots other way to open an excel file.. by manual click..

try to search for Application.getopenfilename.. and set the opened sheet as TargetWorkbook..

 set targetworkbook = application.getopenfilename(....)

It will only goes to next line of code.. after completely open selected file..

Give us some code from "OtherMacro" if you are facing any issue to adapt it..