Skip navigation

Applescript - Excel - Convert columns next to each other to one long column

1117 Views 6 Replies Latest reply: Apr 29, 2012 8:28 AM by jaxjason RSS
JStudent Calculating status...
Currently Being Moderated
Apr 28, 2012 11:39 AM

Dear all,

 

 

I am a student and need to automate somthing in excel. And by googling I found this was posible with applescript but I can't work with applescript.

I have 500 colums next to each other and needs to put them under each other and I need to do this a few times.

 

So this:

 

147
258
369

 

Needs to become this:

1

2

3

4

5

6

7

8

9

 

By googling a bit and trying somthing stuff i found this:

 

on run

    tell application "Microsoft Excel"

        tell sheet "Sheet1" of workbook 1

 

 

            set EndofRow to 100

            set Endofcolumn to 13

 

            set input_data to ""

            repeat with j from 1 to EndofRow

                repeat with i from 1 to Endofcolumn

 

                    set InputCell to the value of range (j & i)

                    set input_data to InputCell

                    set encoded_data to (input_data) of me

 

                    set EncodedCell to ("A" & 20 + i + j)

                    set value of cell EncodedCell to encoded_data

                    set value of cell EncodedCell to encoded_data

 

 

              end repeat

              end repeat

 

        end tell

    end tell

end run

 

But the problem is that i don't know how to select a range that is specified by two variables (that what i put in bold). If anyone would knew how to fix this. Or if anyone would knew a script for this I would be realy happy.

 

Thank you in advance for your help!

 

Jan

Applescript
  • jaxjason Level 4 Level 4 (3,320 points)

    what version of excel are you using, because i could do this in a bout ten minutes with VBA that is included with excel version 2011. I know it can be done using applescript, however Excels Applescript library is a little incomplete and inconsistant from what i hear.

     

    Jason

  • jaxjason Level 4 Level 4 (3,320 points)

    VBA Code:

     

    Sub CombineIt()

    Dim MyRow As Long, MyCol As Long, OutRow As Long

    OutRow = Application.WorksheetFunction.CountA(ActiveSheet.Range("A:A")) + 1

    MyCol = 2

    Do Until Trim(Cells(1, MyCol).Value) = ""

        MyRow = 1

        Do Until Trim(Cells(MyRow, MyCol).Value) = ""

            Cells(OutRow, 1).Value = Cells(MyRow, MyCol).Value

            OutRow = OutRow + 1

            MyRow = MyRow + 1

        Loop

        MyCol = MyCol + 1

    Loop

    MsgBox "done"

    End Sub

  • jaxjason Level 4 Level 4 (3,320 points)

    I *Think* this works....

    [CODE]

    tell application "Microsoft Excel"

              tell active sheet

                        set myCol to 2

                        set lastRowNum to (first row index of (get end (cell 1 of row (count rows)) direction toward the top)) + 1

                        repeat while the value of cell myCol of row 1 of active sheet is not equal to ""

                                  set myRow to 1

                                  repeat while the value of cell myCol of row myRow of active sheet is not equal to ""

                                            set newvalue to the value of cell myRow of column myCol of active sheet

                                            set the value of range ("A" & lastRowNum) of active sheet to newvalue

                                            set myRow to myRow + 1

                                            set lastRowNum to lastRowNum + 1

                                  end repeat

                                  set myCol to myCol + 1

                        end repeat

              end tell

    end tell


     

    [CODE]

    took me forver to realize that you cannot place set the value to the value of. I had to assign the new value for the next cell to a variable, then use that.

     

    i.e. This did not work at all....

    set the value of range ("A" & lastRowNum) of active sheet to the value of cell myRow of column myCol of active sheet

    I had to set the NewValue variable and use it as above.

     

    Jason

  • jaxjason Level 4 Level 4 (3,320 points)

    first, I think mine is a little more flexible since it is not hard coded for number fo rows and columns. You cna have uneven columns of data, in any number of columns. You dont have to have them even (same number of rows), or even know how many columns there are.

     

    second, everything you need to do it in a column by column method is in the applescript i gave you. Interpret what each line does, and you should be able to modify it to work by column range.

     

    Jason

Actions

More Like This

  • Retrieving data ...

Bookmarked By (1)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.