dcsimg
Results 1 to 34 of 34

Thread: [RESOLVED] Txt file convert to .xls

  1. #1

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Resolved [RESOLVED] Txt file convert to .xls

    hello.

    I wish to convert a txt file that is saved down in my folder into a .xls file.

    Before I do this however I need to sort by columns and I wish to sort by using the delimited file type - Other | key.

    Once created as a .xls file.

    I need to import the file into my workbook under worksheet "US Pennies"

    Any ideas how I can do this?

    File path the txt file is save under is

    N/My Documents.

  2. #2
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,458

    Re: Txt file convert to .xls

    What format is the text file in? If you have a file with fixed width data or delimited data, a simple VBA macro can import the data and then sort it for you. Post an example of the type of data you are importing. Remove any sensitive info and replace with dummy info if needed.

  3. #3

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    as requested....Samplev1.txt

  4. #4

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    does anyone know how to import this file?

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,062

    Re: Txt file convert to .xls

    hi,

    this is what I use with vb6, give it a try
    I used your sample text file and it imported to Excel no problem

    Code:
    Private Sub Command1_Click()
    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol
    
    Const ForReading = 1
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("E:\Test\samplev1.txt", _
    ForReading)
    
    'Excel has to be installed
    strExcelPath = "E:\TestImportToExcel.xls"
    
    
    Set objExcel = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
    On Error GoTo 0
    MsgBox "Excel application not found."
    
    End If
    On Error GoTo 0
    
    objExcel.Visible = True
    objExcel.Workbooks.Add
    
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "testing"
    
    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow
    
    End Sub
    hth
    chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    Thanks Chris.

    That worked a treat. How do i change it slightly so that I import the newly converted file into my workbook under worksheet 'Expense'

    Current workbook is called Kris expense.xlsm.

    Thanks

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,062

    Re: Txt file convert to .xls

    just change to the sheetname you want

    Code:
    '....
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "Expense"
    ......
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    Hello,

    Sorry for the delay in coming back. Been on holiday.


    I added the below code as below, but it simply creates a new workbook and re-names the tab as 'Expense'


    '....
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "Expense"
    ......


    I want to import the newly formatted txt file into my current work book.

    'Excel has to be installed
    strExcelPath = "E:\TestImportToExcel.xls"

    How do I change my code to reflect this?

  9. #9
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,458

    Re: Txt file convert to .xls

    I imported your sample text file in Excel with the Macro Recorder turned on and got the following code. I sorted on the "symbol" column but you can use whatever column(s) you want. The code generated is very similar to the basic part of the code I use for importing various text files. My code is enhances to import all files of a specific type and file name pattern in a specified folder to individual workbooks. You can also format the columns and perform other data processing as needed.
    Code:
    Code:
    Sub GetTextDataTest1()
    '
    ' GetTextDataTest1 Macro
    '
    
    '
        ChDir "C:\Temp\VBA\VBForums\Sample01"
        Workbooks.OpenText Filename:="C:\Temp\VBA\VBForums\Sample01\Samplev1.txt", _
            Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
            , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _
            :=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), _
            Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), Array(13, 2 _
            ), Array(14, 2), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 2), Array _
            (20, 5), Array(21, 5), Array(22, 5), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 2), _
            Array(27, 2), Array(28, 2), Array(29, 2), Array(30, 5), Array(31, 2), Array(32, 2), Array( _
            33, 2), Array(34, 2), Array(35, 2)), TrailingMinusNumbers:=True
        ActiveWindow.LargeScroll ToRight:=1
        ActiveWindow.SmallScroll ToRight:=-24
        Range("L1").Select
        ActiveWorkbook.Worksheets("Samplev1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Samplev1").Sort.SortFields.Add Key:=Range("L2:L4") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Samplev1").Sort
            .SetRange Range("A1:AI4")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: Txt file convert to .xls

    but it simply creates a new workbook and re-names the tab as 'Expense'
    you can try adding the code below to move the renamed sheet to the last sheet in the workbook containing the code, if that is not the desired workbook, change the workbook object to suit

    Code:
    objsheet.Move , ThisWorkbook.Sheets.Count
    objsheet.parent.close false   ' discard the newly opened workbook as it should now contain no sheets
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    Didn't seem to work buddy

    The code now produces this file. Which is correct. I just need to import it into my file under the tab 'expense'

    Workfile I need to import into.

    N:\My Documents\New Revenue P&L.xlsm"

    Name:  Capture1.jpg
Views: 101
Size:  40.3 KB

    Sub Importtxt()


    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol

    Const ForReading = 1

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("N:\My Documents\samplev1.txt", _
    ForReading)

    'Excel has to be installed
    strExcelPath = "N:\My Documents\New Revenue P&L.xlsm"


    Set objExcel = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
    On Error GoTo 0
    MsgBox "Excel application not found."

    End If
    On Error GoTo 0

    objExcel.Visible = True
    objExcel.Workbooks.Add

    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "US Equities"

    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow


    End Sub

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,062

    Re: Txt file convert to .xls

    Hi,

    this is one way to open an existing ExcelFile, if this is what your after ?
    if so then add the other code from Post#5

    here how to open
    Code:
    Private Sub Command1_Click()
     'or set a Ref. to Excel
    '   Dim xclApp As Excel.Application
    '   Dim xclWbk As Excel.Workbook
    '   Dim xclSht As Excel.Worksheet
    
       'oder hier für Late Binding ohne Verweis
       Dim xclApp As Object
       Dim xclWbk As Object
       Dim xclSht As Object
       Dim Excelfile As String
       
       Excelfile = "E:\TestImport.xls" '<-- this Excelfile already exists
       
          Set xclApp = CreateObject("Excel.Application")
          Set xclWbk = xclApp.Workbooks.Open(Excelfile)
          Set xclSht = xclWbk.Worksheets(1)
          
          
         xclApp.Visible = True
          
    'With xclSht
    '      Text1.Text = xclSht.Range("B3").Value
    '      'add more Cells to read
    'End With
    ' xclApp.DisplayAlerts = False
    '
    '      xclApp.ActiveWindow.Close SaveChanges:=False ' or True
    '      Set xclSht = Nothing
    '      Set xclWbk = Nothing
    '      xclApp.Quit
    '      Set xclApp = Nothing
    End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: Txt file convert to .xls

    Didn't seem to work buddy
    which particular part?
    what did happen? error? wrong result? something else?

    if you have the correct result in the wrong workbook, you can just move the sheet into someother workbook, to obtain the desired result
    if you are inserting the data into the sheet by cell then change the sheet in the data is inserted into

    if the vba code is in the same workbook as where you want the worksheet, then you can use the thisworkbook object to specify the target worksheets parent
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    Hello,

    Ok, nearly there on this.

    Below is my code.

    Last thing I need to change is this part of the coding...

    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "US Equities"

    I need this to read so that it imports into my current file.

    N:\My Documents\New Revenue P&L.xlsm

    How do I change the objexcel from an active book to my current file?



    Sub Importtxt()


    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol

    Const ForReading = 1

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("N:\My Documents\samplev1.txt", _
    ForReading)

    'Excel has to be installed
    strExcelPath = "N:\My Documents\New Revenue P&L.xlsm"


    Set objExcel = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
    On Error GoTo 0
    MsgBox "Excel application not found."

    End If
    On Error GoTo 0

    objExcel.Visible = True
    objExcel.Workbooks.Add

    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
    objSheet.Name = "US Equities"

    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: Txt file convert to .xls

    Code:
    Sub Importtxt()
    
    
    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol
    
    Const ForReading = 1
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("N:\My Documents\samplev1.txt", _
    ForReading)
    
    'Excel has to be installed
    strExcelPath = "N:\My Documents\New Revenue P&L.xlsm"
    
    
    Set objExcel = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
    On Error GoTo 0
    MsgBox "Excel application not found."
    
    End If
    On Error GoTo 0
    
    objExcel.Visible = True
    
    set objwb = objExcel.workbooks("New Revenue P&L.xlsm")
    '******************
    'if the workbook is not already open change the above to
    'set objwb = objExcel.workbooks.open(strExcelPath)
    '******************
    'if the code is this workbook you can use the
    'ThisWorkBook object instead of objwb below
    '*******************
    Set objSheet =objwb.sheets.add
    objSheet.Name = "US Equities"
    
    
    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    I changed the coding to read like the below, but stopped on the below line

    Run time error '424' Object required. I'm guessing I've missed something.


    Set objSheet = objwb.Sheets.Add
    objSheet.Name = "US Equities"





    Sub Importtxt()


    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol

    Const ForReading = 1

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("N:\My Documents\samplev1.txt", _
    ForReading)

    'Excel has to be installed
    strExcelPath = "N:\My Documents\New Revenue P&L.xlsm"


    Set objExcel = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
    On Error GoTo 0
    MsgBox "Excel application not found."

    End If
    On Error GoTo 0

    objExcel.Visible = True
    objExcel.Workbooks.Add

    Set objSheet = objwb.Sheets.Add
    objSheet.Name = "US Equities"


    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow


    End Sub

  17. #17

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    I changed the coding to read like the below, but stopped on the below line

    Run time error '424' Object required. I'm guessing I've missed something.


    Set objSheet = objwb.Sheets.Add
    objSheet.Name = "US Equities"





    Sub Importtxt()


    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol

    Const ForReading = 1

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("N:\My Documents\samplev1.txt", _
    ForReading)

    'Excel has to be installed
    strExcelPath = "N:\My Documents\New Revenue P&L.xlsm"


    Set objExcel = CreateObject("Excel.Application")
    If (Err.Number <> 0) Then
    On Error GoTo 0
    MsgBox "Excel application not found."

    End If
    On Error GoTo 0

    objExcel.Visible = True
    objExcel.Workbooks.Add

    Set objSheet = objwb.Sheets.Add
    objSheet.Name = "US Equities"


    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow


    End Sub

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: Txt file convert to .xls

    I'm guessing I've missed something.
    yes a workbook object
    see the 7 lines above that, that explain the line you need
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  19. #19

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    I've changed the code to this...

    but now I'm getting on line in bold.

    Run time errror 9.... Subscript out of range.

    objExcel.Visible = True
    Set objwb = objExcel.Workbooks("New Revenue P&L.xlsm")

    Set objSheet = objwb.Sheets.Add
    objSheet.Name = "US Equities"

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: Txt file convert to .xls

    is the workbook already open?

    if not see the alternatives listed in the code in post #15
    if you open the workbook, you have to decide if you want to leave it open or save and close
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  21. #21

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    Yes its already open

  22. #22
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: Txt file convert to .xls

    ok it would appear that as your objexcel is a different instance of excel to the one that the workbook is opened in, you can not set an object of the workbook as it in not open in the instance of objexcel, which as you are coding within excel is probably not required at all, the example you are working from was probably originally to work in vb6

    try like
    Code:
    strExcelPath = "N:\My Documents\New Revenue P&L.xlsm"
    On Error GoTo 0
    
    set objwb = workbooks("New Revenue P&L.xlsm")
    Set objSheet = objwb.Worksheets(1)
    objSheet.Name = "US Equities"
    
    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow
    note some lines have been removed
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  23. #23

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    Ok, I've run the below code.

    New error message on this line


    objSheet.Name = "US Equities"


    Run time error 1004 - application defined or object defined error.


    Sub Importtxt()


    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol

    Const ForReading = 1

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("N:\My Documents\samplev1.txt", _
    ForReading)

    strExcelPath = "N:\My Documents\New Revenue P&L.xlsm"
    On Error GoTo 0

    Set objwb = Workbooks("New Revenue P&L.xlsm")
    Set objSheet = objwb.Worksheets(1)
    objSheet.Name = "US Equities"

    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow

  24. #24
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: Txt file convert to .xls

    Set objSheet = objwb.Worksheets(1)
    i do not know why this should give an error, but it probably was not what you wanted to to anyway, me mixing the original code
    should add a now worksheet
    try changing to
    Code:
    Set objSheet = objwb.Worksheets.add
    you can set the position of the new sheet, by default it will be the first sheet

    i tested this bit, worked correctly without error
    Code:
    Set objwb = Workbooks("New Revenue P&L.xlsm")
    Set objSheet = objwb.Worksheets.Add
    objSheet.Name = "US Equities"
    i also tested the code as you had it, no error, but renamed an existing sheet, probably not a desired result
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  25. #25

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    I don't actually want to create a new sheet. Just want to import the txt file into a worksheet called US Equities.

    Changed as per your request above. Still breaking on the same line with same error message.

    Thanks



    Sub Importtxt()


    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol

    Const ForReading = 1

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("N:\My Documents\samplev1.txt", _
    ForReading)

    strExcelPath = "N:\My Documents\New Revenue P&L.xlsm"
    On Error GoTo 0

    Set objwb = Workbooks("New Revenue P&L.xlsm")
    Set objSheet = objwb.Worksheets.Add
    objSheet.Name = "US"

    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow

  26. #26
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: Txt file convert to .xls

    Just want to import the txt file into a worksheet called US Equities.
    does that sheet already exist?
    if so that would be your error

    try
    Code:
    Set objwb = Workbooks("New Revenue P&L.xlsm")
    Set objSheet = objwb.Worksheets("US Equities")
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  27. #27

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: Txt file convert to .xls

    Brilliant. That's worked. Thanks very much.

  28. #28

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: [RESOLVED] Txt file convert to .xls

    Hello.

    Have a small issue with the below coding....


    The actual size of my file is only 107kb, but when I tried to to import this file it took over 45 mins to import and increased the size of my workbook to over
    100mb.

    Does anyone know why my coding is doing this?


    Sub Importtxt()


    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol

    Const ForReading = 1

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("N:\My Documents\samplev1.txt", _
    ForReading)

    strExcelPath = "N:\My Documents\New Revenue P&La.xlsm"
    On Error GoTo 0

    Set objwb = Workbooks("New Revenue P&La.xlsm")
    Set objSheet = objwb.Worksheets("US Equities")


    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines) + 1
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    For icol = 1 To UBound(aline) + 1
    objSheet.Cells(irow, icol).Value = aline(icol - 1)
    Next ' icol
    Next ' irow

    End Sub

  29. #29
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: [RESOLVED] Txt file convert to .xls

    Does anyone know why my coding is doing this?
    certainly nothing obvious, though personally i would not use FSO to do this as vb has built in file i/o, that will do the same thing in this case

    i can only offer to test with a sample file if you want to post one, or you can email me an actual file and workbook to test with
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  30. #30

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: [RESOLVED] Txt file convert to .xls

    Morning.

    Here's a sample file you can test the coding on.... My work book is too big too send.
    Attached Files Attached Files

  31. #31
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: [RESOLVED] Txt file convert to .xls

    on testing with your sample, your original code (just changed path and file names), took 4.96875 seconds
    note i did use an empty workbook with no other data as i could not replicate your xls file, and the sample was smaller than the size you mentioned above
    making application.screenupdating to false, took 3.6875
    changing the code as below took 0.109375 seconds


    Code:
    Sub Importtxt()
    Dim objUser, strExcelPath, objExcel, objSheet, _
    objFSO, objFile, aline, aLines, irow, icol
    
    Const ForReading = 1
    Application.ScreenUpdating = False
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Set objFile = objFSO.OpenTextFile("N:\My Documents\samplev1.txt", _
    'ForReading)
    Set objFile = objFSO.OpenTextFile("c:\temp\samplev2.txt", _
    ForReading)
    
    strExcelPath = "c:\temp\sample2.xls"
    On Error GoTo 0
    
    'Set objwb = Workbooks("New Revenue P&La.xlsm")
    'Set objSheet = objwb.Worksheets("US Equities")
    
    Set objwb = Workbooks.Add             ' next 4 lines used for testing
    Set objSheet = objwb.Worksheets(1)
    objSheet.Name = "ES Equities"
    t = Timer
    aLines = Split(objFile.ReadAll, vbNewLine)
    For irow = 1 To UBound(aLines)
    aline = Split(aLines(irow - 1), "|") 'your delimiter vbTab;komma;SemiColon
    If UBound(aline) < 1 Then Exit For
    objSheet.Cells(irow, 1).Resize(, UBound(aline) + 1) = aline
    'For icol = 1 To UBound(aline) + 1
    'objSheet.Cells(irow, icol).Value = aline(icol - 1)
    'Next ' icol
    Next ' irow
    s = Timer - t         'next 4 lines for testing
    Debug.Print s
    objwb.SaveAs strExcelPath
    objwb.Close
    End Sub
    all excel files created containing your data were about 47k
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  32. #32

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: [RESOLVED] Txt file convert to .xls

    Its still running slow.... Taken over 10mins to import the same file as you. Could it be that my workbook is 43mb?

    Which is bizarre in itself because it should be nowhere near that... How do I check what causing the workbook to be so big?

  33. #33
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: [RESOLVED] Txt file convert to .xls

    How do I check what causing the workbook to be so big?
    copy each sheet in turn to a new workbook and save, then check the size, then add next sheet, would be the simplest method

    test how long to insert the text into a new workbook, and the size to see if your system is doing something different

    the speed of the code should not be affected by the size of the workbook, but could be seriously affected it the file or workbook is on a network drive, though if memory availability is limited it may slow down operation more

    what version of windows and excel, i was only testing the code on an old laptop with xp and excel 2000, i am sure i do not have any workbooks that size to test with
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  34. #34
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,513

    Re: [RESOLVED] Txt file convert to .xls

    i ran the same code (as in post #31) again on an i5 with w10 x64 and excel 2013
    workbook (.xlsx) size was 15k, time showed as 0.015625 seconds

    i have no idea why the code should be so slow on your machine
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width