Object variable or With block variable not set after initially working

Question

I've got this really frustrating problem. The code below is supposed to export a query from Access to Excel and then color the first row, bold it, and autofit the whole worksheet.

This needs to happen multiple times, so I've simply copy pasted the number of times needed with the little changes to the dim names. Unfortunately, it stops working after the second round and gives me the error "Object variable or With block variable not set" and then it highlights a section of the code as seen below. I mean, at this point, I'm just going to do this manually, so it's not a life threatening issue, but I'd love to figure out how to make this work.

Thanks, here's the code that's duplicated multiple times:

Private Sub cmdFinal_Click()
Dim fileLocation As String ' Main folder
Dim finalExport As String

fileLocation = "C:\MYDOCS\Latest\"
finalExport = "FINAL_EXPORT_" & UCase(Format(DateAdd("m", -7, Date), "MMMyy")) & "-" & UCase(Format(DateAdd("m", -2, Date), "MMMyy"))

' Export queries to Final Excel file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryFINAL", fileLocation & finalExport True, finalExport 
        DoEvents
        Me.ProgressBar.Visible = True
        Me.ProgressBar.Width = 500
        Me.Repaint
        DoEvents
    ' Open Excel file, apply colors, save, and quit
    Set xl = CreateObject("Excel.Application")
    Set wr = xl.Workbooks.Open(fileLocation & finalExport & ".XLSX")
    Call ColorMe
    DoEvents
    wr.Save
    wr.Close
    xl.Quit
    DoEvents
    Set sh = Nothing
    Set wr = Nothing
    Set xl = Nothing
    DoEvents

Sub ColorMe()
'
' Format document and make it pretty
'
Set sh = wr.Worksheets(1)

With sh
    sh.Rows("1:1").EntireRow.Select
    With Selection.Interior   <----------Here's where the error occurs
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End With
    Selection.Font.Bold = True
    Cells.Select
    Cells.EntireColumn.AutoFit
DoEvents
End Sub

mielk · Accepted Answer · 2015-07-10 14:32:07Z

3

There are few things:

1) I can't see where the method cmdFinal_Click() is closed - there is no

End Sub

line.

2) You should pass variable wr as parameter to the method ColorMe. Otherwise, this method doesn't know what workbook to process:

Sub ColorMe(wr As Excel.Workbook)

and

Call ColorMe(wr)

3) You don't need to select a range in order to format it. Try the following code:

Sub ColorMe(wr As Excel.Workbook)
    Dim sh As Worksheet
    Dim rng As Excel.Range


    Set sh = wr.Worksheets(1)
    Set rng = sh.Rows(1)   '<---- you create reference to the first row here
                           '      and later you should use this variable
                           '      instead of selecting cells in Worksheet and
                           '      use Selection.

    With rng.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    With rng.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .Bold = True
    End With

    'Cells.Select           '<---- Commented. Can cause errors if worksheet is not active.
    Cells.EntireColumn.AutoFit

    DoEvents

End Sub

answered Jul 10, 2015 at 14:32

mielk

3,94015 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

choglad Over a year ago

Spot on! Way to go! Self-confidence booster for you! Except the "Cells.EntireColumn.Autofit" still strangely doesn't do anything, but the rest works perfectly, so it doesn't matter! And of course I should've passed wr as a variable! Obvious! Y'know, sometimes it feels like my mind is shrinking rather than growing.

mielk Over a year ago

Try to specify worksheet like that: wr.Cells.EntireColumn.AutoFit If worksheet is not specified, the current worksheet is processed.

Collectives™ on Stack Overflow

Object variable or With block variable not set after initially working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related