When running Excel macros from Access for the second time I get the error
' Object variable not set...'
The code breaks on the tagged line :

.Application.ActiveCell.Offset(-2, -3).Range("a1:c1").Select
**** .Range(Selection, Selection.End(xlUp)).Select****
Selection.FillDown

and after working correctly once requires re setting.
The problem appears to be the word Selection and I am wondering how best to re-phrase the above code.
I have studied other problems like this but so far have failed to find a solution that matches.
Any further help would be very much appreciated

First of all, can you post the entire code.

Second, you should NOT be referencing .Range that way. You should be using Excel objects. So, if you are, we need to see your code.

Thankyou for your response.The code below works fine the first time round,
but fails with the above error thereafter


Private Sub Combo0_AfterUpdate()
Dim strMyPath As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlWkSht As Excel.Worksheet
Dim i As Integer
Dim MyRng As Excel.Range
strMyPath = Forms!switchboard!txtFileLocation.Value & "\" & Me.Combo0.Text

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(strMyPath)
Set xlWkSht = xlBook.Worksheets("Milk Production")


With xlWkSht

'xlApp.ScreenUpdating = False
.Activate
.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**
.Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
End With

End Sub

1. If you are using Dim xlApp As Excel.Application then change this:
Set xlApp = CreateObject("Excel.Application")
to this:
Set xlApp = New Excel.Application

2. Why are you using .Application.ActiveCell...etc. instead of just .ActiveCell.Offset... to go with the xlWkSht that you have already used a WITH.

Thankyou very much for your time and help - problem solved

Cool - glad to be able to help. Merry Christmas! :)