Using the Excel Application object within Access

Question

I am creating a VBA script that exports a table in multiple shared excel sheets in a specific directory.

The problem is that Excel sends a warning to confirm the file save for each file.

I would like to delete this confirmation. Using VBA directly in excel macros it is possible to do the following:

Application.DisplayAlerts = False

Does anyone know how to use the object Application from the instance of that spreadsheet within Access or some other way to save that file ?

Code snippet that generates the spreadsheets:

Private Function formataPlanilha(path As String)

    Dim workbook As Excel.workbook
    Dim sheet As Excel.Worksheet

    Set workbook = Excel.Application.Workbooks.Open(path)

    If workbook.MultiUserEditing Then
        workbook.ExclusiveAccess
        workbook.Application.DisplayAlerts = False
    End If

    Set sheet = workbook.Sheets(1)

    On Error GoTo ErrorHandler

    With sheet
        Call Formata_Planilha_Faltas
    End With

    workbook.Saved = True
    workbook.SaveAs Filename:=path, AccessMode:=xlShared, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges

ErrorHandler:
    workbook.Close

End Function

Note: Like the command DoCmd.TransferSpreadsheet acExport does not save the exported file as shared, have to reopen the spreadsheet in excel object, apply some formatting and save as shared.

Answer 1

The Excel VBA code can be used in the same way from within Access, or Word, or from any other Office application.

First, you must import the Excel library into the script written in the other program, in this case, Access. In the editor Microsoft Visual Basic for Applications, go to the menu Ferramentas (Tools), then Referências (References). There, locate and mark the item Microsoft Excel XX Object Library, where XX is the Excel version. Give a OK.

Then, within your Access code, just create a new instance of Excel, and refer it accordingly:

Sub Exemplo()
    Dim excelApp As Excel.Application
    Set excelApp = New Excel.Application

    'Se atribuir True, a janela do Excel aparecerá
    excelApp.Visible = False


    'Coloque seu código aqui, substituindo Excel.Application por excelApp
    '...


    'Termina o Excel
    excelApp.Quit
End Sub