Get excel formatting information

[Roy Vinyard]

I'm using vb6 to extract data from an excel worksheet. Is there any way to obtain formatting information? I need individual column widths and whether text spans a number of columns.

[RobDog888]

Since there are a few questions to be answered I think this way is best.

Usually the easiest way to find out how to access some property or method in most Office Apps is to record
a macro and then check out the code in the VBA IDE (ALT+F11). Its usually in a Module with the date it was written in the
header of the procedure. Then you can place the code in VB with a little translating where needed.

Post back if you neeed more help on this.

HTH

[Roy Vinyard]

Yes, I agree but, in this case, I can't see how to record a macro asking for the information I need.

[RobDog888]

Ok, here is the first recorded macro. I just started a recording and adjusted a column's width.

Sub Macro1()
' Macro1 Macro
' Macro recorded 2/7/2005 by VB/Office Guru
    Columns("C:C").ColumnWidth = 20
    Columns("E:E").ColumnWidth = 6
End Sub

[RobDog888]

Ok, now this one is a little different. I had to go into the Formatting menu
and click Cells... then check Merge cells after I selected three cells that I
had typed some text into. I typed the text into the first cell and continued
typing until it reached the third cell.

Sub Macro2()
' Macro2 Macro
' Macro recorded 2/7/2005 by VB/Office Guru
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Testing the macro to record spaned cell info."
    Range("A4:C4").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
End Sub

So these two macros will show you how to access the properties/methods
to determine the formatting on a spreadsheet.



HTH

[Roy Vinyard]

THanks very much for taking so much trouble to help me. I'll get working along these lines.

[RobDog888]

Not a problem. Glad to help.

Ps, dont forget to Resolve your thread