VBA check if a new workbook is created

[Wicked_]

Hi.

Got a question.
Is there a way to create a VBA code, that constantly checks a specific folder, if a new workbook is created?
For it to be easy to understand, lets pretende workbook1 is called 1.xlsm (which im using), then if i create a book called 2.xlsm inside that same folder, i want to code to recognize it, and launch a specific userform inside book 1.

Is this possible ?

Thanks.

 

[nemmi69]

Can the code that saves the new book run your code? Otherwise how often do you want Excel to check the folder?

 

[Wicked_]

Can the code that saves the new book run your code? Otherwise how often do you want Excel to check the folder?

The code itself is not supposed to save a new book. Basically just "scan" for a new book with a spesific name. Example: Book1 got the code that scans for a new book. If i create a book2 in the same folder, it runs a userform in book1. If i then run book2, and create a book3, the userform will open in book2, and so on.

How often? everything from 1 sec to 10 min i guess is ok, it just checks the folder every now and then. (example every 1 minute, it checks if a new book with a spesific name is created)

 

[nemmi69]

This is the timer code you will need.

In workbook.open

'Call timer to check for user form
My_onTime

Timer check

Sub My_onTime()
    Application.OnTime Now + TimeValue("00:01:00"), "CheckForFile" 
[COLOR=#444444][FONT='inherit']End Sub
[/FONT][/COLOR]

Check for file

Sub CheckForFile()
   'What ever code you like

   'call timer again
   My_onTime
End Sub

Is this the sort of thing you were after?

 

[Wicked_]

This is the timer code you will need.

In workbook.open

'Call timer to check for user form
My_onTime

Timer check

Sub My_onTime()
    Application.OnTime Now + TimeValue("00:01:00"), "CheckForFile" 
[COLOR=#444444][FONT='inherit']End Sub
[/FONT][/COLOR]

Check for file

Sub CheckForFile()
   'What ever code you like

   'call timer again
   My_onTime
End Sub

Is this the sort of thing you were after?

Thanks for the timer check! Worked nicely.
Now i just need a way to basically check if "book X" exists in the CheckForFile sub.

The book where i got this macro will be copied every now and then, with a incremental new name (number (think version)), so i need the macro to check if (example: Book Y exist) then do a code, else if does not exist, do nothing, and it keeps looping and checking every 1 min.

 

[nemmi69]

Well a file exists function is

'######################################################
'Function FileExists checks if the file exists in
' the directory concerned.
'######################################################
Function FileExists(ByVal FileSpec As String) As Boolean
Dim Attr As Long
ErrMsg = FileSpec & " directory not found."


  ' Guard against bad FileSpec by ignoring errors
  ' retrieving its attributes.
  On Error Resume Next
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExists = Not ((Attr And vbDirectory) = vbDirectory)
    'error message if not found
    'If FileExists = False Then MsgBox ErrMsg, vbCritical, ThisWorkbook.Name
  End If
End Function

Dim CFF_name as string
Dim CFF_Found as boolean

Sub CheckForFile()
    If CFF_Found=[COLOR=#574123] FileExists([/COLOR]CFF_name) then
       'create new file
    End If

   'call timer again
   My_onTime
End Sub

 

[Wicked_]

Well a file exists function is

'######################################################
'Function FileExists checks if the file exists in
' the directory concerned.
'######################################################
Function FileExists(ByVal FileSpec As String) As Boolean
Dim Attr As Long
ErrMsg = FileSpec & " directory not found."


  ' Guard against bad FileSpec by ignoring errors
  ' retrieving its attributes.
  On Error Resume Next
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExists = Not ((Attr And vbDirectory) = vbDirectory)
    'error message if not found
    'If FileExists = False Then MsgBox ErrMsg, vbCritical, ThisWorkbook.Name
  End If
End Function

Dim CFF_name as string
Dim CFF_Found as boolean

Sub CheckForFile()
    If CFF_Found=[COLOR=#574123] FileExists([/COLOR]CFF_name) then
       'create new file
    End If

   'call timer again
   My_onTime
End Sub

Hmm, i feel like i'm doing something wrong. Got a error message that the macro does not exist or something when i run it.

I inserted both of those codes in "ThisWorkbook", and tried running it without edititing, and as well as changing the "CFF_name" in the CheckForFile, but same error.
Am i doing something wrong here ?

 

[nemmi69]

Hmm, i feel like i'm doing something wrong. Got a error message that the macro does not exist or something when i run it.

I inserted both of those codes in "ThisWorkbook", and tried running it without edititing, and as well as changing the "CFF_name" in the CheckForFile, but same error.
Am i doing something wrong here ?

The function and the sub go in a regular module not in "This workbook"

 

[Wicked_]

The function and the sub go in a regular module not in "This workbook"

Ah i see.

Ok, so now i got this

ThisWorkbook:

Private Sub Workbook_Open()


Worksheets("Velg").Activate


My_onTime
End Sub


Sub My_onTime()


Application.OnTime Now + TimeValue("00:00:05"), "CheckForFile"
End Sub


and in module22:




'######################################################
'Function FileExists checks if the file exists in
' the directory concerned.
'######################################################
Function FileExists(ByVal FileSpec As String) As Boolean
Dim Attr As Long
ErrMsg = FileSpec & " directory not found."




' Guard against bad FileSpec by ignoring errors
' retrieving its attributes.
On Error Resume Next
Attr = GetAttr(FileSpec)
If Err.Number = 0 Then
' No error, so something was found.
' If Directory attribute set, then not a file.
FileExists = Not ((Attr And vbDirectory) = vbDirectory)
'error message if not found
'If FileExists = False Then MsgBox ErrMsg, vbCritical, ThisWorkbook.Name
End If
End Function












Sub CheckForFile()


Dim CFF_name As String
Dim CFF_Found As Boolean
If CFF_Found = FileExists(TestBook2) Then
'create new file
UserForm7.Show

End If


'call timer again
My_onTime
End Sub


When i run the code from the workbook, it waits 5 sec, then gives the error "Sub or Function not defined on "My_onTime" in module22

What do i do wrong here ?

 

[nemmi69]

Sub My_onTime needs to be in module22 not ThisWorkbook as well