Bonjour le forum,
Je souhaite ouvrir un fichier texte qui contient 100000 lignes sous excel.
J'ai trouvé un post qui propose une macro mais lorsque j'execute cette macro j'ai le message d'erreur: Erreur de syntaxe dans la clause FROM
concernant la ligne: oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
Pouvez vous m'indiquer ou se trouve l'erreur car je ne la trouve pas.
Merci d'avance.
Ci dessous le code complet:
Sub ImportLargeFile()
' Imports text file into Excel workbook using ADO.
' If the number of records exceeds 65536 then it splits it over more than one sheet.
Dim strFilePath As String, strFilename As String, vFullPath As Variant
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object
' Get a text file name
vFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")
If vFullPath = False Then Exit Sub 'User pressed Cancel on the open file dialog
Application.ScreenUpdating = False
' This gives us a full path name e.g. C:\folder\file.txt
' We need to split this into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(vFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(vFullPath).Name
' Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Set oRS = CreateObject("ADODB.RECORDSET")
' Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
While Not oRS.EOF
Sheets.Add
ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
Wend
oRS.Close
oConn.Close
Application.ScreenUpdating = True
End Sub
Je souhaite ouvrir un fichier texte qui contient 100000 lignes sous excel.
J'ai trouvé un post qui propose une macro mais lorsque j'execute cette macro j'ai le message d'erreur: Erreur de syntaxe dans la clause FROM
concernant la ligne: oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
Pouvez vous m'indiquer ou se trouve l'erreur car je ne la trouve pas.
Merci d'avance.
Ci dessous le code complet:
Sub ImportLargeFile()
' Imports text file into Excel workbook using ADO.
' If the number of records exceeds 65536 then it splits it over more than one sheet.
Dim strFilePath As String, strFilename As String, vFullPath As Variant
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object
' Get a text file name
vFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")
If vFullPath = False Then Exit Sub 'User pressed Cancel on the open file dialog
Application.ScreenUpdating = False
' This gives us a full path name e.g. C:\folder\file.txt
' We need to split this into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(vFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(vFullPath).Name
' Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""
Set oRS = CreateObject("ADODB.RECORDSET")
' Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
While Not oRS.EOF
Sheets.Add
ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
Wend
oRS.Close
oConn.Close
Application.ScreenUpdating = True
End Sub
