Stopping a userform from opening when workbook is opened.

[UnsolvedCoding]

Hey all -

I have a list of several thousand workbooks to scan. Enough of them have userforms popping up when the workbook is opened its annoying.

Is there a way to override the Workbook_Open code that forces the userform to open?

I would like to open the workbooks, scan them and close them without having to sit here and click the close button for each workbook.

Anyone know how to do an override?

 

[PHV]

Change the VBA security setting ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[N1GHTEYES]

Do you actually have to open the workbooks? Or do you just want to scan them for info?

You can extract info from closed workbooks. There are loads of examples out there. Here is one:

http://www.exceltip.com/st/Read_inf...orkbook_using_VBA_in_Microsoft_Excel/473.html

Does that help?

Tony

 

[UnsolvedCoding]

If scanning the workbook wihtout opening it can pull info from the worksheets and tab names that would be much easier. I haven't done that before.

Todays batch of files is 451, yesterday was over 2800. Our main program opens the files and looks for information, renames tabs as needed and creates paths to various tabs and various workbooks.

My part of this process is to go over the files that can't be identified and look for workbooks in a different format. So far the only commanality is workbooks that contain the word Entry in one of the tabs and thats mostly true but not always.

Once the workbooks are identified then the tabs with the word entry need to be scanned, info gathered and if required the tab renamed. After this I complie the info into the pre-set string, finish creating the hyperlinks and load them into the database. And yes, the process needs to be overhauled.

So this isn't just a problem of stopping userforms, but that is one of the biggest areas of time consumption.

Given what was just mentioned in the previous post, scanning the workbook without opening it and rename tabs would save lots of time.

 

[N1GHTEYES]

Given that the process for extracting info from a closed workbook uses an Excel4macro, I suspect you probably can't do everything you want to to the closed books. However, you might be able to avoid unnecessarily opening a the ones which don't need changing.

However, I have to say that, whilst I am aware of the technique I suggested, I've never used it. So I can't say what the limitations will be.

Tony

 

[UnsolvedCoding]

PHV -

What do you mean by change the security setting?

 

[UnsolvedCoding]

What I ended up doing was this

If InStr(UCase(sTab), "ENTRY") > 0 Then

ThisWorkbook.Sheets("Complied").Cells(I, "A").Interior.Color = 65535
ThisWorkbook.Sheets("Complied").Cells(I, "h").Value = "X"

Exit For

End If

Where sTab is the name of the active worksheet.

This loops through each tab of the work book and if it finds the word Entry in any tab colors the corrosponding cell yellow, puts an X as in indicator in column H and exits the for statment.

Since some of these work books have over 50 pages this saves a lot of time.

That combined with application.screenupdating = False made things go faster. I just ran through 3200 files in 25 minutes.

 

[N1GHTEYES]

So you did open each workbook then?

How did that avoid the problem of forms which open when the workbook is opened?

Tony

 

[UnsolvedCoding]

Yes I had to open the workbooks and it didn't avoid the forms problem.

Basically I sat by the computer and hit enter a hundred times when the workbooks opened.

There are lots of things out there about getting information from cells on closed workbooks but I didnt' see anything about scan tabs of closed workbooks.