1

I have a vba macro to open excels and read data. 

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
Set excelworkbook = xlApp.Workbooks.Open(file_path, ReadOnly:=True, notify:=False)

And error "Too many different cell formats" occurred on the third line. I assume it's because of cell formats(fonts, borders, etc.) and does anyone know how to resolve this issue programmatically? No error is raised when I try to open the excel manually. Any advice would be appreciated.

bananaLeaf
  • 37
  • 1
  • 6

1 Answers1

0

Instead of your code, try doing it this way:

Dim excelWorkbook As Excel.Workbook
Set excelWorkbook = Excel.Workbooks.Open(file_path, ReadOnly:=True, Notify:=False)

If it still gives you trouble, maybe adding these will help:

Application.DisplayAlerts = False
' Application.ScreenUpdating = False ' Optional, might not be necessary.

Just remember to set them back to their defaults afterwards.

djikay
  • 10,450
  • 8
  • 41
  • 52
  • Hi, I've set displayAlerts to false and still got the same error. Any suggestions? – bananaLeaf Jul 14 '14 at 19:40
  • Even with the code I've posted? Hmm... try without the `ReadOnly` and `Notify` arguments to see if it makes any difference. Other than that, no idea I'm afraid. I've used the above code for my own projects many times without any problems, so I'm not sure what's different with your workbook. – djikay Jul 14 '14 at 20:06
  • 1
    @bananaLeaf: You may have seen this but, if not, [this SO question](http://stackoverflow.com/questions/2449755/too-many-different-cell-formats) seems to be related and has a number of potential solutions to try. – djikay Jul 14 '14 at 23:46
  • I tried without readonly and notify arguments but still no luck. The file I have problem with has only 3 spreadsheets but each has multi-row headers with background color and filter and many different cell formats. I've seen the post but my project is based on ms access. My code couldn't even open the file, let alone change the formats programmatically. – bananaLeaf Jul 15 '14 at 12:30
  • This may also be caused by opening file in excel 2003 by default. Do you know how to set 2010 as default using vba? thanks a lot – bananaLeaf Jul 15 '14 at 13:03
  • @bananaLeaf: Well, Excel 2003 is certainly different to Excel 2010 in terms of styles. Using VBA, I don't know how to set the default association. But if you go to the Control Panel>Programs and Features>, locate MS Office 2010 and do a repair install on it, then the file associations will be updated to Office 2010 by default. Windows updates will almost certainly mess those up again, so you need to do that periodically to make sure the file associations remain as they should be. – djikay Jul 15 '14 at 13:10
  • As @djikay linked and talked about my question has an answer that will delete unused styles, so maybe you could use that and then it will open fine going forward? Hope this is helpful... – Craig Jul 17 '14 at 22:23