locked
Object variable or with block variable not set RRS feed

  • Question

  • hi, i had a code in outlook that worked for me till now with only 1 problem with it, and i fixed it yesterday, its about a sleep event that takes place later in the code, but since that i start to get another error on a diffrent syntex, 

    basicly its doing alot of stuff, so let me write where the problem is, in middle of the project it opens a excell file, and it goes to a specific page and by column B it takes the last filled cell and asighn a range to it, so later i can call it using a find method.

    so here is my code;

    remember xlSheet is set as an object

    Set xlWB = xlApp.Workbooks.Open("C:\Users\xxxxx\Desktop\xxx\Orders.xlsx")
    Set xlSheet = xlWB.Sheets("On Display")
    ' refresh for the new csv file
    xlWB.Connections("inventory").Refresh
    
    'get the last cell with info
    
    xlWB.Sheets("On Display").Activate
    ActiveSheet.Range("B1").Select
    Selection.End(xlDown).Select
    Set LastRange = Selection

    now i get an error object variable or with block variable not set, it worked for me till today, i dont know what happen, i tried playing around with the code using a with, but then it stoped by selection.End, i am just getting more & more confused,

    its all in outlook vba module.

    here is where the code stops at

    ActiveSheet.Range("B1").Select

    Wednesday, March 20, 2013 3:31 PM

Answers

  • Try:

    Set xlWB = xlApp.Workbooks.Open("C:\Users\xxxxx\Desktop\xxx\Orders.xlsx")
    Set xlSheet = xlWB.Sheets("On Display")
    ' refresh for the new csv file
    xlWB.Connections("inventory").Refresh

    'get the last cell with info
    Dim LastRange As Range
    Set LastRange = xlSheet.Cells(xlSheet.Rows.Count, "B").End(xlUp)
    MsgBox LastRange.Address

    • Marked as answer by Sol Stein Wednesday, March 20, 2013 5:41 PM
    Wednesday, March 20, 2013 5:28 PM

All replies

  • Try:

    Set xlWB = xlApp.Workbooks.Open("C:\Users\xxxxx\Desktop\xxx\Orders.xlsx")
    Set xlSheet = xlWB.Sheets("On Display")
    ' refresh for the new csv file
    xlWB.Connections("inventory").Refresh

    'get the last cell with info
    Dim LastRange As Range
    Set LastRange = xlSheet.Cells(xlSheet.Rows.Count, "B").End(xlUp)
    MsgBox LastRange.Address

    • Marked as answer by Sol Stein Wednesday, March 20, 2013 5:41 PM
    Wednesday, March 20, 2013 5:28 PM
  • thanks for your reply, such a simple syntex i dont know why i didnt think of that :( i had that syntex already on another process, just went out of my head.
    Wednesday, March 20, 2013 5:43 PM