Avatar of N M
N M
Flag 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)?
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
aikimark

8/22/2022 - Mon
Ryan Chong

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 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
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
aikimark

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes