Solved

Excel OLE Problem

Posted on 1999-01-11
6
236 Views
Last Modified: 2013-12-25
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
0
Comment
Question by:itacan
6 Comments
 
LVL 18

Expert Comment

by:mdougan
ID: 1489554
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
 
LVL 3

Expert Comment

by:cognition
ID: 1489555
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
 

Author Comment

by:itacan
ID: 1489556
Error : Invalid use of new keyword!!!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Accepted Solution

by:
cognition earned 100 total points
ID: 1489557
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
 
LVL 18

Expert Comment

by:mdougan
ID: 1489558
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
 
LVL 15

Expert Comment

by:ameba
ID: 1489559
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now