re: Transposing data in excel (VBScript)

CCDEAN100

unread,

Aug 8, 2007, 11:38:03 AM8/8/07

to

Hi

I'm trying to transpose an array of data which has been exported from a
program using VBScript.

The code works fine, but it leaves the original data in place, which is
making a mess of the worksheet. The code i'm using is as follows:

>>>>>>>>>>>

Set objExcel = CreateObject("Excel.Application")
Set workbook = objExcel.Workbooks.Open("FILENAME.xls")
ObjExcel.Visible = True

'Set startCell = objExcel.Range("A1")
'startCell.Resize(UBound(ARRAYNAME,2), UBound(ARRAYNAME, 1)).Value =
objExcel.Transpose(ARRAYNAME)

>>>>>>>>>>>

Does anyone have any solutions for transposing the data whilst removing the
original data?

--
Charles Dean

J.Williams

unread,

Aug 8, 2007, 1:48:54 PM8/8/07

to

"CCDEAN100" <CCDE...@discussions.microsoft.com> wrote in message
news:0C4C8140-52EB-4848...@microsoft.com...

One of these should do what you want:

'From http://msdn2.microsoft.com/en-us/library/aa221100(office.11).aspx
const xlCellTypeLastCell = 11

'Clear whole sheet starting from A1
Set rng = objExcel.Range("A1",
objExcel.Cells.SpecialCells(xlCellTypeLastCell))
rng.Clear

'Clear specified cell range
'set rng = objExcel.Range(objExcel.Cells(1,1), objExcel.Cells(4,4))
rng.ClearContents

'Clear 1 row and 1 column larger than the array size
startCell.Resize(UBound(ARRAYNAME,2)+1, UBound(ARRAYNAME, 1)+1).Value =
objExcel.Transpose(ARRAYNAME)

CCDEAN100

unread,

Aug 9, 2007, 5:08:02 AM8/9/07

to

Many Thanks for your reply regarding transposing data in Excel. It worked
brilliantly. However, I have a similar query which I hope you can help me
with. The details are as follows:

I am passing two arrays into Excel. The commands for the first array open
an instance of an existing workbook residing on my hard drive. It opens the
workbook, passes the data array to worksheet 1, and transposes the data
perfectly.

The commands for the second array pass the data to the instance of the Excel
workbook already open, and pass the data to worksheet 2.

Due to the syntax I'm using for the second set of commands, the code you
gave me needs adapting, and I'm struggling to make it work. Can you help me
find a solution? The syntax for the second set of commands is as follows:

>>>>>>>>>>>

Set objExcel = GetObject( ,"Excel.Application")
Set workbook = objExcel.ActiveWorkbook
Set objSheet = workbook.Sheets(2)

objSheet.Cells(i, j).Value = ARRAY(i,j)

Dim rng

Set rng = objExcel.Range("A1",
objExcel.Cells.SpecialCells(xlCellTypeLastCell))
rng.Clear

Set StartCell = objExcel.Range("A1")
StartCell.Resize(UBound(ARRAY, 2), UBound(ARRAY, 1)).Value =
objExcel.Transpose(ARRAY)

>>>>>>>>>>>

I imagine for the experienced VBScript user it it is fairly easy to work
out, but this is my first time using objects in VBScript. I have tried
various combinations which have all failed.

Hope you can help, thanks again.

CCDEAN100

unread,

Aug 9, 2007, 8:18:01 AM8/9/07

to

Hi JW

Ignore the previous post, I've figured it out - the syntax was very simple,
always the way! For those interested it is as follows:

>>>>>>>>

Set startCell = objSheet.Range("B2")
startCell.Resize(UBound(MASSVALUES,2), UBound(MASSVALUES, 1)).Value =
objExcel.Transpose(MASSVALUES)

>>>>>>>>

NB. Basically, change all the 'objExcel' to 'objSheet' (so simple I didn't
expect it to work)

--
Charles Dean

mr_unreliable

unread,

Aug 9, 2007, 11:14:02 PM8/9/07

to

Maybe an answer to the question you didn't ask, but how
about using a pivot table, and just telling the P.T.
to reverse (or exchange) its axes?

cheers, jw
____________________________________________________________

You got questions? WE GOT ANSWERS!!! ..(but,
no guarantee the answers will be applicable to the questions)