2 Replies - 731 Views - Last Post: 15 August 2013 - 04:16 PM Rate Topic: -----

#1 jctgt  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 08-August 13

VB6/Excell Syntax

Posted 15 August 2013 - 02:25 PM

Hi,
I have one form and one textbox
The codes are:

Private Sub Form_Load()
Dim excelApp As Excel.Application
Dim excelWB As Excel.Workbook
Set excelApp = CreateObject("Excel.Application")
Set excelWB = excelApp.Workbooks.Add
excelApp.Visible = True
Dim excelWS As Excel.Worksheet
‘Add a new sheet
Set Sheet = excelWB.Worksheets.Add
Sheet.Name = Date
End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
    Case Is = 8 
    Case Is = 32 
    Case Is = 13                     
   	 Set excelWS = excelWB.Worksheets()               ’HERE IS MY PROBLEM
      	 excelWS.Cells(2, 1) = Text1.Text    Case Is = 45  
    Case 65 To 90   
    Case 97 To 122    
    Case 233 
    Case Else
        KeyAscii = 0: Exit Sub
End Select 'Keyascii
End Sub


’HERE IS MY PROBLEM
I have a run-time error ‘424’ Object required

Please help me to solve my problem

Thanks for your help

Is This A Good Question/Topic? 0
  • +

Replies To: VB6/Excell Syntax

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2890
  • View blog
  • Posts: 9,597
  • Joined: 12-December 12

Re: VB6/Excell Syntax

Posted 15 August 2013 - 02:39 PM

Set excelWS = excelWB.Worksheets() 

excelWS is a Worksheet object and needs to be set to a single worksheet, using one of:
= Worksheets.Add
= Worksheets(3)
= Worksheets("sheetname")
= Sheet1

(among a few others)

BTW I wouldn't name a variable Sheet (which it appears you haven't declared). This may cause a conflict at some point; even if not, it makes the code harder to read. Nor would I set the name of a worksheet directly to Date, I would wrap it in Format(Date,"...")

This post has been edited by andrewsw: 15 August 2013 - 02:42 PM

Was This Post Helpful? 0
  • +
  • -

#3 jctgt  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 08-August 13

Re: VB6/Excell Syntax

Posted 15 August 2013 - 04:16 PM

Thanks andrewsw,

Would you please tell me where to write those code you tell me?

The sheet is name Date as 2013-08-15
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1