Opening Excel Workbook From Access with VBA

[kestefon]

I am working on an Access VBA project, and one step involves creating an Excel workbook.



I pulled a function from a book as a starting point, but when I try to execute it I get an error that says, "An error occurred trying to add new workbook: Object variable or With block variable not set". I think this might have something to do with my references (i.e. Tools > References), but I'm not sure what I'm doing wrong. I'll attach a screenshot of my selections. Also, I'm using Excel and Access 2013.

.

And here is the VBA function I'm referring to.

Function modExcel_CreateNewWorkBook() As Boolean
' Open an existing word document
    On Error Resume Next
    Set wkbExcel = appExcel.Workbooks.Add
    If Err <> 0 Then
        MsgBox "An error occured trying to add new workbook : " & Err.Description, vbCritical, "Unable To Create Workbook"
        modExcel_CreateNewWorkBook = False
    Else
        modExcel_CreateNewWorkBook = True
    End If
End Function

[June7]

That is the proper library reference.

The variable appExcel is not declared nor Set.

If you got this example from a book, I am not impressed by it. The example does not declare variables.

What do you want to happen with workbook once it is created? Should it be named and saved? Populated with data? Should Excel be made visible and allow working with the workbook directly?

[kestefon]

Oops, I actually forgot to paste the variable declarations, but they were indeed part of the code:

Option Compare Database
Option Explicit


Dim appExcel As Excel.Application
Dim wkbExcel As Excel.Workbook
Dim wksExcel As Excel.Worksheet

As for what will be done in Excel, I have some existing Excel VBA procedures that I would run. That part is already complete...it's just a matter of integrating that with Access.

[June7]

And the Set lines?

Sub modExcel_CreateNewWorkBook()
Dim appExcel As Excel.Application
Dim wkbExcel As Excel.Workbook
Dim wksExcel As Excel.Worksheet
Set appExcel = New Excel.Application
Set wkbExcel = appExcel.Workbooks.Add
appExcel.Visible = True
wkbExcel.Close False
appExcel.Quit
Set wkbExcel = Nothing
Set appExcel = Nothing
End Sub

[ssanfu]

You might check out examples of Excel automation at ken Snell's site
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

It's been helpful to me.......