CODEWITHSUNDEEP
excelms-accessvba
Robert Kendall
Robert Kendall

Reputation: 390

Add sheet to Excel through Access VBA

When running this code, I receive an "Object Required" error on activeworkbook.sheets.add command

Also tried objexcel.sheets.add("Test") and received "Add method of Sheets class failed." 

    Public Sub InitialConditions(FileName As Variant)

        Dim objexcel As Object
        Dim wbexcel As Object

        Set objexcel = CreateObject("excel.Application")
        Set wbexcel = objexcel.workbooks.Open(FileName)

        activeworkbook.sheets.Add ("Test")

        objexcel.Visible = True

    End Sub

Upvotes: 0

Views: 8960

Answers (2)

HansUp
HansUp

Reputation: 97101

Your code uses late binding. But the Excel object model is rich and complex. So I think you would be better off to use early binding during development and testing. That way you can take advantage of Intellisense to guide you and the compiler will be able to identify problems more thoroughly.

Public Sub InitialConditions(FileName As String)
    '* enable these 4 lines for deployment
'    Dim objexcel As Object
'    Dim wbexcel As Object
'    Dim objsheet As Object
'    Set objexcel = CreateObject("excel.Application")

    '* enable these 4 lines for development and testing
    '* (requires reference to Excel Object Library)
    Dim objexcel As Excel.Application
    Dim wbexcel  As Excel.Workbook
    Dim objsheet As Excel.Worksheet
    Set objexcel = New Excel.Workbook

    Set wbexcel = objexcel.Workbooks.Open(FileName)
'    ActiveWorkbook.Sheets.Add ("Test")
    Set objsheet = wbexcel.Sheets.Add
    objsheet.Name = "Test"
    objexcel.Visible = True

End Sub

When you're ready to deploy your application, you can switch back to late binding. That allows you to remove the Excel reference which reduces the possibility of reference issues in production use.

Upvotes: 3

PKatona
PKatona

Reputation: 639

try 

activeworkbook.sheets.add after:=activeworkbook.sheets(activeworkbook.sheets.count)
activeworkbook.sheets(activeworkbook.sheets.count).Name = "Test"

Upvotes: -1

Related Questions