none
import fixed width file to excel RRS feed

  • Question

  • objExcel.Workbooks.OpenText _
        "C:\Scripts\Test.txt",,,xlFixedWidth,,,,,,,,,Array(Array(0,1),Array(14,1),Array(32,1))

    By this method we could assign start point of an element. But couldn't assign end point. I mean, in this example I take value from 0 to 13, from 14 to 31, and from 32 till end. But, is it possible not to take value from 10 to 14?

    Tuesday, December 31, 2013 11:48 AM

Answers

  • Re:  skipping a column when opening a text file

    Using the Excel constant "xlSkipColumn" (value of 9) as the second element in the column array should work...
       Array(10, 9)
    '---
    xlGeneralFormat General   0
    xlTextFormat Text             1
    xlMDYFormat MDY date    2
    xlDMYFormat DMY date    3
    xlYMDFormat YMD date    4
    xlMYDFormat MYD date    5
    xlDYMFormat DYM date    6
    xlYDMFormat YDM date    7
    xlEMDFormat EMD date    8
    xlSkipColumn Skip Column   9
    '---
    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)


    • Marked as answer by aldarik Wednesday, January 1, 2014 5:51 AM
    • Edited by James Cone Tuesday, October 18, 2016 5:05 PM
    Tuesday, December 31, 2013 7:37 PM

All replies

  • Re:  skipping a column when opening a text file

    Using the Excel constant "xlSkipColumn" (value of 9) as the second element in the column array should work...
       Array(10, 9)
    '---
    xlGeneralFormat General   0
    xlTextFormat Text             1
    xlMDYFormat MDY date    2
    xlDMYFormat DMY date    3
    xlYMDFormat YMD date    4
    xlMYDFormat MYD date    5
    xlDYMFormat DYM date    6
    xlYDMFormat YDM date    7
    xlEMDFormat EMD date    8
    xlSkipColumn Skip Column   9
    '---
    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)


    • Marked as answer by aldarik Wednesday, January 1, 2014 5:51 AM
    • Edited by James Cone Tuesday, October 18, 2016 5:05 PM
    Tuesday, December 31, 2013 7:37 PM
  • Thanks, that exactly what I wanted.

    And one more little question. How can I do "if" condition for current row? I mean, if the value of first cell is "skip2", it makes second array skipped (Array(14, 9)) and put in the second cell third array. But, if the value of first cell is "skip3", it makes third array skipped (Array(32,9)).

    Wednesday, January 1, 2014 5:51 AM

  • Re:  Open text file code alternatives...

    Maybe...
    '---
    If  objExcel.Workbooks("Sludge").Worksheets(1).Range("A1").Value = "skip2" Then   
    objExcel.Workbooks.OpenText _
        "C:\Scripts\Test.txt",,,xlFixedWidth,,,,,,,,,Array(Array(0,1),Array(14,9),Array(32,1))
    Else
    objExcel.Workbooks.OpenText _
        "C:\Scripts\Test.txt",,,xlFixedWidth,,,,,,,,,Array(Array(0,1),Array(14,1),Array(32,9))
    End If
    '---
    Jim Cone
    Wednesday, January 1, 2014 2:16 PM
  • Thank you.

    If I get more questions, may be just a little bit relative to the topic, may I ask it here?

    Thursday, January 2, 2014 5:39 AM
  • aldarik,

    Re: asking questions

    Best to start a new message.  You will expose the question to a wider array of possible responses.
    As long as one acknowledges all responses, doesn't ask for a complete utility to be created and is polite, you will probably get your question answered.

    FWIW:  When automating Excel, Set your Excel objects to Nothing when done.
    (eliminates "orphans" that leaves Excel still running in Task Manager)
    '---
    Jim Cone
    free Excel Date Picker add-in
    (no trackers, no cookies, no ads, no registration)
    https://goo.gl/IUQUN2 (Dropbox)


    • Edited by James Cone Tuesday, October 18, 2016 5:06 PM
    Thursday, January 2, 2014 2:46 PM