Object With Variable not set

[gblack]

[SOLVED] Object With Variable not set

I am trying to format an open Excel Spreadsheet from MS Access using VBA.

My issue is I run the code once and it runs fine... then I run it a second time and it gives me the Runtime 91 error: Object Variable or With block variable not set... If I then run it again... it runs fine, then I run it another time I get the error over and over... rinse, repeat. The error seems to toggle and I don't know why.

    Dim xlAPPLICATION As Excel.Application
    Dim xlWORKBOOK As Excel.Workbook
    Dim xlWORKSHEET As Excel.Worksheet
    Dim xlCHART As Excel.Chart
    Dim Db As DAO.Database
    Dim rsFROM As DAO.Recordset
    Dim rsFIELD As DAO.Field
    Dim i As Integer
    Dim j As Integer
    Dim strFORCE As String
    Dim strCLASS As String
    Dim intMSG As Integer


    ' Prepare your Excel stuff
    Set xlAPPLICATION = New Excel.Application
    xlAPPLICATION.Visible = True
    Set xlWORKBOOK = xlAPPLICATION.Workbooks.Add
    xlWORKBOOK.Activate
    Set xlWORKSHEET = xlWORKBOOK.ActiveSheet ' Default: The first sheet in the newly created book
    
    xlAPPLICATION.DisplayAlerts = False
    ' Read your data here
    Set Db = CurrentDb()
    strSQL = "SELECT * FROM [00500_UNION_COUNT_VALUE_AND_ROLL_UP];"
    Set rsFROM = Db.OpenRecordset(strSQL)

    ' A simple table that will show the data from rec
    ' i and j will be the coordiantes of the active cell in your worksheet
    With rsFROM
        .MoveFirst

        ' The table headers
        i = 1
        j = 1
        For Each rsFIELD In .Fields
            xlWORKSHEET.Cells(i, j).Value = rsFIELD.Name
            j = j + 1
            
        Next rsFIELD

        ' The table data
        Do
            i = i + 1
            j = 1
            For Each rsFIELD In .Fields
                xlWORKSHEET.Cells(i, j).Value = rsFIELD.Value
                j = j + 1
                If i = 2 And j = 4 Then
                    strFORCE = rsFIELD.Value
                End If
                If i = 2 And j = 5 Then
                    strCLASS = rsFIELD.Value
                End If

            Next rsFIELD
            .MoveNext
        Loop Until .EOF
    End With
    'Format Sheet
    xlWORKSHEET.Name = strFORCE & "-" & strCLASS
    
    
    Select Case Forms![0000_0000_HIERARCHY]![frmChart_Type].Value
        Case 1
            MsgBox ("Option 1 Has been Chosen")
        Case 2
            MsgBox ("Option 2 Has been Chosen")
        Case 3
            MsgBox ("Option 3 Has been Chosen")
        Case 4
            With xlWORKSHEET
                .Cells.Select
                .Range("C1").Activate
                .Cells.EntireColumn.AutoFit
                .Range("E1:U8").Select
                .Shapes.AddChart.Select
            End With
            xlWORKBOOK.ActiveChart.ApplyChartTemplate ( _
                    "C:\Users\garland.black\AppData\Roaming\Microsoft\Templates\Charts\REVERSE_BAR_AREA_2016_05_07_PM06.crtx" _
                    )

            With xlWORKSHEET
                .Shapes("Chart 1").ScaleWidth 2.0281248906, msoFalse, msoScaleFromBottomRight
                .Shapes("Chart 1").ScaleHeight 1.1875, msoFalse, msoScaleFromTopLeft
                .Shapes("Chart 1").ScaleWidth 1.2100668346, msoFalse, msoScaleFromTopLeft
                .Shapes("Chart 1").IncrementLeft -250.25
                .Shapes("Chart 1").IncrementTop -2.25
                .Shapes("Chart 1").IncrementLeft 36
            End With
        Case 5

            With xlWORKSHEET
                .Range("C1:C7,F1:U7").Select
                .Range("F1").Activate
                .Shapes.AddChart.Select
            End With
            xlWORKBOOK.ActiveChart.ApplyChartTemplate ("C:\Users\garland.black\AppData\Roaming\Microsoft\Templates\Charts\SAND_CHART_TEMPLATE_2016_05_05_PM10.crtx")
            With xlWORKSHEET
                .Shapes("Chart 1").IncrementLeft -274.5
                .Shapes("Chart 1").IncrementTop 61.5
                .Shapes("Chart 1").ScaleWidth 2.1406251094, msoFalse, _
                    msoScaleFromTopLeft
                .Shapes("Chart 1").ScaleHeight 1.0868055556, msoFalse, _
                    msoScaleFromTopLeft
            End With
    End Select
    xlWORKSHEET.Columns("A:B").Select
    xlAPPLICATION.Selection.EntireColumn.Hidden = True

    xlWORKSHEET.Range("C1:U1").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10027008
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    Range("C1:U8").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("C1").Select


   xlWORKBOOK.SaveAs ("C:\Users\garland.black\Desktop\SAND_CHART_" & strFORCE & "-" & strCLASS & ".xlsx")

The code breaks at this line:

    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With

:banghead::banghead::banghead::banghead::banghead::banghead:

 

[arnelgp]

With xlApplication.Selection.Font
....
....
....
End With

 

[speakers_86]

A couple things. First of all, I recommend validating the value you are setting at this line:

xlWORKSHEET.Name = strFORCE & "-" & strCLASS

A worksheet name has a length limit, so find out the limit and use .name=left(strFORCE & "-" & strCLASS, x). I also recommend stripping out spaces and special keys here (if that is even remotely possible to be in the tables).

Also, there is no error handler in the code, you certainly want something to handle errors.

You are setting the object variables at the beginning, but at the end, you did not release those variables at the end. The object variables can persist, but if they do, you do not have to set them at the beginning each time.

And finally, the issue is likely that you are saying Selection without indicating you intend Excel.Selection. The code thinks you mean Access.Selection. So you probably want xlWORKSHEET.Selection...

Hope that helps.

 

[gblack]

Thank you both for the replies.

Adding xlApplication.Selection.Font... and so on throughout (i.e. wherever there's a "Selection.XXX"), seemed to do the trick. The funny thing was that I thought I had tried that and it didn't work... so I was about to write back and say no... but I tried it again and bam... works like a charm.

Thanks for the other information as well (i.e. about the name length and error handling and such)... I was not nearly finished with coding for this, so I will keep that in mind as I continue, but this part was driving me a bit batty...

Thanks again...