Good morning,
This macro opens the named text file, copies the data, then goes to the worksheet and pastes it in. The problem I am having is that many of my Item Numbers are not being pasted in whole rather the first 8 characters g in the Item Number field and the other 2 characters go in the Description field. As an example item 269112STUD is formatted and listed correctly in the text file but when it gets pasted into the spreadsheet it ends up as 269112ST and the UD ends up as the first characters in the description field. I thought setting the column width larger might help but got the same results. Maybe I didn't define the column width in the correct place in the macro?
Any suggestions or thoughts would be gratefully accepted, I really appreciate the people on this forum always being so willing to help and share.
Thanks much
Bill
This macro opens the named text file, copies the data, then goes to the worksheet and pastes it in. The problem I am having is that many of my Item Numbers are not being pasted in whole rather the first 8 characters g in the Item Number field and the other 2 characters go in the Description field. As an example item 269112STUD is formatted and listed correctly in the text file but when it gets pasted into the spreadsheet it ends up as 269112ST and the UD ends up as the first characters in the description field. I thought setting the column width larger might help but got the same results. Maybe I didn't define the column width in the correct place in the macro?
Any suggestions or thoughts would be gratefully accepted, I really appreciate the people on this forum always being so willing to help and share.
Thanks much
Bill
Code:
Sub Import_Text_File_to_Estimating_Tool() Application.DisplayAlerts = False
Application.ScreenUpdating = False '
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.PrintCommunication = False
'Macro to open the text file dumped from the AS400, copy all the data, paste it into the Item Master worksheet in the Estimating Tool Workbook
'Set format as Text for all columns to preserve leading zeros in item numbers
Selection.NumberFormat = "@"
Columns("A:G").HorizontalAlignment = xlLeft
'Open the text file dumped from the AS400 and preserves leading zeroes in the Item field
Workbooks.OpenText filename:= _
"H:\Estimator\QPQUPRFIL_GJH_QDFTJOBD*.txt" ', Origin:=65001 _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array( _
3, 1), Array(14, 1), Array(20, 1), Array(33, 1), Array(69, 1), Array(78, 1), Array(89, 1), _
Array(101, 1), Array(103, 1), Array(111, 1)), TrailingMinusNumbers:=True
Cells.Select 'Select all the cells
Selection.Copy 'Copy the selection
Windows("Estimator.xlsm").Activate
Worksheets("Item Master").Activate
Worksheets("Item Master").Range("A:C").ColumnWidth = 15
ActiveSheet.Paste Destination:=Worksheets("Item Master").Range("A1:N88888")
'Cells.Select 'Select all cells in the Item Master worksheet in Estimating Tool
Application.DisplayAlerts = False
ActiveWorkbook.Save 'Save the workbook with the new info pasted in.
Columns.AutoFit 'Autofit Columns to display info properly
Columns("A:C").HorizontalAlignment = xlLeft
Columns.AutoFit 'Autofit Columns to display info properly
ActiveWorkbook.Save 'Save the workbook with the new info pasted in.
Application.DisplayAlerts = True 'Turn on alerts like Save Changes or Save information in Paste Buffer
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.PrintCommunication = True
End Sub
Last edited: