How to Export Excel Data to Textfile in a special format?

[prashantdixit]

Hi,

I am trying to export excel data to text file in a particular format.
The Format for each ROW of Excel is like

IMAN_ROOT/bin/import_file -f=<Column A> -type=<Column B> -d=<Column C> -ref=<Column D> -vb -log=<Column E>


Every Row of the excel should export in text with this format.

I have been able to export all the cell values for each row but not in this format.

Should i use delimiter for each cell value for each row.
Any help would be highly appreciated.

this is my code for exporting

Public Sub ExportToTextFile(FName As String, SelectionOnly As Boolean, AppendData As Boolean)
 
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
 
 
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
 
If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).End(xlUp).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = 4
        StartCol = .Cells(1).Column
        EndRow = Cells(50000, "A").End(xlUp).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If
 
If AppendData = True Then
    Open FName For Append Access Write As #FNum
Else
    Open FName For Output Access Write As #FNum
End If
 
For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Value
        End If
        WholeLine = WholeLine & CellValue & ","
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(","))
    Print #FNum, WholeLine
Next RowNdx
 
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
 
End Sub
 
 

[NeoPa]

prashantdixit: IMAN_ROOT/bin/import_file -f=<Column A> -type=<Column B> -d=<Column C> -ref=<Column D> -vb -log=<Column E>

I have no idea what this is supposed to be describing. Perhaps some example data formatted as you expect it might help.

[prashantdixit]

I have an Excel Worksheet which has 6 Columns
Column A, Column B, Column C, Column D, Column E, Column F.

Now All these Column contain data.
I have got Export Button on the Excel sheet.
1. When user clicks on Export Button then Dialog box open to Save Excel Data in textfile(.txt format only)
2. Let say i have 1000 rows of data corresponding to each column.
3. So in each line of text file , Data from EACH ROW should get exported in this format.
For Row 1
IMAN_ROOT/bin/import_file -f=<Cells(1,"A")> -type=<Cells(1,"C")> -d=<Cells(1,"D")> -ref=<Cells(1,"E")> -vb -log=<Cells(1,"F")>

Where
<Cells(1, "A")> is Cell value in First Row corresponding to Column A,

<Cells(1, "C")> is Cell value in First Row corresponding to Column C.

PS:
1. I need to values corresponding to Skip Column B for all rows as it is not being used in Format while exporting it to Text file
2. This Text format is basically used to generate a script based on Excel data.

I hope this will help you understanding the problem.
The code has already been posted above through which I have been able to export all the cell values for each row but not in this format.

[NeoPa]

NeoPa: Perhaps some example data formatted as you expect it might help.

You don't have to pay any attention to what I suggest, but I can't help thinking it would be sensible.