Hi Paul -
I tried the code you provided without making any modifications and I
had a few issues.
Just for a bit of background: The file I am using for testing has 925
rows, 11 rows should be excluede since the dates in column T do not
match column E. Txt file should have 914 rows of data.
The macro created the new tab, but a majority of the data got added
into the new tab, 897 rows to be exact.
The macro was created fine, but there were only 28 records in the txt
file. The excel rows that were chosen for the txt file were also off,
since there were some rows in the txt file where column T did not
match column E.
I hope my descriptions are a bit helpful in trying to troubleshoot.
If there is any other information that you need please let me know.
Thanks again!
On May 21, 11:20 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> did you look at my code?
> if you change
> StrDelim = "|"
> to
> StrDelim = ""
>
> it should do what you want.
> if you don't NEED to copy the records where T and E are different, you can
> comment out that section.
>
> Paul
>
> ________________________________
> From: Erick C <boricua2...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Fri, May 21, 2010 11:55:43 AM
> Subject: Re: $$Excel-Macros$$ Need macro help - Need to sort, cut and paste
>
> Hi Paul -
> Thanks for the reply. Let me attempt to answer your questions and
> hopefully clarify a bit.
> 1. I am using Excel 2003
>
> 2. All of the steps prior to the code I already had were serving only
> one purpose. I want to write the text file using only rows where the
> date in column T equals the date in column E. In my mind (and
> probably my lack of experience) the only way I could think to
> accomplish this was to identify the rows where the dates did not match
> and cut and paste them out of the worksheet. There may be a much
> simpler way to do this with code, but I do not know what it would be.
>
> 3. I do understand the code that I am using does not have spaces or
> delimiters between the values. I am using this text file in place of
> an import file that we used to have generated externally. The program
> that this text file is imported into is very touchy, so I needed to
> make a text file that exactly matched the format and layout of our old
> text files. These files had no spaces or delimiters, so I have to
> leave them out.
>
> I hope this clears up the questions that you had. Thanks again for
> the response.
>
> On May 21, 7:39 am, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>
>
> > I have a few questions.
>
> > What version of Excel are you using?
>
> > You said in step 6, you "cut" the records that AG is null
> > did you really man "cut", as in: removed from sheet1?
>
> > also, you're writing a file with :
> > a.WriteLine s1 & s2 & s3 & s4 & s5 & s6
> > but the values have no spaces or delimiters between them.
>
> > I think I can do the entire process with a single macro:
> > In my test data, it processed 65,536 records of 74 columns
> > in 8 minutes, 32 seconds.
> > '-------------------------------------------------------------------------
> > Option Explicit
> > Sub CvtTotext()
> > Dim LastRow, LastCol
> > Dim R, C, RowNI
> > Dim FileName, FilePath, fs, f, StrOut, StrDelim
> > Dim TElapsed, TMin, TSec, TStart, TStop
> > '------------------------------------------
> > TStart = Timer
> > Application.ScreenUpdating = False
> > '------------------------------------------
> > ' Create "Not Imported" sheet
> > '------------------------------------------
> > On Error Resume Next
> > Application.DisplayAlerts = False
> > Err.Clear
> > Sheets("Not Imported").Select
> > If (Err.Number = 0) Then Sheets("Not Imported").Delete
> > Sheets.Add After:=Sheets(Sheets.Count)
> > ActiveSheet.Name = "Not Imported"
> > On Error GoTo 0
> > Application.DisplayAlerts = True
> > '------------------------------------------
> > Sheets("sheet1").Select
> > LastRow = ActiveCell.SpecialCells(xlLastCell).Row
> > LastCol = ActiveCell.SpecialCells(xlLastCell).Column
> >
> > For C = 1 To LastCol
> > Sheets("Not Imported").Cells(1, C) = ActiveSheet.Cells(1, C).Value
> > Next C
> >
> > FileName = InputBox(" Enter Text File Name ", "Text File")
> > FilePath = "g:\Treas\Erick\Price File Test\" & FileName & ".txt"
> > StrDelim = "|"
> > Set fs = CreateObject("Scripting.FileSystemObject")
> > If (fs.fileexists(FilePath)) Then fs.deletefile FilePath
> > Set f = fs.CreateTextFile(FilePath, True)
> > Application.StatusBar = "Processing " & LastRow & " records)"
> > RowNI = 1
> > For R = 2 To LastRow
> > If (R Mod 1000 = 0) Then
> > Application.StatusBar = "Processing " & R & " of " & LastRow
> > End If
> > If (ActiveSheet.Range("BM" & R).Value <> ActiveSheet.Range("AE" &
> > R).Value) Then
> > RowNI = RowNI + 1
> > For C = 1 To LastCol
> > Sheets("Not Imported").Cells(RowNI, C) =
> > ActiveSheet.Cells(R, C).Value
> > Next C
> > Else
> > StrOut = Cells(R, 32).Value '32
> > StrOut = StrOut & StrDelim & Cells(R, 3).Value '3
> > StrOut = StrOut & StrDelim & Format(Cells(R, 20).Value,
> > "mmddyyyy") '20
> > StrOut = StrOut & StrDelim & Format(Cells(R, 21).Value *
> > 100000000, "000000000000#") '21
> > StrOut = StrOut & StrDelim & " "
> > StrOut = StrOut & StrDelim & "0000000000000"
> > StrOut = StrOut & StrDelim & " "
> > StrOut = StrOut & StrDelim & " "
> > StrOut = StrOut & StrDelim & " "
> > StrOut = StrOut & StrDelim & "0000000000"
> > StrOut = StrOut & StrDelim & Format(Cells(R, 22).Value,
> > "mmddyyyy") '22
> > StrOut = StrOut & StrDelim & "BNYM"
> > StrOut = StrOut & StrDelim & "0000000000000"
> > StrOut = StrOut & StrDelim & " "
> >
> > f.Writeline StrOut
> > End If
> > Next R
> > f.Close
> > '------------------------------------------
> > Application.StatusBar = False
> > Application.ScreenUpdating = True
> > '------------------------------------------
> > TStop = Timer
> > TElapsed = TStop - TStart
> > TMin = TElapsed \ 60
> > TSec = TElapsed Mod 60
> > MsgBox "Finished" & Chr(13) & TMin & " min " & TSec & " sec"
> > '------------------------------------------
> > End Sub
>
> > Paul
>
> > ________________________________
> > From: Erick C <boricua2...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Thu, May 20, 2010 11:35:21 AM
> > Subject: $$Excel-Macros$$ Need macro help - Need to sort, cut and paste
>
> > Good morning everybody -
>
> > I am still quite a novice when it comes to VB and writing codes, so
> > any help I can get would be greatly appreciated. I am currently
> > working on a code that takes data from a xls file that I download and
> > convert it to a text file. I found an older posting with an example
> > that I modified a bit to meet my format requirements. The formatting
> > is working fine, but there is some manual cleanup that has to be done
> > prior to running the macro that I would like to add to the macro.
> > What I am trying to add to my current macro (in this order):
>
> > 1. Sort the file by column 20 ascending (column T)
> > 2. In cell AG2 (column 33), enter formula "=if(T2=E2, "OK","")
> > 3. Copy formula from AG2 down column AG to last record
> > 4. Sort the file by column 33 (column AG)
> > 5. Create a new worksheet named "Not Imported"
> > 6. Back in Sheet 1, cut any record where the AG cell is null and paste
> > into Not Imported tab
> > 7. Back in sheet 1, continue with the code I have (below):
>
> > Public Sub CvtTotext()
> > LastRow = Cells.Find("*", ActiveCell.SpecialCells(xlLastCell), , ,
> > xlByRows, xlPrevious).Row
>
> > Filename = InputBox(" Enter Text File Name ", "Text File")
> > FilePath = "g:\Treas\Erick\Price File Test\" & Filename & ".txt"
>
> > Set fs = CreateObject("Scripting.FileSystemObject")
> > Set a = fs.CreateTextFile(FilePath, True)
>
> > For Row = 3 To LastRow
> > s1 = Cells(Row, 32).Value
> > s2 = Cells(Row, 3).Value
> > s3 = Format(Cells(Row, 20).Value, "mmddyyyy")
> > s4 = Format(Cells(Row, 21).Value * 100000000, "000000000000#")
> > s5 = " "
> > s6 = "0000000000000"
> > s7 = " "
> > s8 = " "
> > s9 = " "
> > s10 = "0000000000"
> > s11 = Format(Cells(Row, 22).Value, "mmddyyyy")
> > s12 = "BNYM"
> > s13 = "0000000000000"
> > s14 = " "
>
> > a.WriteLine s1 & s2 & s3 & s4 & s5 & s6 & s7 & s8 & s9 & s10 &
> > s11 & s12 & s13 & s14
> > Next
>
> > a.Close
>
> > MsgBox FilePath & " Created."
>
> > End Sub
>
> > If there are existing examples that do something close to what I am
> > trying to do, I would be more than happy to try to rip them apart
> > myself, I know everyone is quite busy. Any help I can get would be
> > greatly appreciated! Thanks!
>
> > --
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links
> > :http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email to excel-macros@googlegroups.com
>
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,800 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know
> > they can subscribe to group
> > athttp://groups.google.com/group/excel-macros/subscribe
>
> > --
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links
> > :http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
