Run-time Error '424': Object Required

[lakshmib01]

Hi,

I am trying to execute the below code to access a chart in excel from VB.

Dim xlwbook As Excel.Workbook
Set xlwbook = xl.Workbooks.Open("D:\Reports\test status1.xlsm",True)
Dim ActiveSheet AS Excel.Worksheet
Set ActiveSheet = xlwbook.Sheets.Item(4)
Dim ActiveChart As Excel.ChartObject
Set ActiveChart = ActiveSheet.ChartObjects("Chart 4").Activate

The line "Set ActiveChart = ActiveSheet.ChartObjects("Chart 4").Activate" is throwing the error "Run-time Error '424': Object Required". I am not able to identify any fix. can any one please help me in identifying a solution.

Thanks in advance,
Lakshmi

[lakshmib01]

Hi,

can any one please help.I am not able to identify the issue.

Thanks,
Lakshmi

[iagina]

Dim xlwbook As Excel.Workbook

The line "Set ActiveChart = ActiveSheet.ChartObjects("Chart 4").Activate" is throwing the error "Run-time Error '424': Object Required".

How did you define Excel.Workbook? take a look at its definition because according to your program, it cannot see chart 4

[dglienna]

Try this:

Charts("Chart 4").Activate
ActiveSheet.SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbBlue

[lakshmib01]

Thanks for the replies,

ActiveSheet.SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbBlue is not working as Activesheet doesn’t' have an element SeriesCollection. Hence it is throwing a compile time error.

Excel.worbook is being recognized by the VB editor as i have added Microsoft.Excel in my project->references.

Thanks,
Lakshmi

[iagina]

Hi,

Set xlwbook = xl.Workbooks.Open("D:\Reports\test status1.xlsm",True)

I don't know if this code really opens the workbooks as it is supposed to... Is there a way of ensuring the workbook is seen as open? Then access can be made to the chart. I don't know how to do this but I do know it can be done, someone may help.

[dglienna]

This works:

Option Explicit

Public xlAppTemp As Excel.Application
Public xlWorkBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Dim strDate$

Public Sub GenerateReport()
  On Error GoTo ErrHandler
  
  ' Creating Object for Excel File.....
  Set xlAppTemp = New Excel.Application
  
  ' Making it Invisible and non-Interactive.....
  xlAppTemp.Visible = False
  xlAppTemp.DisplayAlerts = False
    ' Opening Template Excel File.....
  Set xlWorkBook = xlAppTemp.Workbooks.Open(App.Path & "\Book1.xls", , False)
  Set xlSheet = xlWorkBook.Sheets(1)

  ' Making Active to Worksheet 1.....
  xlSheet.Activate

  ' I am doing lot of things in it, but to provide you with example
  xlSheet.Cells(15, 1) = "This is my report 1"

  ' Formating Date to attach with new file name.....
  strDate = Format(Date, "yyyy-mm-dd")
  
  ' Saving excel file with new name on different folder.....
  xlWorkBook.SaveAs App.Path & "\Output" & strDate & ".xls"

Cleanup:
  ' Destroying Objects.....
  Set xlSheet = Nothing
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing
'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True
  xlAppTemp.Quit
  Set xlAppTemp = Nothing
  Exit Sub
ErrHandler:
'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
'(save happens just above if no error occurs)
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing

'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True

  xlAppTemp.Quit
  Set xlAppTemp = Nothing
End Sub

Private Sub Command1_Click()
  Call GenerateReport
  Beep
End Sub

[babycherub]

Hi. I get this error when i am opening a office 2007 Excel document. it has lots of graphs involved, any help appreciated

[dglienna]

Please start a new post. That doesn't have anything to do with 424