I have Excel spreadsheet with 6 columns and unknown # rows

Posted on 2005-04-14
Last Modified: 2010-05-02
I would like to have a flat file with 2 columns, where

column1 would contain Excel cols 1,3 and 5
column2 would contain Excel cols 2,4 and 6

Thanks in advance

Question by:AlexF777
    LVL 37

    Expert Comment

    by:Harisha M G
    Hi AlexF777,
        Flat file means? Text file? or another Excel sheet? Do you want it to be done in VBA?
    Do this...
        Goto Excel --> Insert --> Worksheet.
        In the new sheet..
        A1 = Sheet1!A1 & Sheet1!A3 & Sheet1!A5
        B1 = Sheet1!A2 & Sheet1!A4 & Sheet1!A6
        Now copy both the formulas to the bottom to all the rows where you have data.
        Select both the columns and copy.
        Goto a text editor like notepad or wordpad and paste.

    LVL 1

    Expert Comment

    elaborate the question..


    Author Comment

        Flat file means text file.
         I want it done in VB.
         Not manually.

    For example:    Col1  Col2  Col3  Col4  Col5  Col6
                             A      B       C      D       E     F
    Would give me a flat text file with
                             A      B
                             C      D
                             E       F

    LVL 35

    Accepted Solution

    Hi Alex,

    I think you're looking for something like:

    Function AlexF777CreateFlatFileFromExcel(ByVal ExcelFileName As String, _
      ByVal TextFileName As String) As Boolean
     Dim vFF As Long
     Dim xl As New Excel.Application, xlRow As Excel.Range
     Dim xlWS As Excel.Worksheet
     Dim Col1() As String, Col2() As String, Cnt As Long, Max1 As Long, Max2 As Long
     Dim i As Long
     xl.Visible = False
     xl.ScreenUpdating = False
     xl.Workbooks.Open ExcelFileName
     Cnt = 0
     Cnt = 0
     For Each xlRow In xl.ActiveSheet.UsedRange.Rows
      For i = 1 To xlRow.Cells.Count
       If i / 2 <> Int(i / 2) Then
        ReDim Preserve Col1(Cnt)
        ReDim Preserve Col2(Cnt)
        Col1(Cnt) = xlRow.Cells(i).Text
        Cnt = Cnt + 1
        If Len(xlRow.Cells(i).Text) > Max1 Then Max1 = Len(xlRow.Cells(i).Text)
        Col2(UBound(Col2)) = xlRow.Cells(i).Text
        If Len(xlRow.Cells(i).Text) > Max2 Then Max2 = Len(xlRow.Cells(i).Text)
       End If
      Next i
     Next xlRow
     vFF = FreeFile
     Open TextFileName For Output As #vFF
     For i = 0 To Cnt - 1
      If Len(Col1(i)) < Max1 Then Col1(i) = String(Max1 - Len(Col1(i)), " ") & Col1(i)
      If Len(Col2(i)) < Max2 Then Col2(i) = String(Max2 - Len(Col2(i)), " ") & Col2(i)
      Print #vFF, Col1(i) & Col2(i)
     Next i
     Close #vFF
     xl.ScreenUpdating = True
     Set xl = Nothing
     Set xlRow = Nothing
     Set xlWS = Nothing
    End Function

    Call it by using a line like:
     AlexF777CreateFlatFileFromExcel "C:\test.xls", "C:\alexf.txt"

    It does require an early binding reference to Microsoft Excel.  If you'd rather it be late binding, or another method completely, please let me know and I can hopefully make the changes you need.  Let me know how it works!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    731 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