none
Debug Help Please on Access VBA to Open Excel RRS feed

  • Question

  • I seem to be unable to assign a reference to a workbook object variable.  The below code replicates the problem when I run it.  I would appreciate someone telling me were I have a code error, or if no coding error then what do I do next.  I have comments in the code that show what the debug watch showed.  Thank you very much for your help.  --jd

    Private Function Test()
    Dim xlXL As Excel.Application
    Dim xlBook As Excel.Workbook
    Const strcAddedWorksheetName = "qryTestData"
    Const strcFileName = "c:\ATest\XL_Test.xlsx"
        
        Set xlXL = New Excel.Application       'after execution, debug watch values:  xlXL = 'Microsoft Excel', xlBook = Nothing
        xlXL.DisplayAlerts = False
        xlXL.Visible = True
        Set xlBook = xlXL.Workbooks.Open(strcFileName)      'after execution, debug watch values:  xlXL = 'Microsoft Excel', xlBook = ""
            xlBook.Workbooks(strcAddedWorksheetName).Name = "New"      'this line causes Run-time error '438' Object doesn't support this property or method
            xlBook.SaveAs strcFileName, CreateBackup:=False
        xlBook.Close
        Set xlBook = Nothing
    xlXL.Quit
    Set xlXL = Nothing
    End Function
    Friday, November 2, 2012 1:17 PM

Answers

  • Thank you very much, Kolkata.  I tried to check all the object properties but I just made a mistake here.  I have made the change in the test code and it works.  But what is still odd to me (probably because I don't understand the concepts of objects well enough) is that even though it works there is no value for xlBook in the debug watch window.  I will go back to my original code and let you know if I get it all working.  Thanks again.  --jd 
    • Marked as answer by jdpavek Friday, November 2, 2012 6:20 PM
    Friday, November 2, 2012 5:34 PM

All replies

  • Change  

    xlBook.Workbooks(strcAddedWorksheetName).Name = "New"      'this line cau

    to

    xlBook.Worksheets(strcAddedWorksheetName).Name = "New"


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.


    Friday, November 2, 2012 2:08 PM
    Answerer
  • I am sorry, Kolkata, but it seems you reply did not post correctly.  Would you try again, pleas. I appreciate your help.  Thank you very much. --jd

    Friday, November 2, 2012 2:46 PM
  • Actually Workbooks is not a property of Workbook object (xlBook)

    I think you mean

    xlBook.Worksheets(strcAddedWorksheetName).Name = "New"


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, November 2, 2012 2:56 PM
    Answerer
  • Thank you very much, Kolkata.  I tried to check all the object properties but I just made a mistake here.  I have made the change in the test code and it works.  But what is still odd to me (probably because I don't understand the concepts of objects well enough) is that even though it works there is no value for xlBook in the debug watch window.  I will go back to my original code and let you know if I get it all working.  Thanks again.  --jd 
    • Marked as answer by jdpavek Friday, November 2, 2012 6:20 PM
    Friday, November 2, 2012 5:34 PM
  • Hmm.

    xlBook is declared as Excel Workbook.Not sure what you added as watch.But if you added watch for xlbook then you need to click on + symbol to get all the properties of xlbook.

    then look for name property


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, November 3, 2012 6:25 AM
    Answerer