Using TransferSpreadSheet via VBA

Question

Using TransferSpreadSheet via VBA

Navigation

#1 by (1 votes)

0

I'm finalizing my improvement project and have something that intrigues me a lot.

I needed to make a report based on the result of an SQL query, which I even asked in Exporting SQL query data to Excel , and now I need to do the opposite and import data from an excel spreadsheet to the access.

As I do not have much knowledge about VBA, many things I've done I've had to research a lot before. In all cases it has always appeared as an option to use the DoCmd.TransferSpreadSheet method. However every time I tried to use it, it returned an error message saying:

The "TransferSheet" command or action is not available now.

My question is: Can I use this command inside excel? Or should it only be used in Access?

In my case I have the DB created with Access, and a form created in excel to perform the registration of the information in the DB.

On this form I have a "Update Base" button, when the user clicks the button it should, in theory, copy the data of a certain worksheet into a DB table.

By my research the code would not be very complex, it would look something like this:

Private Sub atualizarbase_btn_Click()
    Dim strXls As String

    strXls = ThisWorkbook.Path & "\ATIVOS\ativos.xlsx"

    DoCmd.TransferSpreadsheet acImport, , "ativos", strXls, True, "ativos!"

End Sub

How can I make this work?

excel vba ms-access

asked by anonymous 06.04.2017 / 21:55

1 answer

Oracle insert too many values Pass variable to Javascript for Controller SpringMVC

score 1 · Accepted Answer

As I said in the comments of your question, there are other ways to do what you want, but by directly answering the question of how to use DoCmd.TransferSpreadsheet in VBA within Excel, you can do this:

Sub TransferirPlanilha()
    Dim strCaminhoDB as String, strXls as String
    Dim appAccess as Access.Application

    strCaminhoDB = "C:\Ativos.accdb"
    strXls = ThisWorkbook.Path & "\ATIVOS\ativos.xlsx"

    Set appAccess = New Access.Application

    With appAccess
        Application.DisplayAlerts = False
        .OpenCurrentDatabase strCaminhoDB
        .DoCmd.TransferSpreadsheet acImport, , "ativos", strXls, True, "ativos!"
        .Quit
        Application.DisplayAlerts = True
    End With

    Set appAccess = Nothing
End Sub

Without forgetting the references to execute the code above: