drdrew1469
New Member
- Joined
- May 5, 2002
- Messages
- 5
I have created the following macro to retrieve data from many excel spreadsheets (all the same layout). My problem is that when it retrieves the data, creates a link and puts the value in the active cellrange, it doesn't account for blank cells and jambs them all together, i.e. if retreiving 5 spreadsheets, it uses A1 - A3 instead of A1=#, A2=#, A3=blank, A4=blank, A5=#. Thant means I have to check the path (links) across my "consolidated data" to make sure the cells line up and if they don't, it's a mess to re-arrange all those cells. Any help is greatly appreciated.
Thanks,
Drew
With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlLeft
End With
Range("A2").Select
Selection.Consolidate Sources:="'C:Path[*.xls]Sheet1'!R3C3", Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True
Range("B2").Select
Selection.Consolidate Sources:="'C:Path[*.xls]Sheet1'!R5C2", Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True
Range("C2").Select
Selection.Consolidate Sources:="'C:Path[*.xls]Sheet1'!R18C9", Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True
Selection.ClearOutline
Range("A1").Select
End Sub
This message was edited by drdrew1469 on 2002-05-06 09:12
Thanks,
Drew
With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlLeft
End With
Range("A2").Select
Selection.Consolidate Sources:="'C:Path[*.xls]Sheet1'!R3C3", Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True
Range("B2").Select
Selection.Consolidate Sources:="'C:Path[*.xls]Sheet1'!R5C2", Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True
Range("C2").Select
Selection.Consolidate Sources:="'C:Path[*.xls]Sheet1'!R18C9", Function:=xlCount _
, TopRow:=False, LeftColumn:=False, CreateLinks:=True
Selection.ClearOutline
Range("A1").Select
End Sub
This message was edited by drdrew1469 on 2002-05-06 09:12