Bonjour à tous,
Voici mon petit problème.
J'importe des données depuis une base AS400, or les données importées font plus de 65536 lignes et donc l'import stoppe quand l'onglet est plein.
Comment puis-je faire pour lui demander de créer un nouvel onglet (ou plus si nécessaire) et de continuer l'import sur les onglets suivants ?
Merci
Voici le code actuel :
Public ZtNomAS
Public ZtNomDSN
Public ZtAgence
Public ZtDateDeb
Public ZtDateFin
Public ZtCodeClt
Public ZtTemp
Sub Ext4()
'
'
ZtNomAS = "FROM " & ("TOA.AZTOUDTA") & ".EXPEDE EXPEDE"
'ZtNomDSN = "ODBC;DSN=" & ("AZL2000") & ";"
ZtAgence = "(EXPEDE.EXDCARJ=" & Range("CodeAgence") & ")"
ZtDateDeb = Year(Range("DateDeb")) & Right("00" & Month(Range("DateDeb")), 2) _
& Right("00" & Day(Range("DateDeb")), 2)
ZtDateDeb = "(EXPEDE.EXDDTNM>=" & ZtDateDeb & ")"
ZtDateFin = Year(Range("DateFin")) & Right("00" & Month(Range("DateFin")), 2) _
& Right("00" & Day(Range("DateFin")), 2)
ZtDateFin = "(EXPEDE.EXDDTNM<=" & ZtDateFin & ")"
'ZtCodeClt = "(EXPEDE.EXDCTDGDE=" & ("122999") & ")"
Sheets("Expeditions").Select
Range("$A$2").Select
With Selection.QueryTable
.Connection = "ODBC;DSN=AZL2000"
.Sql = Array( _
"SELECT EXPEDE.EXDDTNM, EXPEDE.EXDK2DGDE, EXPEDE.EXDCTDGDE, EXPEDE.EXDCYDGDE, EXPEDE.EXDCLDGDE, EXPEDE.EXDEGDGDE, EXPEDE.EXDNUNM, EXPEDE.EXDREDGDE, EXPEDE.EXDK2FM, EXPEDE.EXDCYFM, EXPEDE.EXDCP, EXPEDE.EXDCLFM, EXPEDE.EXDEGFM, EXPEDE.EX" _
, _
"DNBC0NM, EXPEDE.EXDSS, EXPEDE.EXDMHSRQY, EXPEDE.EXDMXSRQY, EXPEDE.EXDMHQZ, EXPEDE.EXDMXQZ, EXPEDE.EXDD6QY, EXPEDE.EXDNFQY, " _
, _
" EXPEDE.EXDNFQZ, EXPEDE.EXDF2MBSR, EXPEDE.EXDJGH5, EXPEDE.EXDPVTP, EXPEDE.EXDCOIG, EXPEDE.EXDCD" & Chr(13) & "" & Chr(10) & ZtNomAS & Chr(13) & "" & Chr(10) & "WHER" _
, _
"E " & ZtAgence & " AND (EXPEDE.EXDJGH5='D') AND " & ZtDateDeb & " AND " & ZtDateFin & " " _
)
.Refresh False
End With
Sheets("Expeditions").Select
Cells.Select
ActiveSheet.Copy
Workbooks("Extraction a2000.xls").Activate
Sheets("Expeditions").Select
Rows("2:65536").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Voici mon petit problème.
J'importe des données depuis une base AS400, or les données importées font plus de 65536 lignes et donc l'import stoppe quand l'onglet est plein.
Comment puis-je faire pour lui demander de créer un nouvel onglet (ou plus si nécessaire) et de continuer l'import sur les onglets suivants ?
Merci
Voici le code actuel :
Public ZtNomAS
Public ZtNomDSN
Public ZtAgence
Public ZtDateDeb
Public ZtDateFin
Public ZtCodeClt
Public ZtTemp
Sub Ext4()
'
'
ZtNomAS = "FROM " & ("TOA.AZTOUDTA") & ".EXPEDE EXPEDE"
'ZtNomDSN = "ODBC;DSN=" & ("AZL2000") & ";"
ZtAgence = "(EXPEDE.EXDCARJ=" & Range("CodeAgence") & ")"
ZtDateDeb = Year(Range("DateDeb")) & Right("00" & Month(Range("DateDeb")), 2) _
& Right("00" & Day(Range("DateDeb")), 2)
ZtDateDeb = "(EXPEDE.EXDDTNM>=" & ZtDateDeb & ")"
ZtDateFin = Year(Range("DateFin")) & Right("00" & Month(Range("DateFin")), 2) _
& Right("00" & Day(Range("DateFin")), 2)
ZtDateFin = "(EXPEDE.EXDDTNM<=" & ZtDateFin & ")"
'ZtCodeClt = "(EXPEDE.EXDCTDGDE=" & ("122999") & ")"
Sheets("Expeditions").Select
Range("$A$2").Select
With Selection.QueryTable
.Connection = "ODBC;DSN=AZL2000"
.Sql = Array( _
"SELECT EXPEDE.EXDDTNM, EXPEDE.EXDK2DGDE, EXPEDE.EXDCTDGDE, EXPEDE.EXDCYDGDE, EXPEDE.EXDCLDGDE, EXPEDE.EXDEGDGDE, EXPEDE.EXDNUNM, EXPEDE.EXDREDGDE, EXPEDE.EXDK2FM, EXPEDE.EXDCYFM, EXPEDE.EXDCP, EXPEDE.EXDCLFM, EXPEDE.EXDEGFM, EXPEDE.EX" _
, _
"DNBC0NM, EXPEDE.EXDSS, EXPEDE.EXDMHSRQY, EXPEDE.EXDMXSRQY, EXPEDE.EXDMHQZ, EXPEDE.EXDMXQZ, EXPEDE.EXDD6QY, EXPEDE.EXDNFQY, " _
, _
" EXPEDE.EXDNFQZ, EXPEDE.EXDF2MBSR, EXPEDE.EXDJGH5, EXPEDE.EXDPVTP, EXPEDE.EXDCOIG, EXPEDE.EXDCD" & Chr(13) & "" & Chr(10) & ZtNomAS & Chr(13) & "" & Chr(10) & "WHER" _
, _
"E " & ZtAgence & " AND (EXPEDE.EXDJGH5='D') AND " & ZtDateDeb & " AND " & ZtDateFin & " " _
)
.Refresh False
End With
Sheets("Expeditions").Select
Cells.Select
ActiveSheet.Copy
Workbooks("Extraction a2000.xls").Activate
Sheets("Expeditions").Select
Rows("2:65536").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents