By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,836 Members | 2,059 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,836 IT Pros & Developers. It's quick & easy.

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

P: 36
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
Expand|Select|Wrap|Line Numbers
  1. Public Sub ExportToTextFile(FName As String, SelectionOnly As Boolean, AppendData As Boolean)
  2.  
  3. Dim WholeLine As String
  4. Dim FNum As Integer
  5. Dim RowNdx As Long
  6. Dim ColNdx As Integer
  7. Dim StartRow As Long
  8. Dim EndRow As Long
  9. Dim StartCol As Integer
  10. Dim EndCol As Integer
  11. Dim CellValue As String
  12.  
  13.  
  14. Application.ScreenUpdating = False
  15. On Error GoTo EndMacro:
  16. FNum = FreeFile
  17.  
  18. If SelectionOnly = True Then
  19.     With Selection
  20.         StartRow = .Cells(1).Row
  21.         StartCol = .Cells(1).Column
  22.         EndRow = .Cells(.Cells.Count).End(xlUp).Row
  23.         EndCol = .Cells(.Cells.Count).Column
  24.     End With
  25. Else
  26.     With ActiveSheet.UsedRange
  27.         StartRow = 4
  28.         StartCol = .Cells(1).Column
  29.         EndRow = Cells(50000, "A").End(xlUp).Row
  30.         EndCol = .Cells(.Cells.Count).Column
  31.     End With
  32. End If
  33.  
  34. If AppendData = True Then
  35.     Open FName For Append Access Write As #FNum
  36. Else
  37.     Open FName For Output Access Write As #FNum
  38. End If
  39.  
  40. For RowNdx = StartRow To EndRow
  41.     WholeLine = ""
  42.     For ColNdx = StartCol To EndCol
  43.         If Cells(RowNdx, ColNdx).Value = "" Then
  44.             CellValue = Chr(34) & Chr(34)
  45.         Else
  46.            CellValue = Cells(RowNdx, ColNdx).Value
  47.         End If
  48.         WholeLine = WholeLine & CellValue & ","
  49.     Next ColNdx
  50.     WholeLine = Left(WholeLine, Len(WholeLine) - Len(","))
  51.     Print #FNum, WholeLine
  52. Next RowNdx
  53.  
  54. EndMacro:
  55. On Error GoTo 0
  56. Application.ScreenUpdating = True
  57. Close #FNum
  58.  
  59. End Sub
  60.  
  61.  
Jul 22 '10 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,608
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.
Jul 22 '10 #2

P: 36
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.
Jul 22 '10 #3

NeoPa
Expert Mod 15k+
P: 31,608
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.
Jul 22 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.