How to change column size in VB6 and Excel 2002

[Tony Girgenti]

Hello.

Using VB6.0 and Excel 2002. I'm trying to change the column width of a
column before saving the workbook. I can do other changes to the worksheet,
like fonts and values etc., but it won't change the columnwidth.

I tried these statements and none of them work:
oSheet.Range("A1").Columns.ColumnWidth = 5.75
oSheet.Range("A1").ColumnWidth = 5.75
oSheet.Columns("A:A").ColumnWidth = 5.75

oSheet.Columns("A").Select
Selection.ColumnWidth = 5.75

Does anybody know how to do this?

Any help would be gratefully appreciated.

Thanks,
Tony

 

[GTVT06]

Hello.

Using VB6.0 and Excel 2002.  I'm trying to change the column width of a
column before saving the workbook.  I can do other changes to the worksheet,
like fonts and values etc., but it won't change the columnwidth.

I tried these statements and none of them work:
        oSheet.Range("A1").Columns.ColumnWidth = 5.75
        oSheet.Range("A1").ColumnWidth = 5.75
        oSheet.Columns("A:A").ColumnWidth = 5.75

        oSheet.Columns("A").Select
        Selection.ColumnWidth = 5.75

Does anybody know how to do this?

Any help would be gratefully appreciated.

Thanks,
Tony

Try:

Sheets("oSheet").Columns("A:A").ColumnWidth = 5.75

 

[Tony Girgenti]

Hello GTVT06.

I'm getting Subscript out of range when i try your code.

Thanks,
Tony

 

[Dave Peterson]

oSheet.Columns("A:A").ColumnWidth = 5.75

or

oSheet.Range("A1").Entirecolumn.ColumnWidth = 5.75

 

[JLGWhiz]

What exactly do you have the variable osheet equal to?
Better yet, is it a variable or a sheet name?

 

[kounoike]

i tested below in VB6.0 and Excel2003. it could change the column width of a
column, and almost your code also could change the width of column.

Sub testVB6()
Dim oApp As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set oApp = New Excel.Application

oApp.Visible = True
On Error Resume Next
Set oBook = oApp.Workbooks.Open("C:\testsample.xls")
If oBook Is Nothing Then
Set oBook = oApp.Workbooks.Add
oBook.SaveAs "C:\testsample.xls"
End If

Set oSheet = oBook.Worksheets(1)
oSheet.Columns("A").ColumnWidth = 5.75
oSheet.Range("A1") = oSheet.Columns("A").ColumnWidth

oApp.DisplayAlerts = False
oBook.Close SaveChanges:=True
Set oSheet = Nothing
Set oBook = Nothing
oApp.Quit
Set oApp = Nothing
End Sub

keiji

 

[Tony Girgenti]

Hello JLGWhiz.

I used the code posted by kounoike in this thread. It works fine except
that it doesn't change the column width. I makes all of the other changes as
far as values and fonts, etc., but not the column width.

Can this really be done?

Thanks,
Tony

 

[Tony Girgenti]

Hello kounoike.

I tried using your code. It works great except that it put a value in cell
A1 of 5.71 and it did not change the column width to 5.75. It makes all of
the other changes for values and fonts, etc., but not the column width.

Thanks,
Tony

 

[Tony Girgenti]

Hello kounoike.

I used your code and it works fine except that it doesn't change the column
width. I makes all of the other changes as far as values and fonts, etc.,
but not the column width. The value of cell A1 is 5.71 and it's width is
5.71.

Thanks,
Tony

 

[kounoike]

Hi tony

I can change column width in my environment, so i don't have any idea why
you can't change it.
by the way, can you change column width not from VB6 but with Excel VBA?
some code with Excel VBA like this "Columns("A:A").ColumnWidth = 20" could
change the column width of A to 20 on your Excel's activesheet?

keiji

 

[Tony Girgenti]

Yes, i created a macro with your statement and it worked fine.

Can you think of any reason why my program will not change the column width?

Thanks,
Tony

 

[kounoike]

Sorry, but i have no idea and it's beyond my knowledge.
Show exactly what you did , including your code, then someone else here
could help you.

keiji

 

[Tim Zych]

This quote from a KB article *might* help:

XL2000: How to Programmatically Measure the Visible Range in Points
http://support.microsoft.com/kb/213323/en-us
The ColumnWidth property can widen or narrow a column only by a minimum
incremental amount; otherwise the column width does not change. You may need
to experiment with a column to find the smallest amount by which it can be
widened.

Perhaps 5.75 is not attainable based on your worksheet's current
data/format, etc configuration, but a width close to that might be.