Thank you Asish. This works great!
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of ashish koul Sent: Friday, May 20, 2011 10:36 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Re: Macro for Converting txt to xls using Text Import Wizard Sub delimit_txt_file() Dim s As Workbook Dim fNameAndPath As Variant fNameAndPath = Application.GetOpenFilename _ (FileFilter:="Text Files (*.txt), *.txt", _ Title:="Select File To Be Opened") Workbooks.OpenText Filename:=fNameAndPath, Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="|" Set s = ActiveWorkbook s.Sheets(1).Cells.Select s.Sheets(1).Cells.EntireColumn.AutoFit Dim i As Long For i = 2 To s.Sheets(1).Range("a1").End(xlDown).Row s.Sheets(1).Cells(i, 3).Value = Application.Trim(s.Sheets(1).Cells(i, 3).Value & "|" & s.Sheets(1).Cells(i, 4).Value) If Right(s.Sheets(1).Cells(i, 3).Value, 1) = "|" Then s.Sheets(1).Cells(i, 3).Value = Left(s.Sheets(1).Cells(i, 3).Value, Len(s.Sheets(1).Cells(i, 3).Value) - 1) End If If Left(s.Sheets(1).Cells(i, 3).Value, 1) = "|" Then s.Sheets(1).Cells(i, 3).Value = Right(s.Sheets(1).Cells(i, 3).Value, Len(s.Sheets(1).Cells(i, 3).Value) - 1) End If Next i s.Sheets(1).Columns("D:D").Delete Shift:=xlToLeft End Sub On Thu, May 19, 2011 at 8:59 PM, Susan 1 <sunni...@gmail.com> wrote: Hi Jsin - I've attached an example of the formatted spreadsheet and the raw data txt file, the macro or VB I need would automatically format the txt file into the spreadsheet style. Thanks for you help on this! On Thu, May 19, 2011 at 4:54 AM, JsinSk <jsin...@gmail.com> wrote: I can guess how you want it formatted, but is it possible for you to attach a sample excel sheet with the desired tabs/columns how you want it? On May 18, 3:31 pm, Susan 1 <sunni...@gmail.com> wrote: > Hi ~ > > Wondering if you could assist me with the Text import wizard to configure > the columns and tab delimiter for the attached txt file. And then send me > the steps. > > I will be needing to do this every week to the same types of txt files, > importing into Excel and I'd like to create a Macro for this but cannot > figure out how to set the tab/columns. > > Thanks, > > Sunnie > > 2065409868_05-03-2011.txt > 6KViewDownload -- ---------------------------------------------------------------------------- ------ 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 <http://www.excel-macros.blogspot.com/> 4. Learn VBA Macros at http://www.quickvba.blogspot.com <http://www.quickvba.blogspot.com/> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com <http://exceldailytip.blogspot.com/> To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------- ------ 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 <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Ashish Koul <http://akoul.blogspot.com/> akoul.blogspot.com <http://akoul.wordpress.com/> akoul.wordpress.com My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> P Before printing, think about the environment. -- ---------------------------------------------------------------------------- ------ 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 <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- 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 <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
