Problem with exported text
 

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
--
Matt

Problem with exported text
 

I made the code handle all posible situations which made in a little
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189337

http://www.thecodecage.com/forumz/chat.php

Problem with exported text
 

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,
--
Matt




"joel" wrote:


I made the code handle all posible situations which made in a little
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189337

http://www.thecodecage.com/forumz/chat.php

.

Problem with exported text
 

I found two typo errors in the code. I saw a number of problems in the
original code and fixed them when I posted the code. this should give
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189337

http://www.thecodecage.com/forumz/chat.php

Problem with exported text
 

Thanks. That looks like something I can use.
--
Matt




"joel" wrote:


I found two typo errors in the code. I saw a number of problems in the
original code and fixed them when I posted the code. this should give
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189337

http://www.thecodecage.com/forumz/chat.php

.

Problem with exported text
 

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,
--
Matt




"joel" wrote:


I found two typo errors in the code. I saw a number of problems in the
original code and fixed them when I posted the code. this should give
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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189337

http://www.thecodecage.com/forumz/chat.php

.

Problem with exported text
 

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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189337

http://www.thecodecage.com/forumz/chat.php

Problem with exported text
 

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



--
Matt




"joel" wrote:


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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189337

http://www.thecodecage.com/forumz/chat.php

.

Problem with exported text
 

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

--
Matt




"Matthew S" wrote:

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



--
Matt




"joel" wrote:


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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=189337

http://www.thecodecage.com/forumz/chat.php

.