0

Per comments below, I'm editing my original posted question to more accurately reflect my problem.

This is for my job. In my work program, I download my data table to a csv file and it automatically opens the data in Notepad. I don't save the file in Notepad. (This process has to remain the same per my job. Saving the data is unnecessary and would take too much time as I have to do this same step hundreds of times a day.)

Once the data opens in Notepad, I copy all the data. I then need to paste the data directly into excel. The data is comprised of 66 different columns and includes headers. I need to parse the columns so that many of them are skipped, while most of the others are pasted as text. In the array below, i thought i laid that out correctly using the Array(1, 9), Array(4, 2) method, hoping Column 1 would be skipped and Column 4 would be pasted as text, etc...

When running this code though, the first column is skipped, but then all subsequent columns are pasted, not following any of the array setup i wrote in the code. I've researched questions on this site and many others. I can't figure what is wrong with my code to cause this issue.

Here is a link to the data I'm using (edited for anonymity of data): https://www.dropbox.com/s/2ujbpgm3zkvserx/Test_Data_Parsing.txt?dl=0

Here is the code I'm using:

    Sub Test_PasteParsing()


        With ThisWorkbook.Sheets(1)
                Range("A1").PasteSpecial
        End With


        Columns("A:A").TextToColumns _
                    Destination:=Range("A1"), _
                    DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, _
                    ConsecutiveDelimiter:=True, _
                    Tab:=True, _
                    Semicolon:=False, _
                    Comma:=False, _
                    Space:=False, _
                    Other:=False, _
                    OtherChar:=False, _
                    FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 4), Array(4, 2), Array(5, 9), Array(6, 9), _
                        Array(7, 9), Array(8, 2), Array(9, 9), Array(10, 9), Array(11, 2), Array(12, 9), _
                        Array(13, 2), Array(14, 9), Array(15, 9), Array(16, 2), Array(17, 9), Array(18, 9), _
                        Array(19, 9), Array(20, 9), Array(21, 9), Array(22, 9), Array(23, 9), Array(24, 9), _
                        Array(25, 9), Array(26, 9), Array(27, 9), Array(28, 2), Array(29, 2), Array(30, 2), _
                        Array(31, 2), Array(32, 9), Array(33, 9), Array(34, 9), Array(35, 9), Array(36, 9), _
                        Array(37, 9), Array(38, 2), Array(39, 9), Array(40, 2), Array(41, 9), Array(42, 9), _
                        Array(43, 9), Array(44, 9), Array(45, 9), Array(46, 9), Array(47, 9), Array(48, 9), _
                        Array(49, 9), Array(50, 9), Array(51, 2), Array(52, 2), Array(53, 2), Array(54, 2), _
                        Array(55, 9), Array(56, 9), Array(57, 2), Array(58, 9), Array(59, 2), Array(60, 9), _
                        Array(61, 9), Array(62, 2), Array(63, 9), Array(64, 9), Array(65, 2), Array(66, 2)), _
                    TrailingMinusNumbers:=True


    End Sub
Macca
  • 1
  • 4
  • The above code is only partial. There is no filename from which to import. Please supply code so that I can replicate the problem. Its hard to guess what else is being done. – donPablo Oct 27 '19 at 21:16
  • 1
    Why are you not using the txt import assistant to choose the columns you want to import? You can create a macro with the macro recorder for this issue. – David García Bodego Oct 28 '19 at 03:08
  • I edited my original post above to hopefully be more accurate in describing how I use my data and paste into Excel. +donPablo: The code above is complete. Pardon my original post if I gave the impression I was using a saved file. I am copying directly from an open Notepad and pasting data into Excel. +David Garcia Bodego: By txt import assistant do you mean txt import wizard? I thought that was only used with saved files and in this example i'm not using a saved file. I apologize for any confusion I created. – Macca Oct 28 '19 at 14:01

0 Answers0