Extract data from Excel worksheet using FSO
Currently I am able to select based on UsedRange
Public Sub Data(WorkSheetName)
With Worksheets(WorkSheetName)
.UsedRange: .UsedRange.Select
End With
End Sub
How can I write to a file all non-empty rows (using FSO)?
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.
Consultant
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
Social distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
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 AccessWhy 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.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
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