none
2010 Excel VBA - Run Time Error - Object doesn't support this property or method RRS feed

  • Question

  • I'm writing a line of code for an Excel 2010 macro which will run a vlookup between two workbooks. The vlookup is used to determine the open/closed status of the invoice numbers in the "MainWkbk" by opening a daily report "APODaily". Being a newbie to VBA I'm trying to write the code so that it adds a lookup worksheet in the MainWkBk, then pastes columns M-P of each worksheet in the APODaily file onto the lookup sheet in MainWkbk, then runs the vlookup itself.

    My problem is once I paste the data from the 1st APODaily worksheet onto the Lookup worksheet of the MainWkBk, I can't activate the APODaily file again to pull data from the 2,3,4th worksheets.

    I've tried various methods from "APODaily.Activate", "Workbook(APODaily).Activate" and "Windows.Workbook(APODaily).Activate", none of them work. Any suggestions?

    Private Sub OKButton_Click()
    Dim MainWkbk As Workbook
    Dim APODaily As String
    Set MainWkbk = ActiveWorkbook
    Application.DisplayAlerts = False
    Dim Path As String
    Path = "S:\Daily Reports\APO\"
    Dim PrevDate As Date
    PrevDate = Date - 1
    Application.DisplayAlerts = True
    Columns("M:M").Select
    Selection.EntireColumn.Insert
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "AR Status"
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Lookup"
    Range("A1").Select
    APODaily = Path & "AP_Daily_SNP_" & Format(PrevDate, "mmddyy") & ".xls"
    Workbooks.Open (APODaily), _
        Notify:=False, ReadOnly:=True
    Columns("M:P").Select
    Selection.Copy
    MainWkbk.Activate
    Range("A1").Select
    ActiveSheet.Paste
    Selection.End(xlDown).Offset(1, 0).Select
    Windows.Workbook(APODaily).Activate ********Error happens here


    Lorac1969

    Thursday, June 25, 2015 12:22 PM

Answers

  • Hi Lorac1969,

    To make the code works, please ensure the get workbook correctly. As Wouter metioned that we don't need to specific the fullpath using the workbooks collection. We can refer the workbook via the name of workbook.

    Also we can get the instance of workbook when we open or create a workbook. For examlpe, here is the code get the workbook instance when open it:

    Set aWorkbook = Workbooks.Open("FilePath")
    aWorkbook.Activate
    In addtion, here is some helpful links for you learning Excel developing:
    How do I... (Excel 2013 developer reference)

    Object model reference (Excel 2013 developer reference)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 26, 2015 6:42 AM
    Moderator

All replies

  • Hello,

    You should not include the entire path in the string APODaily when you use it in Workbook(APODaily).

    eg.  location is c:\temp\excel1.xlsm   -> Workbook(excel1.xlsm).activate not Workbook(c:\temp\excel1.xlsm).activate

    Hope it helps!

    Wouter

    Thursday, June 25, 2015 12:29 PM
  • Hi Wouter,

    Thanks for taking the time to reply. I'm confused the line of code where there error is occuring doesn't have the full file path, it only has the workbook name in the brackets.

    Windows.Workbook(APODaily).Activate ********Error happens here

    The only place where I list the whole path is in the begining of the macro:

    Dim Path As String
    Path = "S:\Daily Reports\APO\"

    After the above lines only there is no mention of the full path. I have a lot to learn about VBA and the proper terminology so It's very possible I'm missunderstanding you.


    Lorac1969

    Thursday, June 25, 2015 7:51 PM
  • Hi Lorac1969,

    To make the code works, please ensure the get workbook correctly. As Wouter metioned that we don't need to specific the fullpath using the workbooks collection. We can refer the workbook via the name of workbook.

    Also we can get the instance of workbook when we open or create a workbook. For examlpe, here is the code get the workbook instance when open it:

    Set aWorkbook = Workbooks.Open("FilePath")
    aWorkbook.Activate
    In addtion, here is some helpful links for you learning Excel developing:
    How do I... (Excel 2013 developer reference)

    Object model reference (Excel 2013 developer reference)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 26, 2015 6:42 AM
    Moderator