Why don't my Column widths match?

[MPW]

Hi All,

I am attempting to replicate column widths from one workbook to another.
Here is the line I wrote to do this.

For cc = 1 to 256
     newwkbk.Sheets(WSName.Columns(cc).ColumnWidth = _
     oldwkbk.Sheets(WSName.Columns(cc).ColumnWidth
Next cc

This should be enough to see what I am doing. If you need to see more I can provide it.

My problem is that even though the column width numbers are being carried over, the actual width of the columns dose not match.
I believe that my issue has to do with pixels vs millimeters but I am not sure. If this is the case how can I change the format of the new workbook match the format of the old? Failing that, can I convert the one into the other? For instance pixels into mm.

One more question: Why are different formats even used? It seems like it would make more sense to just use one.

Thanks

 

[Andrew Poulsom]

The code you posted doesn't compile. Why don't you copy and paste special column widths?

 

[MPW]

I know coping it over would be quicker. However, I would like to accomplish this by using values instead of the copy/paste function.

My goal is to replicate an entire workbook without copy/paste to avoid bringing over any extra baggage. This is working pretty good and has been reducing the size of the workbooks. There are a few areas I have been struggling with. ie. Validation, Conditional Formatting etc.

In this case the column widths are correct for some of the workbooks but not all. I would like to understand it better.

 

[MPW]

When you said the code doesn't compile I realized I left out the closing parentheses for the sheet names.
Sorry about that. It should have been.

For cc = 1 to 256
     newwkbk.Sheets(WSName).Columns(cc).ColumnWidth = _
     oldwkbk.Sheets(WSName).Columns(cc).ColumnWidth
Next cc

Thanks for pointing it out.

 

[Andrew Poulsom]

One unit of column width is equal to the width of one character in the Normal style. Are you sure that the font in the Normal Style is the same for both workbooks? Also is the Zoom setting the same?

 

[MPW]

Zoom,
Font Name
Font Size
are the same.

I can even change the column width manually and they physically look different.
The columns on the original sheet look wider then the new worksheet.
Also the columns on the new worksheet look like a normal workbook when it is first opened.

Eureka! I think I found the answer.

The standard font name and size in the options were set different then the original sheet.
When I set the options to match the old sheet it worked perfectly.

Sometimes just talking it out is the best answer.

Now I just have to set the properties in the options for the new workbook to match the old one. I am pretty sure that is possible.

Thanks for asking the questions that got my brain moving.

 

[MPW]

Well, I cannot apply the new font name and size without restarting Excel.
I don't suppose a variable could be carried over a close/reopen.
I guess I could write the path info into a temp file to be retrieved?
Perhaps opening a 2nd instance of Excel might work.
Any thoughts on this?

 

[shg]

I cannot apply the new font name and size without restarting Excel

You can change the font name and size for style Normal.

    Dim wkb         As Workbook
    Set wkb = ActiveWorkbook
    
    With wkb.Styles("Normal").Font
        .Name = "Arial"
        .Size = 9
        .Italic = False
        .Bold = False
    End With

 

[MPW]

I must be missing something with the styles.

I tried this as a test.

With newwkbk.Styles("Normal").Font
    .Name = oldwkbk.Styles("Normal").Font.Name
    .Size = oldwkbk.Styles("Normal").Font.Size
End With

The columns still didn't match even though the styles were loaded.

I wonder if I need to set this before I load newwkbk or before I create it in the first place?

I do know that I can change it in the options and then open up a 2nd instance of Excel with the new properties. But, If I can get your idea to work it would be cleaner.

 

[MPW]

Changing the Application.StandardFont and StandardFontSize for the new Workbook is only half of the challenge.

The bigger question is "How do you find out these properties for an already existing document?"

Apparently the workbook carries this information with it. How do you retrieve it? I do not even know if there is a interface that can read those properties.