EXCEL Won't Open files with Activex anymore 1

[Hooch2000]

I have created several EXCEL files with extensive VBA code and ActiveX controls from the Controls toolbox. I have installed them all on several office computers and all were working very well. I recently created another file which worked fine until the last change I made. Now it will not load at all (Not Responding - on Win XP and Application Error, Close or Ignore on Win 98). In addition, as soon as I loaded this new program on the office PCs, they will not only not run this program but will not run any of the other xls files that used to work fine. The common demominator appears to be ActiveX controls. If I take an archived file and replace ActiveX buttons with Forms controls, the programs go back to operating just fine. The problem is, I need to get to the files on the office computers as they have embedded client data on them. Any way to reset the way Excel treats ActiveX files? I desparately need to fix the problem with the office computers. Any help would be appreciated.

 

[asjeff]

Try opening the files with the shift key held down - this will prevent any macros etc from executing. You should then be able to remove the last change you made or play with the the projects references until you find out the answer to your problem.

This is only a thought and it might not work - but it would be my first action

Hope it helps....

Asjeff

 

[RobBroekhuis]

If Asjeff's approach doesn't work, there are other methods for retrieving worksheet data from corrupted workbooks. Let us know if you need more help.
Rob

 

[Hooch2000]

asjeff, Thank you for the tip. I was unaware of the shift key trick. I think I may have gotten myself into a little bit deeper problem unfortuately. The shift key did not enable me to open the file. I'm concerned that I messed up EXCEL in general. Even if i take the affected file off of the computers, I still can not run any other files that used to work fine. I would like to try the other options to salvage as much of the file as I can.

I also want to be able to remedy the problem with the office PC so that it can run other files with ActiveX controls in them.

Any continued help on recovering the file would be appreciated. Thank you very much for taking the time to reply to my problem.

Thank You

 

[asjeff]

Hmmm - this sounds a bit of a conundrum. Possibly my next step would be to do an Office repair on one of your machines to see if this fixes the problem. Failing that you're looking at a complete reinstall, but this sounds a bit drastic in the first instance.

Rob - Have you any ideas? Could it be to do with the version of Excel that it was originally created in?

 

[RobBroekhuis]

I agree that - first things first - the Excel needs to be repaired before anything else. When I've had corrupted workbooks that I cared quite a bit about (stood to lose a lot of work), I've taken a few approaches to retrieving the individual parts of the workbook (sheets, VBA modules) and putting them back together again. Still a good bit of work, but beats starting over from scratch...
If you need to go there, please ask.
Rob

 

[Hooch2000]

Thanks again for the quick response. I already tried a reinstall of office on one computer. I still get the same results when I try to open this file. I have since narrowed it to ACTIVEX specifically. Once I run my file on any PC, that PC is no longer able to run any other EXCEL files that contain ACTIVEX controls. If I wait long enough, small files with very few ACTIVEX controls will open. I then proceed to replace the ACTIVEX control with the Forms Controls and resave and this eliminates the problem for that particular file.

It is the larger files (more time invested) that I can not open at all in order to replace ACTIVEX with Forms controls.

I have since found a way to import raw unformatted data from each of the sheets in the affected workbook but I will have to reformat each page. Better than nothing!!.

I I ever find out whatis causing the ACTIVEX files errors, I will be sure to report it on this handy Tech-Tips Forum. Thanks again for all of your attention and consideration.

By the way, the recovery of data involved the following steps:

1) Open EXCEL
2) Open a New Workbook
3)in Cell A1, Enter =Filename!A1 (where file name is the affected file to be recovered)
4) This brings up a dialog box to let you select the sheet from the affected file.
5) Cell A! of the selected sheet is copied to the new book.
6) Simply copy cell A1 to the bottom right corner of affected sheet boundries if you can remember where it was.

Thanks again.

Frank MArtin

 

[MatthewGB]

Frank,

I have experienced exactly the same problem as you. I am running Win2000, Office2000. On my computer, any excel files containing ActiveX Objects take about 2 secs to open per control (from the control toolbox) in the workbook.

I have tried reinstalling office from scratch, but to no avail. The only other option left is to reformat the drive, but that would be too much trouble than it is worth.

The only difference I have to your problem is that running my excel file on other computers does not make the problem occur on that computer. Only the computer on which i created the program seems to be affected.

This lengthy dialogue does not help you at all but I thought I should let you know that this problem has and still is occuring for me also. If you do by any chance come across a solution (apart from formatting) please let me know on this thread or forum.

Cheers

Matt

 

[RobBroekhuis]

I don't remember where I got this code (which I have since slightly modified), but it's been a lifesaver a few times. Recovers both worksheets (in fully formatted and unformatted form, to ensure capture of cells containing more than 255 characters) and VBA modules. Paste the code into a Word VBA module. The comments up top provide more instructions. Set the recovery boolean variables appropriately before running.

Option Explicit

' Change the following file names in the code to something you want.
' The first ("XL.Workbooks.Open FileName:=" in the code) is the path
' and file name of the corrupted spreadsheet file.
' The second ("C:\temp\vbe_" in this example) includes the path and
' folder to put the exported contents of the corrupted spreadsheet.
'
' Establish a reference to the Microsoft Excel 9.0 Object Library: Tools,
' References, check the box next to this entry.
' Execute the code (cursor in module; press F5).
' if you get an File Open error message, click Debug, then Continue.
' When the code has executed, look in the Export File Name you indicated.
' You'll see a bunch of *.txt files.
' Open a new Excel spreadsheet.
' File/Import each of these txt files.
' Excel will rename the txt files to the original module name, although you
' may have to copy the contents of ThisWorkbook from a class module into
' the actual ThisWorkbook folder.
' Save the renewed spreadsheet. You're done!

Sub Recover_Excel_VBA_modules()

Dim XL As Excel.Application
Dim XLVBE As Object
Dim wb As Workbook
Dim sh As Worksheet
Dim i As Integer, j As Integer, k As Integer
Dim fname As String, s As String
Dim RecoverSheets As Boolean, RecoverModules As Boolean

'User input to define process below

fname = "c:\windows\desktop\publication catalog"
RecoverSheets = True
RecoverModules = False

Set XL = New Excel.Application
XL.Workbooks.Open FileName:=fname + ".xls"

If Not RecoverSheets Then GoTo GetModules
Set wb = XL.Workbooks.Add
If XL.Workbooks.Count > 1 Then
'successfully created second workbook, retrieve sheets into it
XL.Workbooks(1).Activate
For i = 1 To XL.Worksheets.Count
If i > 1 Then wb.Sheets.Add
Debug.Print XL.Sheets(i).Name
wb.ActiveSheet.Name = XL.Sheets(i).Name + " (unfmt)"
XL.Sheets(i).UsedRange.Copy wb.ActiveSheet.Range("A1&quot
XL.Sheets(i).Copy After:=wb.Sheets(wb.ActiveSheet.Name)
Workbooks(1).Activate
Next i
wb.SaveAs FileName:=fname & " - sheets.xls"
wb.Close
Else
For i = 1 To XL.Worksheets.Count
Set sh = XL.Sheets(i)
Debug.Print "Restoring " & sh.Name & " (" & sh.UsedRange.Rows.Count & " rows)"
Open "e:\recover\" & sh.Name & ".csv" For Output As #1
For j = 1 To sh.UsedRange.Rows.Count
s = """" & sh.Cells(j, 1) & """"
For k = 2 To sh.UsedRange.Columns.Count
s = s & ",""" & sh.Cells(j, k) & """"
Next k
Print #1, s
Next j
Close #1
Next i
End If
If Not RecoverModules Then GoTo SubDone

GetModules:
Set XLVBE = XL.VBE
j = XLVBE.VBProjects(1).VBComponents.Count
For i = 1 To j
Debug.Print XLVBE.VBProjects(1).VBComponents(i).Name
XLVBE.VBProjects(1).VBComponents(i).Export _
FileName:="e:\recover\" & _
XLVBE.VBProjects(1).VBComponents(i).Name & ".txt"
Next

SubDone:
XL.Quit
Set XL = Nothing
End Sub

Rob

 

[Hooch2000]

Wow, What a tech-tips line. I did not expect this level of response.

I have since recovered as much of my file as possible and I replaced all of the ActiveX controls with forms controls. Not only is the file working flawlessly now but the file size is roughly 1/3 of the previous file size. THere are a few disadvantages in using Forms controls relative to activX but mainly in formatting. The control I will miss most in my applications is the Calendar control. Anyone know of an equivalent Calendar control for Forms Controls?

Also, I see that I am not the only one with this problem. MAtthewGB is experiencing similar problems although they do not appear to affect other PCs.

I will continue to look for a resolution to fix EXCEL or Windows or whatever is causing the Activex problems and I will be sure to update all of you. Who knows, maybe Microsoft will actually do something with the error report that they had me send (Windows XP). I'm not holding my breath on that option.

Thanks again for all of your help folks.

Frank (Hooch2000)