Can't set column width

[TheVillagesBill]

As part of an application I am trying to build an excel worksheet from
scratch using visual basic. When I try to set the column width I receive the
following error: "Exception from HRESULT: 0x800A03EC" on the first statement
that tries to set the columnwidth. This is very frustrating because I have
all the code working on the old Visual Basic 6, but can't get it going on
Visual Studio 2008 with Excel 2007. Note that changing the names of the
sheets just above the failing code works. This seems to be related to the
previous question that I posted today about getting the value of an existing
cell. I've extracted the code that fails and it is below: HELP

.............................................................................................

Imports Microsoft.Office.Interop.Excel
Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim returnFile As String = "C:\MyPool\MyPoolData\Test"
Dim k, j As Integer
Dim schedDate As String
Dim schedTime As String

Dim xla As New Application
Dim xlb = xla.Workbooks.Add
xlb.Activate()
j = 0
For Each ws As Worksheet In xlb.Worksheets
j += 1
ws.Name = "Week" & Trim(j.ToString)
Next

For Each ws As Worksheet In xlb.Worksheets
For k = 0 To 1
'ws.Activate()
'ws.Select()
ws.Range(ws.Columns(1 + (5 * k))).ColumnWidth = 10 'Number
for selection
ws.Range(ws.Columns(2 + (5 * k))).ColumnWidth = 12 'Number
for selection
Next
Next
schedDate = Format(Date.Today, "yyyyMMdd")
schedTime = Format(TimeOfDay, "hhmm")
returnFile = returnFile & schedDate & schedTime
xlb.SaveAs(returnFile, FileFormat:=XlFileFormat.xlWorkbookNormal)
xlb.Close(SaveChanges:=False)
xla.Quit()

End Sub
End Class

 

[JLGWhiz]

Just a quick glance I see:

Dim k, j As Integer

Should be:

Dim k As Integer, j As Integer

Otherwise k is variant

Dim xlb = xla.Workbooks.Add

Should be:

Set xlb = xla.Workbooks.Add

I would also Dim ws As Worksheet with the other declarations instead of
trying to do it on the fly.

Then change:

For Each ws As Worksheet In xlb.Worksheets

To"

For Each ws In xlb.Worksheets

Then the statementa for column width only needs the column as a column, not
a range.

Exmpl:
ws.Columns(1 + (5*k)).ColumnWidth = 10

 

[TheVillagesBill]

Fixed the j and k dim. Can't change the Dim to Set because there is no
longer a Set statement available in vb.net. Can't eliminate the range
because the routine won't compile without it; intellisense says that option
strict disallows late binding. I fput in a dim for the ws worksheet and
removed the as worksheet for the for each. Overall result, same problem.

 

[JLGWhiz]

I am not familiar with the peculiarities of vb.net, but I am pretty sure
that is the hang up in Excel.

You could try this syntax to see if both vb.Net and VBA will accept it:

ws.Range("1:1").Offset(,5*k).ColumnWidth = 10

But I am pretty sure that is the culprit that caused the original error.

 

[TheVillagesBill]

I'm not sure what this did, but it didn't work. On the first pass thru the
loop it seemed to work, but when K=1 it failed with an exception. I changed
the instruction location to save the sheet and exit when the exceptioin
occurred
and found that all columns in the first sheet had changed their column width
to 10, not just column 1.

 

[TheVillagesBill]

Found the reason it didn't work. I needed to specify both ends of the range.