Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data from exported file read as text, when edited becomes a # (pic | Excel Discussion (Misc queries) | |||
Exported from Access - text has a character before it | Excel Discussion (Misc queries) | |||
Need quotes around exported tab delimited text | Excel Discussion (Misc queries) | |||
Dates exported as text | Excel Programming | |||
why are there quotes in my exported text file? | Excel Discussion (Misc queries) |