Excel -> Access Import Bizarreness

  • Thread starter Thread starter Mike Labosh
  • Start date Start date
M

Mike Labosh

A guy gave me an Excel file that he needs me to covert to XML. No problem.
Slurp it into a database and use a DataSet.WriteXML() call to turn it into
an XML file.

But I can't import it into Access because it has "Merged Cells". For
example, data that should look like this:

ColA ColB
----- -----
Rec1 this
Rec2 this
Rec3 this
Rec4 that
Rec5 Other

The three "this" cells have been merged into one tall cell:

ColA ColB
----- -----
Rec1 this
Rec2
Rec3
Rec4 that
Rec5 Other

I need to "unmerge" these, but I know almost nothing about Excel. Can
someone show me the magic thing to do?
 
Range("D8:E10").mergecells = false

EEEEEWWWWW!!!!! That's awful. This file is miles long and has hundreds of
instances of merged values. I finally found the checkbox in the Format
Cells dialog, but either manually clicking all these things or coding their
cell addresses is going to be supremely painful. I guess I'm S.O.L. ?
 
OK>select the cells button (top of the 1 and left of the A)>right
click>format>alingment>uncheck merge cells
 
OK>select the cells button (top of the 1 and left of the A)>right
click>format>alingment>uncheck merge cells
Click to expand...

Outstanding! But the blocks of cells that were merged didn't get the values
"fill-down" copied into them. Is there another cool trick that can be
coupled with the above technique that will also "fill-down" the values?
 
My approach:

Write a formula to the right that is something like =if(A2="", A1, A2) in
cell B2, copy to the bottom. Then copy, paste special values to remove the
formula. Then delete the original column.

I have had to do this when referencing pivot tables before.
 
And if that data is in a pivottable, it'll have to be converted to values first.
 
Back
Top