Run macro only after file exist

vostroxe

New Member
Joined
Jul 13, 2018
Messages
29
How do I ensure macro only can be executed upon the Book2 is open? The VBS code below will help to auto open the file upon data extraction but it will be delay a bit to open hence resulted error at 100. On Error function seems doesnt work. Im thinking is there any loop function to wait until the file is exist?

Code:
Sub Macro1()

With VBS data


VBS code 'This VBS will download data and extracted into Book2.'


End With


100
    Windows("Book2.xlsx").Activate


    On Error GoTo 100
    
    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("T:T").Select
    Selection.TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Range("A:T").Select
    Selection.Copy
    Windows(Dir(ThisWorkbook.FullName)).Activate
    Sheets("Book2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.DisplayAlerts = False
    Workbooks("Book2.xlsx").Close savechanges:=False
    Application.DisplayAlerts = True


End Sub
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
You could put in a simple delay of 3 seconds like this - amend to match delay required

Code:
<code>Application.Wait(Now + TimeValue("00:00:[COLOR=#ff0000]03[/COLOR]"))</code>
 

vostroxe

New Member
Joined
Jul 13, 2018
Messages
29
the timing is different every time depending on the volume of the data. Hence delay will not work for the file.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
What is the longest delay you expect for a big file? 15 seconds?
- to avoid VBA error use that until you get a better soution
Code:
<code>Application.Wait(Now + TimeValue("00:00:[COLOR=#ff0000]15[/COLOR]"))</code>
 

vostroxe

New Member
Joined
Jul 13, 2018
Messages
29

ADVERTISEMENT

i cant confirm on the timing. I got more than 150 files to be extract in the same condition. Every file of it got different time.
 

vostroxe

New Member
Joined
Jul 13, 2018
Messages
29

ADVERTISEMENT

Im thinking whether it possible to use below code and combine with "loop" or "Do-Until" function until the Book2 is open? but i have no idea how to write the codes.

Code:
[COLOR=#333333]Application.Wait(Now + TimeValue("00:00:[/COLOR][COLOR=#ff0000]05[/COLOR][COLOR=#333333]"))[/COLOR]
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
As requested, here is a way to do what you want....
(using the method and function @Teeroy suggested in post#6)

- a time check to break out if the file doesn't open with 15 minutes for some reason
YOU MUST include this otherwise the loop will never stop if the file is not found - choose your own time limit

- 5 second wait before checking again if file is open
- 1 minute wait (once) after verifying that the file is open

Amend both waiting times so that it works for you
- the values must be tailored to whatever makes sense on your system with your files.

Amend the path and file name

Use the function like this ....

Code:
Sub TestFileOpened()
    Const fname = "[COLOR=#000080]C:\FullPath\ToFileName.xls[/COLOR]"
    Dim StartTime:  StartTime = Now
    
    Do Until IsFileOpen(fname)
        If Now - StartTime > [COLOR=#ff0000]15[/COLOR] / (60 * 24) Then
            MsgBox "Bored of waiting for file to open"
            End
        End If
        Application.Wait (Now + TimeValue("00:00:[COLOR=#006400]05[/COLOR]")) ' wait 5 seconds to see if file is open
    Loop
        Application.Wait (Now + TimeValue("00:[COLOR=#006400]01[/COLOR]:00"))  'wait 1 minute (once)
        MsgBox "carry on with rest of macro"
End Sub

The function must be placed in a standard module
Code:
Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer
    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=filenum"]#filenum[/URL] 
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.
    Select Case errnum
        Case 0
         IsFileOpen = False
        Case 70
            IsFileOpen = True
        Case Else
           Error errnum
    End Select
End Function
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,502
Messages
5,602,037
Members
414,496
Latest member
Nishnoshnish

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top