Problem with the workbook_open() procedure.

[Alex St-Pierre]

Hello,
I have a problem with a macro. I have create a lot of
excel file which create an operation when you open each of
them for the first time. (with the use of Private Sub
workbook_open()). If you open any of them for the first
time, the operation (form) is create succesfully. I have
create a macro to open, save and close all of them but the
results is very bad because it doesn't produce the same
thing as if it was done one by one. The macro #1,2 and 3
(see below) are not execute until the end. I think vba is
jumping some step when it open the form and then, it save
and close the form without finishing the operation.

Sub macrofortheopening-closeofexcelfile-oneatthentime()
For(each excel file)
Workbooks.Open FileName:=path & "\" & file (see below)
ActiveWorkbook.Save
ActiveWorkbook.Close
Loop
End Sub

This is my procedure at the opening of each excel file:
Private Sub workbook_open()
If(if first time opening)
Macro #1
Macro #2
Macro #3
End If
endsub

 

[Steve Culhane [MS]]

Alex,
I would recommend directly calling your workbooks.
Like this....


Sub test()
Dim I As Integer
Dim CurrentWorkBook As Workbook
Dim MyFiles As FileSearch
Set MyFiles = Application.FileSearch

With Application.FileSearch
.LookIn = "c:\"
.FileType = msoFileTypeExcelWorkbooks
.Execute

For I = 1 To .FoundFiles.Count
Set CurrentWorkBook =
Application.Workbooks.Open(.FoundFiles.Item(I))
Macro1( CurrentWorkBook )
Macro2( CurrentWorkBook )
Macro3( CurrentWorkBook )

CurrentWorkBook.Save
CurrentWorkBook.Close
Next I
End With

End Sub

The WorkbookOpen is fired off on the workbook, but your macro is based on a
template, or addin.
You don't know if the macro's have fired off before you are closing the
workbook. So what I
would do is run the other macros from your main macro, passing it the
workbook you want to
manipulate. This way you know where you are in the process and can control
the saving and
closing of the workbook.

Try it out…

Stephen Culhane
(e-mail address removed)
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.





--------------------
| Content-Class: urn:content-classes:message
| From: "Alex St-Pierre" <[email protected]>
| Sender: "Alex St-Pierre" <[email protected]>
| Subject: Problem with the workbook_open() procedure.
| Date: Tue, 2 Sep 2003 07:30:42 -0700
| Lines: 29
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcNxXsoSiqC8+80DTXeQMJCt55Vy0Q==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.office.developer.vba
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.office.developer.vba:16766
| NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
| X-Tomcat-NG: microsoft.public.office.developer.vba
|
| Hello,
| I have a problem with a macro. I have create a lot of
| excel file which create an operation when you open each of
| them for the first time. (with the use of Private Sub
| workbook_open()). If you open any of them for the first
| time, the operation (form) is create succesfully. I have
| create a macro to open, save and close all of them but the
| results is very bad because it doesn't produce the same
| thing as if it was done one by one. The macro #1,2 and 3
| (see below) are not execute until the end. I think vba is
| jumping some step when it open the form and then, it save
| and close the form without finishing the operation.
|
| Sub macrofortheopening-closeofexcelfile-oneatthentime()
| For(each excel file)
| Workbooks.Open FileName:=path & "\" & file (see below)
| ActiveWorkbook.Save
| ActiveWorkbook.Close
| Loop
| End Sub
|
| This is my procedure at the opening of each excel file:
| Private Sub workbook_open()
| If(if first time opening)
| Macro #1
| Macro #2
| Macro #3
| End If
| endsub
|

 

[Alex St-Pierre]

Hi Stephen,
I have integrated all the programmation. But, when I
tried to start it, there is an error 450 with the
line "Macro1(CurrentWorkbook)" or "Macro2(... ) It works
if I put these 3 lines in commentary. Does it is because
it can't read the macro name in the workbook which is not
open?

Thank you

Alex

 

[Alex]

what was wrong is that I have to use
CurrentWorkbook.Macro1 to be able to use it.

 

[RedPepper]

Hi Alex,

Did you find a solution for you filesearch problems? I have the same:
never find a file! I use Windows 2000 Professional with Office 2002. If
you do, I would greatly appreciate to know what you finally did.

Thanks,

RP