Consolidate

Jirik61 · #1 06-28-2012, 08:21 AM

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

Code:

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 · #2 07-03-2012, 04:42 AM

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:

Code:

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 · #3 07-13-2012, 07:40 AM

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 · #4 07-13-2012, 01:57 PM

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 · #5 07-18-2012, 12:47 AM

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 · #6 08-06-2012, 06:08 AM

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 · #7 08-07-2012, 12:14 AM

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

Quote:

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

Jirik61 · #8 08-07-2012, 07:33 AM

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

Jirik

macropod · #9 08-07-2012, 07:55 PM

Hi Jirik,

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

Jirik61 · #10 08-08-2012, 04:10 AM

Hello Paul,

the line with command consolidate - see enclosed jpeg.

Jiri

macropod · #11 08-08-2012, 04:59 AM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Is there a portable version of MS Project 2007?	pstein	Project	1	03-26-2012 07:37 AM
Word 2010: new document opens as 2007 version	DigiCat	Word	0	02-29-2012 11:51 AM
This macros are not working in office english version 2007	hans	Excel Programming	4	12-10-2011 07:03 AM
problem opening document in word 2007 version	Lavanya	Word	1	05-28-2010 03:40 AM
Which Version of Office 2007 to Buy?	FauxAsian	Office	1	03-25-2010 09:10 PM