itacan
asked on
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.Vis ible = True
ExcelSheet.Cells(1, 1).Value = "This is column A, row 1"
ExcelSheet.Application.Qui t
Set ExcelSheet = Nothing
End Sub
What should I do?
thank you
--------------------------
Private Sub Command1_Click()
Set ExcelSheet = CreateObject("Excel.Sheet"
ExcelSheet.Application.Vis
ExcelSheet.Cells(1, 1).Value = "This is column A, row 1"
ExcelSheet.Application.Qui
Set ExcelSheet = Nothing
End Sub
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").Column Width = 25
XLWS.Columns("B:B").Column Width = 15
With XLWS.Cells(1, 1)
.Value = "A cell value"
.Font.Size = 14
.Font.Bold = True
End With
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").Column
XLWS.Columns("B:B").Column
With XLWS.Cells(1, 1)
.Value = "A cell value"
.Font.Size = 14
.Font.Bold = True
End With
ASKER
Error : Invalid use of new keyword!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Cel ls(1, 1).Value = "This is column A, row 1"
MD
change
ExcelSheet.Cells(1, 1).Value = "This is column A, row 1"
to
ExcelSheet.Application.Cel
MD
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.Vis ible = True
because Workbook also has Application property
but your third line won't work (Workbook doesn't have Cells)
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"
Set ExcelSheet = ExcelBook.Sheets(1)
Your second line works:
ExcelSheet.Application.Vis
because Workbook also has Application property
but your third line won't work (Workbook doesn't have Cells)
MD