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

 

[shg]

Those are application-wide settings. If you create a workbook from scratch, they are the font and font size used in the new workbook. If you have default templates in XLSTART, then they don't affect a new workbook from the template, but are the font and font size used in the formula bar and names box.

How do you retrieve it?

They are read-write properties; just read them.

is only half of the challenge

I thought the challenge was resolved:

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.

 

[MPW]

I agree that the StandardFont is an Application setting for all new workbooks. However, once a workbook is created those attributes are attached to the workbook. Since these properties reside on the workbook they will ignore the application font settings. I believe this is true but if not let me know.



The challenge I was was referring to was to:
Read the font properties from an existing workbook.
Reset the StandardFont in the Application to match those fonts.
Open up a new instance of Excel.
Create a new version of the old workbook.

You said.

They are read-write properties; just read them.

Where can I read the workbook properties that contain the StandardFont of the Workbook when it was created?


Experiment:
If you Change the StandardFontSize to 20 and close/reopen Excel.
Save the blank workbook.
Change the StandardFontSize back to the original size (I think it is 10pt) and close/reopen Excel.
Now compare the workbook that was saved with a new one and see the difference.

 

[shg]

When you create a new, blank workbook (i.e., not from the template in XLSTART), then the font name and style for Normal are taken from Application.StandardFont/Size. That will remain the case for that workbook if you later change Application.StandardFont/Size to something else. It is the style that persists, not the setting.

It doesn't matter how it came to pass; there are, to my knowledge, no lingering genes that would tell you what StandardFont/Size were when the workbook was created, nor is there any lingering expression of those genes in the workbook.

You can change the font of style Normal in any workbook at will.

 

[MPW]

Did you try the experiment I mentioned?

 

[shg]

Yes, and got exactly the behavior I expected; style Normal was unchanged from the style it was saved as.

 

[MPW]

Thank you for your patience. I know I must sound like I'm chasing leprechauns or something. I guess my breakdown must be the difference between setting the font and setting the style. The earlier code that you sent:

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

I did try the code that you sent but it did not seem to help.

I understand the style encompasses the font attributes. Outside of VBA how would you set the style of a workbook?

My issue still is that when trying to adjust the column width to match an older workbook they do not always match up.

 

[shg]

Outside of VBA how would you set the style of a workbook?

In Excel 2007, right-click the style in the Style Gallery, click Modify.

 

[MPW]

So sorry, I usually do this at the beginning of each post.
I am using Office 2003 in Windows XP.

 

[shg]

Format > Style

 

[MPW]

Sorry to take so long to answer. Other irons and all that.

I had to get my brain around it but finally light broke through.
I trapped the styles when I opened the original workbook to clone and then applied them to the new workbook. It worked very well.

Thanks for your patience, and the correct answer.