Access creates XL, adds sheet, then can't use With Selection

[JustAmy]

I have an Access application that creates an Excel workbook and then starts adding sheets to it. All is well until I try to select a range and use a "With Selection" statement to set several properties at once. I get an Error 91, "Object variable or With block variable not set." However, if I lose the With Selection and just set all properties individually, everything is fine. It looks to me like the problem is that the selection isn't getting selected.

Here's the code that doesn't work:

    Dim wst As Object
    Set wst = wbk.Worksheets.Add
    wst.Name = "Cover Letter"
    wst.Columns("B").ColumnWidth = 35
    wst.Columns("A").ColumnWidth = 6
    wst.Columns("B").WrapText = True

    wst.Rows("31:31").RowHeight = 20
    wst.Rows("19:19").RowHeight = 30

    wst.Range("B19:E19").Select
      With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
       End With
     wst.Range("B19:E19").Merge

And here's the code that does work.

    Dim wst As Object
    Set wst = wbk.Worksheets.Add
    wst.Name = "Cover Letter"
    wst.Columns("B").ColumnWidth = 35
    wst.Columns("A").ColumnWidth = 6
    wst.Columns("B").WrapText = True

    wst.Rows("31:31").RowHeight = 20
    wst.Rows("19:19").RowHeight = 30

    wst.Range("B19:E19").HorizontalAlignment = xlLeft
    wst.Range("B19:E19").VerticalAlignment = xlBottom
    wst.Range("B19:E19").Orientation = 0
    wst.Range("B19:E19").AddIndent = False
    wst.Range("B19:E19").IndentLevel = 0
    wst.Range("B19:E19").ShrinkToFit = False
    wst.Range("B19:E19").ReadingOrder = xlContext
    wst.Range("B19:E19").MergeCells = False
    wst.Range("B19:E19").Merge

Hope that's enough code to show you what you need to know. Any ideas?

Amy

 

[Norie]

Why not try introducing another object into your code?

eg

Dim wstSelectRange As Object

Set wstSelectRange=wst.Range("B19:E19")

With wstSelectRange
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
wstSelectRange("B19:E19").Merge

Make any difference?

 

[JustAmy]

Re: Access creates XL, adds sheet, then can't use With Selec

It did make a difference, with just a slight alteration to the merge command. Here's what I used:

   wstSelectRange.Merge

Very weird. Why does this work and not "With Selection"?

Thanks so much for your help!

Amy

 

[bat17]

I think that it is because Selection is an aplication level object in XL so it should be qualified with your XL Variable

Dim xlApp As Excel.Application
.....

With xlApp.Selection
......


HTH

Peter

 

[Norie]

Amy

Didn't mean to leave the range in that line - it was late.