Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Import character set

Posted on 2013-01-10
3
Medium Priority
?
334 Views
Last Modified: 2013-01-16
I am importing multiple text files in to excel using the code below but I need to change the character set of the origin file as the files aren't importing the test correctly. If I manually import the file I can change it and its file.

Sub ReadFilesIntoActiveSheet()
    Dim fso As FileSystemObject
    Dim folder As folder
    Dim file As file
    Dim FileText As TextStream
    Dim TextLine As String
    Dim Items() As String
    Dim i As Long
    Dim cl As Range

    ' Get a FileSystem object
    Set fso = New FileSystemObject

    ' get the directory you want
    Set folder = fso.GetFolder("C:\tmp\")

    ' set the starting point to write the data to
    Set cl = ActiveSheet.Cells(1, 1)

    ' Loop thru all files in the folder
    For Each file In folder.Files
   
        ' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)

        ' Read the file one line at a time
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine

            ' Parse the line into | delimited pieces
            Items = Split(TextLine, "|")

            ' Put data on one row in active sheet
            For i = 0 To UBound(Items)
                cl.Offset(0, i).Value = Items(i)
            Next

            ' Move to next row
            Set cl = cl.Offset(1, 0)
        Loop

        ' Clean up
        FileText.Close
    Next file

    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing

End Sub
0
Comment
Question by:miller75
  • 2
3 Comments
 
LVL 14

Expert Comment

by:Farzad Akbarnejad
ID: 38765771
Hello,
Change

Set FileText = file.OpenAsTextStream(ForReading)

to

Set FileText = file.OpenAsTextStream(ForReading, -1)    ' -1 is for Unicode

-FA
0
 

Accepted Solution

by:
miller75 earned 0 total points
ID: 38766110
I tried that but its imported in Chinese!
0
 

Author Closing Comment

by:miller75
ID: 38781991
I converted the files first using another program
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question