Link to home
Start Free TrialLog in
Avatar of N M
N MFlag for Luxembourg

asked on

Extract data from Excel worksheet using FSO

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)?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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.
Avatar of N M

ASKER

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
        .UsedRange.Select
    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
        Next
    
        oFile.Close
    
    Set fso = Nothing
    Set oFile = Nothing

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
https://www.experts-exchange.com/articles/7811/A-Better-Concatenate-Function.html

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.