Solved

Excel Import character set

Posted on 2013-01-10
3
301 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now