none
VBA Code broken From Excel 16.0.7 to version 16.0.8 RRS feed

  • Question

  • Having issues with below piece of code. Works fine on excel version 16.0.7 but not 16.0.8. 

    Dim path As Variant
    Dim myfile As Variant
    path = ActiveWorkbook.path
    myfile = ActiveWorkbook.Name
    Workbooks.OpenText filename:= _
            path & "\" & myfile, Origin:=437, _
            StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(16 _
            , 1), Array(20, 1), Array(51, 1), Array(54, 1), Array(66, 1), Array(80, 1), Array(86, 1), _
            Array(107, 1), Array(121, 1)), TrailingMinusNumbers:=True

    When it works, It separates the data from a .SP file into separate columns. When it doesnt all data is still in column A.

    I actually tried on other PC's and we are having the same issue. the ones with updated excel cant read that code while the older ones can. I cant seem to find anything on Microsofts page for a fix. Any ideas?
    Monday, May 29, 2017 6:38 PM

All replies

  • Hi Vitaliy,

    This forum focuses on the general discussion for Office 2016. I notice that the issue is related to the Macro VBA code in Excel. To better resolve your issue, I would move the thread to Excel for developers forum for more help.

    Thanks for your understanding.


    Regards,

    Winnie Liang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, May 30, 2017 2:03 AM
  • Hello,

    I suggest you follow the link below to revert Office.

    https://support.microsoft.com/en-us/help/2770432/how-to-revert-to-an-earlier-version-of-office-2013-or-office-2016-click-to-run

    If the code works after reverting, i think we could confirm the issue causes from Office update. I would suggest you sumbit your feedback on Excel UserVocie site: https://excel.uservoice.com/

    Accoring to Workbooks.OpenText Method (Excel), it loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data. i think it works for text files like *.prn;*.txt;*.csv and i notice you are open the current workbook as text file.

    I would suggest you use QueryTable to get the data.

    E.g.

        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;D:\test.txt", Destination:=Range("$A$1"))
            .CommandType = 0
            .Name = "1"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileFixedColumnWidths = Array(13, 14, 15, 14, 27, 10, 4)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 31, 2017 6:57 AM
    Moderator