Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Array loops

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    At work, I get a lot of textfiles that I need to open in a particular format in excel. I have to open them with fixed width, and the column widths, including the column headings are usually given to me in a seperate excel spreadsheet. I have recorded a macro to do this, but, it only works for a particular format. I get this type of code coming up which I wish to generalise:

    Array(Array(0,1),Array(3,1),Array(4,1),.....)

    I am given an excel spreadsheet with the values 0,3,4 etc. in column A of the spreadsheet. My wish is for the code above to read this in automatically, at present I have to go into the code and change the 0,3,4 to be the new format all the time which is a pain.

    Also, I am given in column B, the column headings. It would be nice if those column headings could be placed in the top row of the resulting text file, I am sure this could be done with transposing the range in column B and inserting it in the top row of the text file.

    But, as a general point, assume I have the following values in Range("a1:a5")

    2,5,6,7,8

    ok. now I sometimes want to loop using these values which is not a problem. But, sometimes, I need the index number to be used. i.e. I would like B(2)=5 and so on .
    This ties into what I thought would work for the above. Ideally I would like to get something of this sort:

    array(array(A(0),1), array(A(1),0), array(A(2),0),.....)

    where A = Range("A1:A5") or something like that? Somethign along these lines woul dbe awesome. Especially so if we could get A to be a dynamic range.

    Cool, now please try and help me.

    Alternatively, at the moment what I have done is use the spreadsheet I have been given with the widths and column headings format and used them to generate the code on the spreadsheet by manipulating text strings etc. So I have th actual macro as text in Range("D4:D7") say. Is there a way that a macro in a module could actually 'read' the text in D4:D7 of this worksheet and exectute it? Wouldn't that be cool.

    Thanks.

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you give an example of your current code, and what you would have in your range A1:A5, I think I can help you (and I'm sure that others could too).

    Thank you,

    -rh

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I get the format required for opening a text file in a table on an excel spreadsheet like this:

    Category Field start Width
    AGE 0 3
    POL NO 3 6
    DATE 9 6
    PREM_TYPE 15 5
    POL_TYPE 20 10
    UNIT1 30 4
    UNIT2 34 2
    UNIT3 36 6
    END_DATE 42 6
    48

    I get this code in my macro, which I would like to generalise and be updated automatically based on the table above:

    Workbooks.OpenText Filename:="C:My Documentsfile.txt", Origin:=xlWindows _
    , StartRow:=1, DataType:=xlFixedWidth,
    FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(9, 1), Array(15, 1), Array(20, 1), Array(30, 1), Array(34, 1), Array(36, 1),Array(42, 1), Array(48, 1))

    So I would like to get this code updated automatically for any given change in the column headings and widths given in the table on the excel spreadsheet. I just want to put the different column headings and widths in the spreadsheet, and just click a button to open the text file in exactly that format.

    I would be grateful for any help. Thankyou!

  4. #4
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, let's start with the values you use for your fields. You want the starting values, like you have in your second column, but you don't need the lengths. In the third column instead of the lengths, you should have the Data Type you want for the field. In your example, you have each field as General. The possible values are:






































    1 General
    2 Text
    3 MDY Date
    4 DMY Date
    5 YMD Date
    6 MYD Date
    7 DYM Date
    8 YDM Date
    9 Skip the column



    Here is some code that I used to open a text file, hopefully it will give you some ideas. Happy Excelling,

    Russell



    Option Explicit

    Sub TestArray()
    Dim rng As Range
    Dim arr() As Integer
    Dim intRow As Integer
    Dim intCol As Integer

    ' Here are 3 examples of how to get your range:
    Set rng = Selection
    Set rng = Range("A1:B7")
    Set rng = Range("MyTextInfoRange")

    If rng.Columns.Count <> 2 Then
    MsgBox "Range must have exactly 2 columns - one for the " & _
    "starting position of the field, and the other for " & _
    "the data type"
    Exit Sub
    End If

    ReDim arr(1 To rng.Rows.Count, 1 To 2)

    For intRow = 1 To rng.Rows.Count
    For intCol = 1 To 2
    'Or: For intCol = 1 to rng.Columns.Count
    arr(intRow, intCol) = rng(intRow, intCol)
    Next intCol
    Next intRow


    Workbooks.OpenText FileName:="D:MyFile.txt", Origin:=xlWindows, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=arr

    End Sub



    [ This Message was edited by: Russell Hauf on 2002-03-21 13:51 ]

    [ This Message was edited by: Russell Hauf on 2002-03-21 13:52 ]

    [ This Message was edited by: Russell Hauf on 2002-03-21 14:05 ]

    [ This Message was edited by: Russell Hauf on 2002-03-21 14:05 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    WOW!

    Thankyou ever so much, that is simply awesome!

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You're welcome - sorry about the big space everyone...fooling around with HTML (at which I am pretty much a rookie!).


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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