Problem with exported text

[Matthew S]

The code below works all the way up to the point where it exports the data to
a text file. When I look within the text file I find that it includes bland
rows as well as the rows with the data. I did add a formula to column 1 so
that when they enter a specific value it always makes it a 1. Otherwise it
leaves the cell blank.

=IF(N8<>"",1,"") is the formula in column A starting on row 8.

Text file has the following as the results... which goes on for one hundred
rows which is how far down I copied the above formula.

1;;test;;;test;;;;;;;;s;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;
;;;;;;;;;;;;;;;;;;;;;;;

Sub Button1_Click()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This exports the data to a semicolon seperated text file.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim fileName As String
'Dim inputRow As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim EndRow As Long
Dim CellValue As String
Dim currPath As String
Dim slnum As String

slnum = UCase(Range("C8:C8").Value)
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
currPath = ActiveWorkbook.Path
fileName = currPath & "\" & slnum & "_Appendix1data.TXT"
With ActiveSheet.UsedRange
EndRow = .Cells(.Cells.Count).Row
End With
Open fileName For Output Access Write As #FNum

If EndRow >= 8 Then
For RowNdx = 8 To EndRow
'inputRow = ""
For ColNdx = 1 To 24
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = ""
Else
CellValue = Cells(RowNdx, ColNdx).Text
End If
' inputRow = inputRow & CellValue & ";"
Next ColNdx
'inputRow = Left(inputRow, Len(inputRow) - 1)
Print #FNum, ' inputRow
Next RowNdx

Else
GoTo EndMacro:
End If

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

MsgBox ("Formated Data is stored in " & fileName)

End Sub

 

[joel]

I made the code handle all posible situations which made in a littl
bit more complicated.


Sub Button1_Click()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This exports the data to a semicolon seperated text file.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim fileName As String
'Dim inputRow As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim EndRow As Long
Dim CellValue As String
Dim currPath As String
Dim slnum As String

slnum = UCase(Range("C8:C8").Value)
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
currPath = ActiveWorkbook.Path
fileName = currPath & "\" & slnum & "_Appendix1data.TXT"
Open fileName For Output Access Write As #FNum

With ActiveSheet

'get last row of data
EndRow = 0
For ColNdx = 1 To 24
LastRow = .Cells(Rows.Count, Colcount).End(xlUp).Row
If LastRow > EndRow Then
EndRow = LastRow
End If
Next ColNdx


If EndRow >= 8 Then
For RowNdx = 8 To EndRow
LastCol = .Cells(RowNdx, Columns.Count).End(xlToLeft).Column
If LastCol <> 1 Or .Cells("A" & RowNdx) <> "" Then

For ColNdx = 1 To LastCol
CellValue = Cells(RowNdx, ColNdx).Text

If ColNdx = 1 Then
OutputLine = CellValue
Else
OutputLine = OutputLine & ";" & CellValue
End If
Next ColNdx
Print #FNum, OutputLine
End If
Next RowNdx
End If
End With


EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

MsgBox ("Formated Data is stored in " & fileName)

End Sub

 

[Matthew S]

When I use that code I get nothing exported to the text file. I may need to
approach this differently rather than trying to fix the existing code, which
someone else did so I am not sure why they did it that way.

I basically need to choose the data starting in row 8 and for the first 24
columns and export that to a text file. I do have some formula's in the
cells as well as a dropdown list for them to choose from. Column A is always
going to be 1 when exporting so maybe if I could simply add a 1 for each row,
for that column that would be a better approach.

Thanks,

 

[joel]

I found two typo errors in the code. I saw a number of problems in th
original code and fixed them when I posted the code. this should giv
the results you are looking for.



Sub Button1_Click()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This exports the data to a semicolon seperated text file.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim fileName As String
'Dim inputRow As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim EndRow As Long
Dim CellValue As String
Dim currPath As String
Dim slnum As String

slnum = UCase(Range("C8:C8").Value)
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
currPath = ActiveWorkbook.Path
fileName = currPath & "\" & slnum & "_Appendix1data.TXT"
Open fileName For Output Access Write As #FNum

With ActiveSheet

'get last row of data
EndRow = 0
For ColNdx = 1 To 24
LastRow = .Cells(Rows.Count, ColNdx).End(xlUp).Row
If LastRow > EndRow Then
EndRow = LastRow
End If
Next ColNdx


If EndRow >= 8 Then
For RowNdx = 8 To EndRow
LastCol = .Cells(RowNdx, Columns.Count).End(xlToLeft).Column
If LastCol <> 1 Or .Range("A" & RowNdx) <> "" Then

For ColNdx = 1 To LastCol
CellValue = Cells(RowNdx, ColNdx).Text

If ColNdx = 1 Then
OutputLine = CellValue
Else
OutputLine = OutputLine & ";" & CellValue
End If
Next ColNdx
Print #FNum, OutputLine
End If
Next RowNdx
End If
End With


EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

MsgBox ("Formated Data is stored in " & fileName)

End Sub

 

[Matthew S]

Thanks. That looks like something I can use.

 

[Matthew S]

Joel,

I have one more question. On the exported text file it exports the data to
the column that has data and stops there. Is there a way to have it show all
24 columns even if the last 10 lets say do not contain anything?

Thanks,

 

[joel]

It can be done either way. Some people need the the extra columns an
some people don't want them. In you case where you have semicolon
seperating the columns I can add extra semicolons so ther are 2
semicolons on each line seperating the 24 columns.

 

[Matthew S]

So I could have

For ColNdx = 1 To 24 instead of For ColNdx = 1 to LastCol?

Then probably just comment out:

' LastCol = .Cells(RowNdx, Columns.Count).End(xlToLeft).Column
' If LastCol <> 1 Or .Range("A" & RowNdx) <> "" Then

 

[Matthew S]

I modified the code to show as it does below. So worked. Wanted to make sure
I could figure it out first so disregard last post.

Thanks for all your help.

'get last row of data
EndRow = 0
For ColNdx = 1 To 24
LastRow = .Cells(Rows.Count, ColNdx).End(xlUp).Row
If LastRow > EndRow Then
EndRow = LastRow
End If
Next ColNdx


If EndRow >= 8 Then
For RowNdx = 8 To EndRow
LastCol = .Cells(RowNdx, Columns.Count).End(xlToLeft).Column

'LastCol = .Cells(RowNdx, Columns.Count).End(xlToLeft).Column
If LastCol <> 1 Or .Range("A" & RowNdx) <> "" Then

For ColNdx = 1 To 24
CellValue = Cells(RowNdx, ColNdx).Text