?
Solved

Excel object

Posted on 2000-04-18
9
Medium Priority
?
129 Views
Last Modified: 2010-05-02
I have this code which I copyed straight from the help files

Private Sub Command1_Click()
' Declare an object variable to hold the object
' reference. Dim as Object causes late binding.
Dim ExcelSheet As Object

Set ExcelSheet = CreateObject("Excel.Sheet")
ExcelSheet.Application.Visible = True
' Place some text in the first cell of the sheet.
ExcelSheet.Cells(1, 1).Value = "This is column A, row 1"
' Save the sheet to C:\test.doc directory.
ExcelSheet.SaveAs "C:\temp\TEST1.xls"
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.
Set ExcelSheet = Nothing

End Sub

but when it gets to the line

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

I get the error message "Object doesn't support this message or property"
0
Comment
Question by:damienm
  • 5
  • 3
9 Comments
 
LVL 22

Accepted Solution

by:
ture earned 200 total points
ID: 2726125
damienm,

The error is because this line...
  Set ExcelSheet = CreateObject("Excel.Sheet")
....creates a WORKBOOK and not a SHEET.

A Workbook does not have a Cells property. Therefore, this line generates an error:
  ExcelSheet.Cells(1, 1).Value = "This is column A, row 1"

Use this instead:
  ExcelSheet.Sheets(1).Cells(1, 1).Value = "This is column A, row 1"

Ture Magnusson
Karlstad, Sweden
0
 

Expert Comment

by:skyblue011400
ID: 2726130
Try changing the problem line to:

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

You can get help on the Excel Application object properties and methods by adding a reference to the Microsoft Excel Object Library in your project and using the object browser (press F2).
0
 

Expert Comment

by:skyblue011400
ID: 2726133
skyblue changed the proposed answer to a comment
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Expert Comment

by:ture
ID: 2726152
Thanks, skyblue!
0
 

Expert Comment

by:skyblue011400
ID: 2726159
it's the first time i have ever posted anything.  i was surprised to see your answer popup a minute before mine!
0
 

Expert Comment

by:skyblue011400
ID: 2726167
it's the first time i have ever posted anything.  i was surprised to see your answer popup a minute before mine!
0
 
LVL 22

Expert Comment

by:ture
ID: 2726170
Welcome to EE, skyblue! It's good to see that you are beginning to post solutions. Keep at it! You'll beat me next time...

It was nice of you to withdraw your answer when you noticed my post with the same solution.

/Ture
0
 

Expert Comment

by:skyblue011400
ID: 2726186
.. and maybe i'll know not to press refresh next time!
0
 
LVL 2

Author Comment

by:damienm
ID: 2726255
Hardluck Skyblue a few seconds earlier and you would have 50 points,

Thanks Ture, Skyblue
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

839 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