vba access RRS feed

  • Question

  • Bonjour pour ce petit code j ai une erreur qui apparaît et je ne vois comment faire pour y remédier?

    "L'opération doit utiliser une requête qui peut être mise à jour"

    erreur 3073

    Public Sub import()

    Dim Chemin as String

    Dim NameList()

    wkbookpath = chemin

    Dim XL As Object

    Set XL = CreateObject("Excel.Application")

    With XL

    .Visible = False

    .displayalerts = False

    .Workbooks.Open wkbookpath

    For Each ws In XL.Worksheets

    ReDim Preserve NameList(counter)

    NameList(counter) = ws.Name

    counter = counter + 1


    .ActiveWorkbook.Close (True)


    End With

    Set XL = Nothing

    For i = LBound(NameList()) To UBound(NameList())

    DoCmd.TransferSpreadsheet acImport, , NameList(i), wkbookpath, True, NameList(i) & "!A1:IU9999"

    Next i

    End Sub

    Friday, October 12, 2018 1:09 PM

All replies

  • We speak English in this forum.

    At first sight your code looks OK, and since it does not use any queries, the error may come from somewhere else. Use the debugger: Ctrl+Break when the error occurs, then inspect the code, variables, etc.

    -Tom. Microsoft Access MVP

    Friday, October 12, 2018 1:55 PM
  • In English the error message for 3073 is, "Operation must use an updateable query."

    In principle, that code works for me, though you did not declare a number of variables, and you should always do so.  But even if I declare them as Variant, which would be the default if you do not have "Option Explicit" specified, when I test the code it works.

    Since there is no query explicitly used in the code, I guess that the error is raised in the call to TransferSpreadsheet.  I wondered if maybe it was caused by a worksheet name that is not valid as an Access table name, so I tried it with a worksheet named "zzImport.Test2" (since the dot character (.) is not valid in table names.  However, it still succeeded, replacing the "." with"_" in the resulting table name.

    Does the error arise for every workbook you run it on?  Does it arise for every worksheet in the workbook?  If it is raised for only one worksheet, what is different about that worksheet?

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, October 12, 2018 3:53 PM