Good Afternoon,
I have a huge .lis file that I've saved as a txt file and I would like to import this into excel. Now I've found some code that allows me to import this over multiple sheets, which is perfect. However, everything gets dumped into the first column.
Part of the problem is that the file has row headings every page.
What I would like to do is delete the headings, import this massive beast into excel and have the columns separated appropriately. A space separates the data within the txt file to define what is a new column. The headings are defined as not beginning with a number.
This is the code that I found on the site already for the importation.
Please let me know if I haven't explained my situation properly. Thank you in advance for your time and help.
Regards,
Chris
-------------------------------------------------------------------------
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, strFullPath As String
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object
'Get a text file name
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...")
If strFullPath = "False" Then Exit Sub 'User pressed Cancel on the open file dialog
'This gives us a full path name e.g. C:tempfolderfile.txt
'We need to split this into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(strFullPath).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
End Sub
I have a huge .lis file that I've saved as a txt file and I would like to import this into excel. Now I've found some code that allows me to import this over multiple sheets, which is perfect. However, everything gets dumped into the first column.
Part of the problem is that the file has row headings every page.
What I would like to do is delete the headings, import this massive beast into excel and have the columns separated appropriately. A space separates the data within the txt file to define what is a new column. The headings are defined as not beginning with a number.
This is the code that I found on the site already for the importation.
Please let me know if I haven't explained my situation properly. Thank you in advance for your time and help.
Regards,
Chris
-------------------------------------------------------------------------
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, strFullPath As String
Dim lngCounter As Long
Dim oConn As Object, oRS As Object, oFSObj As Object
'Get a text file name
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...")
If strFullPath = "False" Then Exit Sub 'User pressed Cancel on the open file dialog
'This gives us a full path name e.g. C:tempfolderfile.txt
'We need to split this into path and file name
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path
strFilename = oFSObj.GetFile(strFullPath).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
End Sub