Use the Excel Application object within Access

Question

Use the Excel Application object within Access

Navigation

#1 by (2 votes)

2

I am creating a VBA script that exports a table into several excel shared worksheets in a specific directory.

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

I would like to delete this confirmation. Using VBA directly in excel macros you can do the following:

Application.DisplayAlerts = False

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

Code snippet that generates the tiles:

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: As the DoCmd.TransferSpreadsheet acExport command does not save the exported file as shared, I have to reopen the worksheet in the excel object, apply some formatting, and save as shared.

excel vba ms-access

asked by anonymous 28.04.2014 / 15:31

1 answer

Orphan records, checking in another table How to use LEAD and LAG using a condition

score 2 · Accepted Answer

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

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

Next, within your Access code, simply create a new instance of Excel, and reference 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