vba error : 'Object variable not set...'

[MARRICK]

I am attempting to automate Excel from Access.The code below works the first
time it is run but fails with the above error on subsequent attempts.To re run
the code successfully the database must be re opened.
The break occurs where the line is highlighted below.The problem seems to be with Excel's Selection Method.

Most grateful for any information on how to work round this problem

 Sub Combo0_AfterUpdate()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlWkSht As Excel.Worksheet
    Dim MyRng As Excel.Range

    Dim strMyPath As String
    Dim i As Integer
    Set xlApp = New Excel.Application    ' ("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(strMyPath)
    Set xlWkSht = xlBook.Worksheets("Milk Production")

    
    strMyPath = Forms!switchboard!txtFileLocation.Value & "\" &     Me.Combo0.Text
    xlApp.Visible = True
    With xlWkSht

        'xlApp.ScreenUpdating = False
        .Activate 'goes to specific xl worksheet ,even if another worksheet in collection was
        'active when workbook was saved and closed
        .Range("h3").Value = "=right(c2,22)"
        .Columns("A:A").Select
        For i = 1 To 3
            .Columns("A:A").Insert Shift:=xlToRight
        Next
        .Range("A5").Select
        .Range("a5").Value = "ClientID"
        .Range("b5").Value = "Farm"
        .Range("c5").Value = "Year"
        .Range("a6").Value = "=mid(k3,4,5)"
        .Range("b6").Value = "=mid(k3,10,2)"
        .Range("c6").Value = "=left(k3,3)"
        .Range("a6:c6").Copy
        .Range("a6:c6").PasteSpecial Paste:=xlPasteValues
        .Rows("1:4").Select
        .Rows("1:4").Delete
        .Range("d2").Select
        .Range("d2").End(xlDown).Select
        .Application.ActiveCell.Offset(-2, -3).Range("a1:c1").Select
        [B].Range(Selection, Selection.End(xlUp)).Select[/B]
        Selection.FillDown
    End With

    End Sub

[Killer42]

Sorry to see you don't appear to be getting any response to this question.

I'm afraid it's a bit beyond what I've done in VBA, but I do wonder - is it possible that the changes made the first time 'round are changing the way the selection operates?




Note, I changed the asterisks to bolding, so it stands out. Oh, and another thing. I think you might find more VBA expertise in the Access forum than the VB one. No guarantees, but it might be worth a try.

[ubentook]

The strMyPath variable is not assigned until after you use it to open the workbook.
How are you getting the workbook to open using an empty variable?
'------------------

I am attempting to automate Excel from Access.The code below works the first ...

[Killer42]

Good point, ubentook. I missed that.