Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

I have Excel spreadsheet with 6 columns and unknown # rows

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

1 Solution
Harisha M GCommented:
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.

elaborate the question..

AlexF777Author Commented:
    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

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

New feature and membership benefit!

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now