Excel OLE Problem

The code shown below, I can't run. Error is "Object dosen't support this property...", line "ExcelSheet.Cells..."
What should I do?

thank you
---------------------------------------------------------
Private Sub Command1_Click()
Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Visible = True
ExcelSheet.Cells(1, 1).Value = "This is column A, row 1"
ExcelSheet.Application.Quit
Set ExcelSheet = Nothing
End Sub
itacanAsked:
Who is Participating?
 
cognitionCommented:
Try the following which uses late binding.

    'Dim XLApp As New Excel.Application
    Dim XLApp As Excel.Application
    Dim XLWS As Worksheet
    Dim XLWB As Workbook
    Dim XLCellRange As Range


    Set XLApp = CreateObject("Excel.Application")

    XLApp.Application.Visible = True
    Set XLWB = XLApp.Workbooks.Add()
    Set XLWS = XLWB.Worksheets.Add()
    XLWS.Name = "Worksheet Name"
    XLWS.Columns("A:A").ColumnWidth = 25
    XLWS.Columns("B:B").ColumnWidth = 15
   
    With XLWS.Cells(1, 1)
        .Value = "PRODUCT SUMMARY SPREADSHEET"
        .Font.Size = 14
        .Font.Bold = True
    End With
0
 
mdouganCommented:
You can always go to the View|Object browser menu item, select the Excel library, select the sheet object and view what the available properties and methods are.  In this case I looked up Cells and saw that this is a method available to the Application object.  So, maybe you want to be doing CreateObject("Excel.Application")

MD
0
 
cognitionCommented:
I wrote this just for you !
In Project | References set a reference to Microosft Excel Object Library.

    Dim XLApp As New Excel.Application
    Dim XLWS As Worksheet
    Dim XLWB As Workbook
    Dim XLCellRange As Range

    XLApp.Application.Visible = True
    Set XLWB = XLApp.Workbooks.Add()
    Set XLWS = XLWB.Worksheets.Add()
    XLWS.Name = "Worksheet Name"
    XLWS.Columns("A:A").ColumnWidth = 25
    XLWS.Columns("B:B").ColumnWidth = 15
   
    With XLWS.Cells(1, 1)
        .Value = "A cell value"
        .Font.Size = 14
        .Font.Bold = True
    End With


0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
itacanAuthor Commented:
Error : Invalid use of new keyword!!!
0
 
mdouganCommented:
Hey, everyone is just re-submitting (granted, a more detailed explanation of) my rejected answer!  Here's another thing you could try with your original code that might work.

change
ExcelSheet.Cells(1, 1).Value = "This is column A, row 1"

to
ExcelSheet.Application.Cells(1, 1).Value = "This is column A, row 1"

MD

0
 
amebaCommented:
Your line:
   Set ExcelSheet = CreateObject("Excel.Sheet")
will create WorkSheet in Excel 7, but WorkBook in Excel 8.

You can replace your line with this 2 lines:
   Set ExcelBook = CreateObject("Excel.Sheet") ' this will create XLbook
   Set ExcelSheet = ExcelBook.Sheets(1)

Your second line works:
   ExcelSheet.Application.Visible = True
because Workbook also has Application property

but your third line won't work (Workbook doesn't have Cells)


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.