Automate copy and rename excel files

[Leuchtparka]

Hi All,

I´m loocking for a solution to automate some copy and rename tasks and found this great tool.

I´m trying to solve the following problem:

There are a main excel file with one sheet inside.

There are a second excel file (called name-file) with a lot of names in the first column like Peter, Mika, Susanne, Paul...

I want to have a copy of the main file, renamed with the names of the first column frome name-file and inside the the target file in first row and column having the same name of name-file.

e.g.

mainfile.xls

name-file.xls

(firts column)

Peter

Mika

Susanne

Paul

want have files:

Peter_mainfile.xls (inside first row and column "Peter")

Mika_mainfile.xls ...

...

Is anybody out there how can give ideas for a solution?

Thx.

[99ojo]

Hi,

if i understand you right, this should help:

#include <excel.au3>
;#include <array.au3> ; uncomment for debugging see following remarks
Global $arnames
$mainfile = @ScriptDir & "\mainfile.xls"
$namefile = @ScriptDir & "\name-file.xls"
$oExcel = _ExcelBookOpen ($namefile)
$arnames = _ExcelReadSheetToArray ($oExcel)
_ExcelBookClose ($oExcel)
;_ArrayDisplay ($arnames) ; debugging line to see array contents after reading excelsheet to array for checking definition of For Loop

;loop over array, $arnames [0] [1] amount of columns in Array
For $i = 1 To $arnames [0] [1]
    Filecopy ($mainfile, @ScriptDir & "\" & $arnames [1] [$i] & "_mainfile.xls")
Next

You should also have a look into helpfile for functions _excel*

;-))

Stefan

Edited March 22, 2010 by 99ojo

[junkew]

Hi,

I certainly would have doubt why you would like to write this in AutoIT.

Its far more efficient to write a macro in VBA (within excel alt+f11) like

sub testIt()
   dim wbMain as Workbook
   dim wbData as workbook
   dim i as long
   dim tName as string

   set wbMain=workbooks("mainfile.xls")
   set wbData=workbooks.open("name-file.xls")
   
   i=1
   while wbdata.worksheets(1).cells(i,1).value <> ""
      tName=wbdata.worksheets(1).cells(i,1).value
      set wbNew = workbooks.add
      wbnew.worksheets(1).cells(1,1).value=tname
      wbnew.saveas tName
      i=i+1
      wbnew.close
   wend
end sub