Hello,
I got Excel file with data taht I should migrate into SQL Server database.
The problem is that it contains some cells that are merged. I tried to create
SSIS using Excel Source but if multiple cells (in a row) are merged together,
it returns value only for the first cell and for the rest the returned value
is null.
E.g. when A1='x', A2='y' and B1 is merged with B2 and contains value 5, then
for area A1:B2 I get rows:
x, 5
y, null

This is not what I would expected as from my point of view B2 should refere
to the same value as B1.
The file is quite big and changing frequently. I need some preprocessing or
other way how to automatically retrieve the same value for all merged cells.

Any idea?
(I can do programming but I'm not much familiar with VBA and Excel DOM so
please don't be too brief in the response.)

Thanks a lot
eXavier

Hello eXavier,

From your post, my understanding on this issue is: you want to know how to
unmerge the merged ranges in Excel worksheet and divide its value into each
cell. If I'm off base, please feel free to let me know.

As you see, if we unmerge a merged range, all the cells except the
left-upper corner one will be set to null by design. In order to divide the
value to each cell, we need to run a preprocess VBA macro or Office
automation client. Below is a VBA macro I write for you. You may open the
Excel VBA editor (alt+F11) and copy the code into Sheet1. Then run it by
clicking F5.

Sub UnMerge_Cells()
Dim currentCell As Range
Dim mergeArea As Range
Dim mergeValue As Variant
Dim mergeCell As Range

For Each currentCell In Me.UsedRange
If currentCell.MergeCells Then
Set mergeArea = currentCell.mergeArea
mergeValue = mergeArea.Cells(1, 1).Value2
' split the area
mergeArea.UnMerge
For Each mergeCell In mergeArea
mergeCell.Value2 = mergeValue
Next mergeCell
End If
Next currentCell
End Sub

Here is some explanations of the code: I scan each cell in the used range
of the current worksheet by using the code "For Each currentCell In
Me.UsedRange". currentCell refers to the current cell to be scanned.
MegeCells property of Range
(http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx)
indicate if the specified range contains only merged cells. If it is true
(the currentCell is within a merged range), then I get the merge range with
the property MergeArea. MergeArea
(http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx) returns
a range object that represents the merged range containing any part of the
specified range. In the meantime, I store the value of the current merged
range into a variant 'mergeValue': mergeValue = mergeArea.Cells(1,
1).Value2. Then I unmerge the range, iterate each cell in it and set the
value to be mergeValue. If the currentCell is not within a merged range, it
goes to the next loop.

Please try the macro and see if it fits your situation. If you do not plan
to use a VBA macro, but an Office automation client application, please
refer to the KB http://support.microsoft.com/kb/302084 if you are using C#.
In the automation client, we need to programmatically open the worksheet
first (with Application.WorkBooks.Open metho), then change the above VBA
code to C# by following the same logic process. If you encounter any
problem with it, please feel free to let me know. I will do my best to help
you.

Sincerely,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Jialiang,
Perfect, that's exactly what I needed.

Thanks!
eXavier

"Jialiang Ge [MSFT]" wrote:

Hello eXavier,

From your post, my understanding on this issue is: you want to know how to
unmerge the merged ranges in Excel worksheet and divide its value into each
cell. If I'm off base, please feel free to let me know.

As you see, if we unmerge a merged range, all the cells except the
left-upper corner one will be set to null by design. In order to divide the
value to each cell, we need to run a preprocess VBA macro or Office
automation client. Below is a VBA macro I write for you. You may open the
Excel VBA editor (alt+F11) and copy the code into Sheet1. Then run it by
clicking F5.

Sub UnMerge_Cells()
Dim currentCell As Range
Dim mergeArea As Range
Dim mergeValue As Variant
Dim mergeCell As Range

For Each currentCell In Me.UsedRange
If currentCell.MergeCells Then
Set mergeArea = currentCell.mergeArea
mergeValue = mergeArea.Cells(1, 1).Value2
' split the area
mergeArea.UnMerge
For Each mergeCell In mergeArea
mergeCell.Value2 = mergeValue
Next mergeCell
End If
Next currentCell
End Sub

Here is some explanations of the code: I scan each cell in the used range
of the current worksheet by using the code "For Each currentCell In
Me.UsedRange". currentCell refers to the current cell to be scanned.
MegeCells property of Range
(http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx)
indicate if the specified range contains only merged cells. If it is true
(the currentCell is within a merged range), then I get the merge range with
the property MergeArea. MergeArea
(http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx) returns
a range object that represents the merged range containing any part of the
specified range. In the meantime, I store the value of the current merged
range into a variant 'mergeValue': mergeValue = mergeArea.Cells(1,
1).Value2. Then I unmerge the range, iterate each cell in it and set the
value to be mergeValue. If the currentCell is not within a merged range, it
goes to the next loop.

Please try the macro and see if it fits your situation. If you do not plan
to use a VBA macro, but an Office automation client application, please
refer to the KB http://support.microsoft.com/kb/302084 if you are using C#.
In the automation client, we need to programmatically open the worksheet
first (with Application.WorkBooks.Open metho), then change the above VBA
code to C# by following the same logic process. If you encounter any
problem with it, please feel free to let me know. I will do my best to help
you.

Sincerely,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.