By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,644 Members | 2,105 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,644 IT Pros & Developers. It's quick & easy.

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

P: 1
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

Expand|Select|Wrap|Line Numbers
  1.  Sub Combo0_AfterUpdate()
  2.     Dim xlApp As Excel.Application
  3.     Dim xlBook As Excel.Workbook
  4.     Dim xlWkSht As Excel.Worksheet
  5.     Dim MyRng As Excel.Range
  7.     Dim strMyPath As String
  8.     Dim i As Integer
  9.     Set xlApp = New Excel.Application    ' ("Excel.Application")
  10.     Set xlBook = xlApp.Workbooks.Open(strMyPath)
  11.     Set xlWkSht = xlBook.Worksheets("Milk Production")
  14.     strMyPath = Forms!switchboard!txtFileLocation.Value & "\" &     Me.Combo0.Text
  15.     xlApp.Visible = True
  16.     With xlWkSht
  18.         'xlApp.ScreenUpdating = False
  19.         .Activate 'goes to specific xl worksheet ,even if another worksheet in collection was
  20.         'active when workbook was saved and closed
  21.         .Range("h3").Value = "=right(c2,22)"
  22.         .Columns("A:A").Select
  23.         For i = 1 To 3
  24.             .Columns("A:A").Insert Shift:=xlToRight
  25.         Next
  26.         .Range("A5").Select
  27.         .Range("a5").Value = "ClientID"
  28.         .Range("b5").Value = "Farm"
  29.         .Range("c5").Value = "Year"
  30.         .Range("a6").Value = "=mid(k3,4,5)"
  31.         .Range("b6").Value = "=mid(k3,10,2)"
  32.         .Range("c6").Value = "=left(k3,3)"
  33.         .Range("a6:c6").Copy
  34.         .Range("a6:c6").PasteSpecial Paste:=xlPasteValues
  35.         .Rows("1:4").Select
  36.         .Rows("1:4").Delete
  37.         .Range("d2").Select
  38.         .Range("d2").End(xlDown).Select
  39.         .Application.ActiveCell.Offset(-2, -3).Range("a1:c1").Select
  40.         .Range(Selection, Selection.End(xlUp)).Select
  41.         Selection.FillDown
  42.     End With
  44.     End Sub
Dec 24 '07 #1
Share this Question
Share on Google+
3 Replies

Expert 5K+
P: 8,434
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.
Dec 29 '07 #2

P: 58
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 ...
Dec 30 '07 #3

Expert 5K+
P: 8,434
Good point, ubentook. I missed that.
Dec 31 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.