Object variable or With block variable not set, Error 91

[Hemish]

Hi i am copying a query from MS Access to an excisting excel template. I am using VBA code, once the data is copied then it formats the excel document.

When i run this function, 1st time it runs properly, then i ran it again the same function does not work and stops on the formatting part. If i close the database and re open it, it works 1st time then second time get the same message Object variable or With block variable not set, Error 91.

I am a bit stuck here below is the code



Public Function AgedStockExport()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim xlApp As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strPathName As String
Dim strQueryName As String
Dim strSheetName As String
Dim strFilePath As String


Set db = CurrentDb()


On Error GoTo err_handler


strFilePath = "C:\SalesReport\AST.XLSX"
strQueryName = "qryAgedStockFinal" ' query from database
strSheetName = "AgedStock"

strPathName = strFilePath


Set rst = db.OpenRecordset(strQueryName)


Set xlApp = CreateObject("Excel.Application")


Set xlWBk = xlApp.Workbooks.Open(strPathName)


xlApp.Visible = True


Set xlWSh = xlWBk.Worksheets(strSheetName)



xlWSh.Range("A3").CopyFromRecordset rst





xlWSh.Range("O3:R3").Select
Range(Selection, Selection.End(xlDown)).Select Error Message second time i run this
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 16764057
.TintAndShade = 0
.PatternTintAndShade = 0
End With



xlWSh.Range("N2:N3").Select
xlWSh.Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone


xlWSh.Range("F2:F3").Select
xlWSh.Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone

xlWSh.Range("X2:X3").Select
xlWSh.Range(Selection, Selection.End(xlDown)).Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone


xlWSh.Range("S3").Select
xlWSh.Range(Selection, Selection.End(xlDown)).Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 12119736
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.Color = -16747520
.TintAndShade = 0
End With

xlWSh.Range("T3:W3").Select
xlWSh.Range(Selection, Selection.End(xlDown)).Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 14277119
.TintAndShade = 0
.PatternTintAndShade = 0
End With






xlWBk.SaveAs "c:\SalesReport\Test.xlxs"
rst.Close

Set xlApp = Nothing

Set xlWSh = Nothing
Set xlWBk = Nothing
Set rst = Nothing
Set db = Nothing
Exit_AgedStockExport:
Exit Function


err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Resume Exit_AgedStockExport
End Function


Please could you guide me as i am stuck on this.

Thanks

 

[Trevor G]

You have this, but you need to reference your dim statement here so try this

xlWSh.Range("O3:R3").Select
Range(Selection, Selection.End(xlDown)).Select Error Message second time i run this
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 16764057
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Try this and you will then have to check what else you need to adjust below this point.

With xlWSh
.Range("O3:R3").Select
.Range(Selection, Selection.End(xlDown)).Select 'Error Message second time i run this
.Interior.Color = 16764057
End With