Bonsoir le Forum!

En important des données d'Excel vers ACCESS j'ai rencontré l'erreur 6: Dépassement de capacité. C'est la ligne 31 qui est coloré jaune

Code:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Private Sub btnImportXl1_Click()
'Microsoft Excel xx.x Object Library
'Early binding
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
'Late binding
'Dim xlApp As Object
'Dim xlWbk As Object
'Dim xlWst As Object
Dim strFilePath As String, strBackSlash As String, strFileName As String, strExt As String
Dim strTableName As String, strSheetName As String, strExportAddress As String, strExportSheetAddress As String
Dim lastRow As Integer
Dim lastColumn As Integer
Dim rngExport As Range
 
strFilePath = CurrentProject.Path
strBackSlash = "\"
strFileName = "EXPORT_NOTE"
strExt = ".xlsx"
strFilePath = strFilePath & strBackSlash & strFileName & strExt
 
strTableName = "T_NOTE"
strSheetName = "Feuil1"
 
Set xlApp = New Excel.Application ' Early binding
'Set xlApp = CreateObject("Excel.Application")' Late binding
Set xlWb = xlApp.Workbooks.Open(strFilePath)
Set xlWs = xlWb.Sheets(strSheetName)
 
lastRow = xlWs.Cells(xlWs.Rows.Count, "A").End(xlUp).Row ' calcule le dernier numéro de ligne non vide
lastColumn = xlWs.Cells(1, xlWs.Columns.Count).End(xlToLeft).Column ' calcule le dernier numéro colonne non vide
'Set rngExport = Range(Cells(1, 1), Cells(lastRow, lastColumn)) 'instancie la plage de cellules
Set rngExport = xlWs.Range(xlWs.Cells(1, 1), xlWs.Cells(lastRow, lastColumn)) 'instancie la plage de cellules
Debug.Print rngExport.Address
 
strExportAddress = WorksheetFunction.Substitute(rngExport.Address, "$", "")
strExportSheetAddress = strSheetName & "!" & strExportAddress
 
Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel12Xml, strTableName, strFilePath, True, strExportSheetAddress)
 
Workbooks(strFileName & strExt).Close SaveChanges:=False
Set xlWb = Nothing
If xlApp.ActiveWindow Is Nothing Then
    xlApp.Quit
End If
Set xlApp = Nothing
Dim strKill As String
'strKill = "TASKKILL /F /IM EXCEL.exe"
'Shell strKill, vbHide
 
End Sub