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
--
The Villages Bill

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" <(E-Mail Removed)> wrote in message
news:CB291914-77CE-46A0-8673-(E-Mail Removed)...
> 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
> --
> The Villages Bill

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.
--
The Villages Bill


"JLGWhiz" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:CB291914-77CE-46A0-8673-(E-Mail Removed)...
> > 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
> > --
> > The Villages Bill
>
>
> .
>

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" <(E-Mail Removed)> wrote in message
news:4ADEB12B-7312-44E6-8DB7-(E-Mail Removed)...
> 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.
> --
> The Villages Bill
>
>
> "JLGWhiz" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:CB291914-77CE-46A0-8673-(E-Mail Removed)...
>> > 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
>> > --
>> > The Villages Bill
>>
>>
>> .
>>

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.
--
The Villages Bill


"JLGWhiz" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:4ADEB12B-7312-44E6-8DB7-(E-Mail Removed)...
> > 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.
> > --
> > The Villages Bill
> >
> >
> > "JLGWhiz" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:CB291914-77CE-46A0-8673-(E-Mail Removed)...
> >> > 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
> >> > --
> >> > The Villages Bill
> >>
> >>
> >> .
> >>
>
>
> .
>

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


"TheVillagesBill" wrote:

> 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.
> --
> The Villages Bill
>
>
> "JLGWhiz" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:4ADEB12B-7312-44E6-8DB7-(E-Mail Removed)...
> > > 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.
> > > --
> > > The Villages Bill
> > >
> > >
> > > "JLGWhiz" wrote:
> > >
> > >> 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" <(E-Mail Removed)> wrote in message
> > >> news:CB291914-77CE-46A0-8673-(E-Mail Removed)...
> > >> > 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
> > >> > --
> > >> > The Villages Bill
> > >>
> > >>
> > >> .
> > >>
> >
> >
> > .
> >