Send a suggestion!

We're building a brand new version of the site, and we'd love to hear your ideas

Members

Technology Zones

IBM Learning Center

Articles

Hosted By

MaximumASP

Info
Rated
Read 86,716 times
5 comments

Contents

Downloads

Related Categories

Reading Text File and Export To Excel

gigsvoo

This is a sample code that reads a file (text, dat any ASCII), then export to Excel format spreadsheet accroding to some filtering.

Attribute VB_Name = "Module1"
Sub ExtractName()
   'Establish database connection
   Dim Conn As New ADODB.Connection
   Dim Rs As New ADODB.Recordset
   Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=eC_recipient.mdb"
   Conn.Open
   With Rs
       .CursorType = adOpenStatic
       .CursorLocation = adUseServer
       .LockType = adLockReadOnly
       .ActiveConnection = Conn
       .Open "SELECT * FROM eC_recipient", , , , adCmdText
   End With
   'Initialize workbook variable
   Dim SourceCol As Range
   Dim ScolCount, colCounter As Long
   On Error Resume Next
   'Preparing a new worksheet for data dumping
   Application.DisplayAlerts = False
   ActiveWorkbook.Worksheets("Results").Delete
   Application.DisplayAlerts = True
   DeleteWorksheet = Not CBool(Err.Number)
   'Count total numbers of worksheet
   Dim i, count As Integer
   Dim lastname As String
   For i = 1 To ActiveWorkbook.Worksheets.count
       count = count + 1
   Next
   lastname = ActiveWorkbook.Worksheets.Item(count).Name
'    MsgBox CStr(count)
   Dim wksNewSheet As Excel.Worksheet
   Set wksNewSheet = Worksheets.Add
   'Name and allocate the new worksheet
   With wksNewSheet
       .Name = "Results"
       .Move After:=Worksheets(lastname)
   End With
   'Make a count of how many cells have to process
   Worksheets(1).Activate
   Set SourceCol = Columns("A")
   For colCounter = 1 To SourceCol.Rows.count
       ScolCount = ScolCount + 1
   Next
'    MsgBox CStr(ScolCount)
   'Start processing
   Dim tempC, tempStr As String
   For i = 1 To ScolCount
       Set curcell = Worksheets("Results").Cells(i, 1)
       Set curcell2 = Worksheets("Results").Cells(i, 2)
       If SourceCol.Cells(i).Value <> "" Then
           tempC = UCase(Replace(SourceCol.Cells(i).Value, Mid(SourceCol.Cells(i).Value, 1, 33), ""))
           Rs.MoveFirst
           Do While Not Rs.EOF
               tempStr = UCase(Replace(Rs.Fields(0).Value, Mid(Rs.Fields(0).Value, 1, 6), ""))
               If tempC = tempStr Then
                   curcell.Value = tempC
                   curcell2.Value = Rs.Fields(1).Value
                   GoTo Exit_Loop
               End If
               Rs.MoveNext
           Loop
           curcell.Value = tempC
           curcell2.Value = "Unknown"
       End If
Exit_Loop:
   Next
   If Err.Number <> 0 Then
       MsgBox Err.Number + " " + Err.Description + " " + Err.Source
   End If
   MsgBox CStr(ScolCount) + " records completed!", vbInformation + vbOKOnly, "Completed!"
   Worksheets("Results").Activate
   Columns("A:A").EntireColumn.AutoFit
   Columns("B:B").EntireColumn.AutoFit
End Sub

Comments

Read Comments | Post Comment

  • Re: [1897] Reading Text File and Export To Excel

    Posted by BarbaMariolino on 13 Jun 2008

    Hi,

    Maybe you should try to use some third party component like GemBox.Sp...

  • need help

    Posted by vinnay on 09 Mar 2005

    does anyone know how to create a macro in excel which can read a column. Then sorts the different groups in the column to then display it in a graph? Also how to identify unknowns which arent meant t...

  • Need some help plz

    Posted by nandini_n_arun on 06 Feb 2005

    Need to compare two separate text files after exporting them into Excel. Have exported the text files as two separate excel files. The issue is each of these files after exported to excel have differe...

  • Reading Text File and Export To Excel

    Posted by nandini_n_arun on 06 Feb 2005

    First have a button in excel, on button click event
    try the following

    myfile = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Select TXT FILE", "Select") ' basically gets the txt file...

  • Woking...?!...

    Posted by dimitret on 05 Jun 2002

    I copied this, pasted it, but when I try to run it I get an error...

    Attribute is highlighted / Compile Error, Syntax Error


    Any ideas....?