6 Replies Latest reply: Apr 29, 2012 8:28 AM by jaxjason
JStudent Level 1 Level 1 (0 points)

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
  • 1. Re: Applescript - Excel - Convert columns next to each other to one long column
    jaxjason Level 4 Level 4 (3,325 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

  • 2. Re: Applescript - Excel - Convert columns next to each other to one long column
    jaxjason Level 4 Level 4 (3,325 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

  • 3. Re: Applescript - Excel - Convert columns next to each other to one long column
    jaxjason Level 4 Level 4 (3,325 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

  • 4. Re: Applescript - Excel - Convert columns next to each other to one long column
    JStudent Level 1 Level 1 (0 points)

    Thank you very much! i think i will upgrade to excel 2011 so I can use the VB macro's! But anyway i learned to work a bit with applescript .

     

     

    I found some code myself aswell that works but it took me forever!

     

    tell application "Microsoft Excel"

       

        set EndofRow to 500

        set Endofcolumn to 76

       

     

            repeat with j from 1 to EndofRow

                repeat with i from 1 to Endofcolumn

     

                set x to value of column j of row i

               

                set rowIdx to first row index of (find range "A:A" what "")

                set value of column 1 of row rowIdx to x

               

            end repeat

        end repeat

    end tell

    end run

     

    But thanks for the reply!

    Jan

  • 5. Re: Applescript - Excel - Convert columns next to each other to one long column
    JStudent Level 1 Level 1 (0 points)

    But one more thing this loop is very slow. Is their anyway to make it faster? For example by creating a loop that only has to copy and paste the whole columns and not cell by cell?

     

    Jan

  • 6. Re: Applescript - Excel - Convert columns next to each other to one long column
    jaxjason Level 4 Level 4 (3,325 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