Creating a Simple macro

[TheVig]

This simply copies data from one column to another. It will replace any data in the column and will not delete the source data. Don't know if this what you are looking for, but it should get you started.

Excell has to be closed for this to run. Gintaras, do you have any way to get it working with Excell open?

This might be useful too:
range examples: "" (all), "sel" (selection), "A1:C3" (range), "A:C" (columns A, B and C), "3:3" (row 3), "A1" (cell), "Named" (named range)

Here is the code with notes as best as I understand it all:
Function Function6

Code:

Copy      Help

;;All of this will run hidden...
;;so you won't see a thing happen
;;except for outputting variables in QM.
;;Declares the filepath for Excel file to use
str sfile="$desktop$\Test.xls"
;;Declares the name of the sheet to use
;;Make sure the name of the sheet is correct!!
str sheet="Sheet1"
;;Works the Magic...
Excel.Application app._create
Excel.Workbook book=app.Workbooks.Open(_s.expandpath(sfile))
ExcelSheet es.ws=book.Worksheets.Item(sheet)
;;Declare the variable for the ARRAY you want for data in QM
ARRAY(str) a
;;Get Column A
es.GetCells(a "A:A")
;;Breaks apart data in ARRAY a
int r c
for r 0 a.len
,for c 0 a.len(1)
,,;;This creates a new variable for every peice of data
,,;;a[c r] could be something like a[1 20] which would be the a in column A row 20
,,;;r is indexed to "0" this resets the index to 1
,,r+1
,,;;Set data to new column "2" here is column "B"
,,;;which is the 2nd column in Excel.  
,,;;"3" would be column "C" and so on...
,,es.SetCell(a[c r] 2 r)
;;Saves the Excel sheet
book.Save
;;Closes the Excel
book.Close

Here is the simplified code without notes:
Function Function6

Code:

Copy      Help

str sfile="$desktop$\Test.xls"
str sheet="Sheet1"
Excel.Application app._create
Excel.Workbook book=app.Workbooks.Open(_s.expandpath(sfile))
ExcelSheet es.ws=book.Worksheets.Item(sheet)
ARRAY(str) a
es.GetCells(a "A:A")
int r c
for r 0 a.len
,for c 0 a.len(1)
,,r+1
,,es.SetCell(a[c r] 2 r)
book.Save
book.Close

Not too scary.

Jimmy Vig