Use of Transferspreadsheet via VBA

Asked

Viewed 1,976 times

0

I’m finishing my improvement project and there’s something that intrigues me a lot.

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

As I don’t 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. However every time I tried to use it excel returned an error message saying:

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

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

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

In this form I have a "Update Base" button, when the user clicks on the button he should, in theory, copy the data of a given spreadsheet into a BD table.

From my research the code wouldn’t be too 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 it work?

excel vba ms-access

  • The object DoCmd is exclusive to Access.

  • It’s not a difficult thing to solve, but I have some doubts about what you’re trying to do. 1 - This spreadsheet is always in the same place? 2 - You could not include the data directly in Access instead of the spreadsheet and then to Access?

  • I ask this because in the case 1 you could simply create a linked table within Access and in case 2 you could make the tab ativos always received table data ativos through a connection.

  • @Mark yes the spreadsheet will always be in the same place. But it can be replaced monthly. This asset spreadsheet contains all the employees of the company. Then every month a new spreadsheet will be generated with the data. This worksheet will be saved in the same place and with the same name (overwriting the previous one) and the new collaborators should be imported into the Access table. Since I already have a form that connects with the BD it would be interesting to just add a button on it to make the import.

  • As this form will be used by other people, who do not have so much knowledge about BD, programming and etc, I would prefer that they do not need to use Access to import as it would avoid problems.

  • Is that the way it was placed, it seems to me that you want the table in BD is always replaced by the data of the spreadsheet that is being used at the time.

  • Make this inclusion through the spreadsheet will disturb for example in consistency, for example, not need to update or insert something that is already in the BD.

  • And regarding the knowledge about BD and programming, this is irrelevant because you can make the Access file look like a system to be made this inclusion by a user-friendly interface.

Show 3 more comments

1 answer

1


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

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

Not forgetting the references to execute the above code:

inserir a descrição da imagem aqui

  • I know I could use in the access itself, but I already have a form that connects to BD. I thought it would be easier, both for me and for the end user, if all options were available in one place. This way it would save time to search for information in other places. Your code perfectly met what I needed to do. Thank you very much.

  • Good that you solved, just be careful because you will always be overwriting the data. But if you met your demand, I believe you know the risks. Hug

Browser other questions tagged excel vba ms-access

You are not signed in. Login or sign up in order to post.