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
Expand|Select|Wrap|Line Numbers
- 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
- .Range(Selection, Selection.End(xlUp)).Select
- Selection.FillDown
- End With
- End Sub