We help IT Professionals succeed at work.

Extract data from Excel worksheet using FSO

N M asked
Last Modified: 2018-09-10
Excel VBA: I need to extract a worksheet, row by row, pipe separated, all non-empty rows.
Currently I am able to select based on UsedRange

Public Sub Data(WorkSheetName)

    With Worksheets(WorkSheetName)
        .UsedRange: .UsedRange.Select
    End With

End Sub

Open in new window

How can I write to a file all non-empty rows (using FSO)?
Watch Question


How can I write to a file all non-empty rows (using FSO)?

FSO can't read Excel file, you got to use Excel objects to do that instead, use Excel.Application, Excel.Workbook, Excel.Worksheet instead.
N MConsultant


Thank you for the comment and the effort.
I am currently executing a Sub in an Excel where i want the extracted data, to make it simpler below is my code and I don't know how to recognise the change of row; otherwise, code works:

Public Sub Data(WorkSheetName)

    Dim xRange As Range
    Dim fso As Object
    Dim oFile As Object
    Dim PathFile
        PathFile = frmSettings.TextBox1.Text
            If Right(PathFile, 1) <> "\" Then PathFile = PathFile & "\"
        PathFile = PathFile & Application.ActiveWorkbook.Name & "." & WorkSheetName & ".txt"
    With Worksheets(WorkSheetName)
        Set xRange = .UsedRange
    End With

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFile = fso.CreateTextFile(PathFile)
        For Each cell In xRange.Cells
            p = p & cell.Value & frmSettings.ComboBox1.Value
            If >>row changes<<
                oFile.WriteLine p
                p = ""
            End If
    Set fso = Nothing
    Set oFile = Nothing

End Sub

Open in new window

Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
Top Expert 2014

If you concatenated the row range with a function like my Better Concatenate Function, you could accomplish the following:

1. Specify the pipe character as the inter-cell delimiter
2. Compare two rows with a simple string comparison operation
3. Output the pipe-delimited string if there was a difference

Even if you do not use my function, you can do some row/column slicing with the Index() function to get a vector of your values and then use the Join() function on the vector.  A vector is a 1D array.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions