Consulting

Results 1 to 5 of 5

Thread: VBA - Flat Text Import Changes

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    VBA - Flat Text Import Changes

    We have a mission critical spreadsheet that imports a lot of flat text from a desgin program and then brings it in to this spreadsheet.
    We recently updated the design software, which we do once a year and have done so in my 12 years here. This year, they made a change to a file where it placed the header of a column of text in a different place. Now, our program will not import it correctly. It is the PART column...
    Old text file:

    part_01.jpg

    New Text File:
    part_02.jpg


    So as you can see, they moved PART to the lower left.
    Not being an expert in VBA, I am struggling to find exactly where I need to modify the code to bring it in properly..
    This is the section of VBA code where I do think the selections are made but nothing specifies PART in the code...perhaps it is part of the Array? The file is called CZE_DET.OUT.
    SubIMPORT_CZEOUT()
    Dim aryJobs() As String
    Dim strComb As String
    Dim strDir As String
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Sheets("CEE ORDER").Visible = True
    Sheets("CZE_DET").Visible = True
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Sheets("CEE ORDER").Select
    For i = 1 To colAllBuildings.Count
         strDir = Dir$(colAllBuildings.Item(i) & "\CZE_DET.OUT")
         If strDir <> "" Then
             Workbooks.OpenText Filename:=colAllBuildings.Item(i) & "\CZE_DET.OUT", Origin:=xlWindows, _
             StartRow:=7, DataType:=xlFixedWidth, _
             FieldInfo:=Array(Array(0, 9), Array(5, 1), Array(9, 9), Array(10, 1), _
             Array(13, 9), Array(14, 1), Array(15, 9), Array(16, 1), Array(18, 1), _
             Array(28, 9), Array(35, 9), Array(47, 9), Array(54, 1), Array(57, 1), _
             Array(62, 1), Array(67, 1), Array(72, 1))
             Range("A1:L" & CStr(Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)).Select
             Selection.Copy
             Windows(strShipperName).Activate ' This line does not work, for NO reason!
    '        Windows(1).ActivatePrevious
             Sheets("CZE_DET").Select
             Range("A1").Select
             If Range("A1").Value <> "" Then
                 ActiveSheet.Range("A65536").End(xlUp).Select
                ActiveCell.Offset(1, 0).Select
             End If
             Selection.PasteSpecial Paste:=xlValues
             Selection.Sort Key1:=Range("A12"), Order1:=xlAscending, Orientation:=xlTopToBottom
             Windows("CZE_DET.OUT").Activate
             ActiveWindow.Close
         End If 
    Next

    Any suggestions?
    Last edited by Aussiebear; 01-18-2023 at 12:28 PM. Reason: Removed additional code tags

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •