problem with macro
I found some code that imports a text file to the open workbook on MSKB:
Sub ImportTextFile() Dim DestBook As Workbook, SourceBook As Workbook Dim DestCell As Range Dim RetVal As Boolean ' Turn off screen updating. Application.ScreenUpdating = False ' Set object variables for the active book and active cell. Set DestBook = ActiveWorkbook Set DestCell = ActiveCell ' Show the Open dialog box. RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt") ' If Retval is false (Open dialog canceled), exit the procedure. If RetVal = False Then Exit Sub ' Set an object variable for the workbook containing the text file. Set SourceBook = ActiveWorkbook ' Copy the contents of the entire sheet containing the text file. \ Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy ' Activate the destination workbook and paste special the values ' from the text file. DestBook.Activate DestCell.PasteSpecial Paste:=xlValues ' Close the book containing the text file. SourceBook.Close True This works fine. I added some column headers and resized the columns by adding this which I got from using the recorder: Rows("1:1").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1").Select ActiveCell.FormulaR1C1 = "Item" Range("B1").Select ActiveCell.FormulaR1C1 = "Direct" Range("C1").Select ActiveCell.FormulaR1C1 = "C" Range("D1").Select ActiveCell.FormulaR1C1 = "Co" Range("E1").Select ActiveCell.FormulaR1C1 = "Description" Range("F1").Select ActiveCell.FormulaR1C1 = "UN" Range("G1").Select ActiveCell.FormulaR1C1 = "LYTD" Range("H1").Select ActiveCell.FormulaR1C1 = "YTD" Range("I1").Select ActiveCell.FormulaR1C1 = "MTD" Range("J1").Select ActiveCell.FormulaR1C1 = "M1" Range("K1").Select ActiveCell.FormulaR1C1 = "M2" Range("L1").Select ActiveCell.FormulaR1C1 = "M3" Range("M1").Select ActiveCell.FormulaR1C1 = "O/H" Range("N1").Select ActiveCell.FormulaR1C1 = "Min" Range("O1").Select ActiveCell.FormulaR1C1 = "Max" Range("P1").Select ActiveCell.FormulaR1C1 = "O/O" Range("Q1").Select ActiveCell.FormulaR1C1 = "Last Sold" Range("R1").Select 'size columns Cells.Columns.AutoFit This part works OK too. What I would like to do is include code below ( from the recorder) that lays the file out. I think it should be inserted after the SetSoucerBook = ActiveworkBook but I'm not sure. Also I know the first two line below aren't correct. What would I put there to be able to make it work with the above code? My guess is that the code below could be written in a much shorted version. The code from the recorder looks like this: ChDir "C:\PrintOutput" Workbooks.OpenText Filename:="C:\PrintOutput\report014.txt", Origin:=437, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 2 _ ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _ , 1), Array(16, 1), Array(17, 1)), TrailingMinusNumbers:=True Thanks for taking the time to look. gls858 |
All times are GMT +1. The time now is 01:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com