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.

 

[Wicked_]

Sub My_onTime needs to be in module22 not ThisWorkbook as well

Ok, moved it to module22:

Sub My_onTime()
My_onTime
End Sub


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


Then i get a error:
Out of stack space (not familiar with that error)

at the sub my_ontime i put there.
It opened the userform, which worked great, but also, if i renamed the testbook2 to something else, it will still open the userform, even if it does not match the CFF_Found.

 

[nemmi69]

The Sub My_onTime is wrong, should be -

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

Is TestBook2 in FileExists(TestBook2) a variable or a fixed string? If a fixed string it should be in quotes.

 

[Wicked_]

The Sub My_onTime is wrong, should be -

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

Is TestBook2 in FileExists(TestBook2) a variable or a fixed string? If a fixed string it should be in quotes.

The workbooks that im going to enter there is going to be a fixed string.
I updated the code, and i get no more errors! yay.

Still tho, it shows the userform every 1 min, even if i use quotes or not. (also tried adding .xlsm after the name, with no luck)

 

[nemmi69]

Did you put the quotes round the name?

 

[Wicked_]

Did you put the quotes round the name?

Yeah.

If CFF_Found = FileExists("TestBook2") Then

 

[nemmi69]

Hmmm well to start with - I am an idiot so the use of FileExists is

If FileExists("C:\Temp\TestBook2.txt") Then

Note : It also includes the directory path

 

[Wicked_]

Hmmm well to start with - I am an idiot so the use of FileExists is

If FileExists("C:\Temp\TestBook2.txt") Then

Note : It also includes the directory path

Haha no worries mate :P

That worked, but in a weird way.
If i say:

If FileExist("C:\Temp\TestBook2.xlsm") Then

Then the userform wont show, but if i edit the file itself to example TestBook3, then it launches fine.
Its like If FileExist looks if it does not exist?

And the last "problem" (i swear :P )
I want it to autorun when the workbook is launched.
I tried:

Private Sub Workbook_Initialize()
My_onTime (and some other ways to write it). but it wont autorun (shouldent that be inside Initialize?)

 

[nemmi69]

Did you put this in ThisWorkbook?

Private Sub Workbook_Open()
 CheckForFile
End Sub

 

[Wicked_]

Did you put this in ThisWorkbook?

Private Sub Workbook_Open()
 CheckForFile
End Sub

/facepalm...

Thanks :P that worked like a charm.

Know if its any logical explanation why it wont open the userform if it actually finds the correct xlsm file? It all works perfect, except that it opens the form when it cant find the file i wrote in fileexist. If i change the book name to something else, it works, but if it got the correct name, it will ignore it.

 

[nemmi69]

Not sure, I used this as a test.

Sub Testfile()
If FileExists("C:\Temp\TestBook2.txt") Then
    MsgBox "Found"
Else
    MsgBox "Nope"
End If
End Sub