Consolidate - does not work in 2007 version

[Jirik61]

I used a procedure to consolidate data from other excel files in the same directory. It ran under Excel2003, but in 2007 it does not - the command selection.consolidate ends with Run-time error '1004'. I spent hours to find any hint, but found nothing. Do you have any experience?

Jirik

Sub Konsolidace()
Sheets("Výsledovka").Select
ActiveSheet.Unprotect
Cesta = Application.Workbooks(ActiveWorkbook.Name).Path
Obd = Range("a1")
Range("e13").Select
Selection.Consolidate Sources:=Array( _
  Cesta + "\[F12_K00_Regiony.xlsm]Výsledovka!R13C5:R119C18", _
  Cesta + "\[F12-105151.xlsx]Výsledovka!R13C5:R119C18", _
  Cesta + "\[F12-120100.xlsx]Výsledovka!R13C5:R119C18", _
  Cesta + "\[F12_K_HQ.xlsm]Výsledovka!R13C5:R119C18"), _
  Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
Range("e164").Select
Selection.Consolidate Sources:=Array( _
  Cesta + "\[F12_K00_Regiony.xlsm]Výsledovka!R164C5:R164C18", _
  Cesta + "\[F12-105151.xlsx]Výsledovka!R164C5:R164C18", _
  Cesta + "\[F12-120100.xlsx]Výsledovka!R164C5:R164C18", _
  Cesta + "\[F12_K_HQ.xlsm]Výsledovka!R164C5:R164C18"), _
  Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
ActiveSheet.Protect
 
Range("a1").Select
End Sub

[macropod]

hi Jirik,

Are you sure the source files are where they should be? Do they have the correct names and extensions? Have the source files been converted to the xls(x/m) formats?

FWIW, I believe your code could be made simpler and more efficient:

Sub Konsolidace()
Dim Cesta As String
Cesta = ThisWorkbook.Path
With Sheets("Výsledovka")
  .Unprotect
  .Range("E13").Consolidate Sources:=Array( _
    Cesta & "\[F12_K00_Regiony.xlsm]Výsledovka!R13C5:R119C18", _
    Cesta & "\[F12-105151.xlsx]Výsledovka!R13C5:R119C18", _
    Cesta & "\[F12-120100.xlsx]Výsledovka!R13C5:R119C18", _
    Cesta & "\[F12_K_HQ.xlsm]Výsledovka!R13C5:R119C18"), _
    Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
  .Range("E164").Consolidate Sources:=Array( _
    Cesta & "\[F12_K00_Regiony.xlsm]Výsledovka!R164C5:R164C18", _
    Cesta & "\[F12-105151.xlsx]Výsledovka!R164C5:R164C18", _
    Cesta & "\[F12-120100.xlsx]Výsledovka!R164C5:R164C18", _
    Cesta & "\[F12_K_HQ.xlsm]Výsledovka!R164C5:R164C18"), _
    Function:=xlSum, TopRow:=False, LeftColumn:=False, CreateLinks:=False
  .Protect
End With
End Sub

[Jirik61]

Hi Paul,

I made a special example for you - I left only 2 files for consolidation, each file contains only one sheet. Enclosed you will find 3 files in version 2003, and 3 files in version 2007. You can run macro inside the main file : the version 2003 works, 2007 not.
I will appreciate any idea, any hint...

Jiri

P.S. my code is not optimized, but I will use your corrections with pleasue when the main topic works...

[macropod]

It works OK with your xlsx workbooks (I use Office 2010) - I haven't tried with the xls workbooks. Are you sure your Office 2007 is fully updated? Have you tried repairing Office 2007 (Excel Options|Resources|Diagnose)?

[Jirik61]

Hi Paul,

Thanks for your tip, I passed the diagnose procedure, there was one item wrong and it was solved (by the diagnose utility), but it did not help.
In any case I will upgrade to 2010 version next week, I will inform you about the situation after it.

Jirik

[Jirik61]

Hi Paul,

I have upgraded to 2010 version, but there is no change - the macro stops at command consolidate - the error message is enclosed in jpg. Do you have any hint what to test now? I tried to find a similar repair procedure in Exc2010 (like you recommend for 2007), but I found none

Jirik

[macropod]

As I said, the code worked fine for me. Also as per my first post:

Are you sure the source files are where they should be?

[Jirik61]

Unfortunately yes, they are all in the same directory...

Jirik

[macropod]

Hi Jirik,

If you press Debug when the error occurs, which code line is highlighted?

[Jirik61]

Hello Paul,

the line with command consolidate - see enclosed jpeg.

Jiri

[macropod]

Curious. The code works fine for me. I'd be surprised if repairing Excel would change anything, but you can do this via 'Programs & Features' in the Windows Control Panel. The only other thing I can think of is that a 3rd-party addin that you might be running is interfering with the code's execution.