[VBA/EXCEL] Generare script VBS da una Macro di Excel

[SDB]

Ciao a tutti...
devo generare un vbscript da una macro excel, questa:

sub mia()

Const MAX_COL = 5
Const MAX_ROW = 6
Dim in_Col As Integer, in_Row As Integer
Const FileName1 As String = "c:\file1.xls"
Const FileName2 As String = "c:\file22.xls"
Const FileNameFinale As String = "c:\filefinale12.xls"

Dim obEx1 As Excel.Application
Dim obEx2 As Excel.Application
Dim obExFinale As Excel.Application
Dim wbEx1 As Excel.Workbook
Dim wbEx2 As Excel.Workbook
Dim wbExFinale As Excel.Workbook

Set obEx1 = New Excel.Application
Set obEx2 = New Excel.Application
Set obExFinale = New Excel.Application
Set wbEx1 = obEx1.Workbooks.Open(FileName1)
Set wbEx2 = obEx2.Workbooks.Open(FileName2)
Set wbExFinale = obExFinale.Workbooks.Open(FileNameFinale)



Dim i As Integer
For i = 1 To wbEx1.Worksheets.Count

Dim newWs As Excel.Worksheet

Set newWs = wbExFinale.Worksheets.Add

With wbEx1.Worksheets.Item(i)

newWs.Name = "A" & " " & i 'wbEx1.Worksheets.Item(i).Name & " " & i

For in_Col = 1 To MAX_COL
For in_Row = 1 To MAX_ROW

newWs.Cells(in_Row, in_Col) = .Cells(in_Row, in_Col)

Next in_Row
Next in_Col


End With
Next i


Dim j As Integer
For j = 1 To wbEx2.Worksheets.Count

Dim newWs1 As Excel.Worksheet

Set newWs1 = wbExFinale.Worksheets.Add

With wbEx2.Worksheets.Item(j)

newWs1.Name = "B" & " " & j 'wbEx2.Worksheets.Item(j).Name & " " & j

For in_Col = 1 To MAX_COL
For in_Row = 1 To MAX_ROW

newWs1.Cells(in_Row, in_Col) = .Cells(in_Row, in_Col)

Next in_Row
Next in_Col


End With
Next j

obExFinale.Visible = True


End Sub

Ho iniziato a fare una cosa del genere ,non so come fare e vado a tentativi, ma mi da errore perchè non trova la classe excel:

Const MAX_COL = 5
Const MAX_ROW = 6
Dim in_Col , in_Row
Const FileName1 = "c:\file1.xls"
Const FileName2 = "c:\file22.xls"
Const FileNameFinale = "c:\filefinale12.xls"

Dim obEx1
Dim obEx2
Dim obExFinale
Dim wbEx1
Dim wbEx2
Dim wbExFinale

Set obEx1 = New Excel.Application
Set obEx2 = New Excel.Application
Set obExFinale = New Excel.Application
Set wbEx1 = obEx1.Workbooks.Open(FileName1)
Set wbEx2 = obEx2.Workbooks.Open(FileName2)
Set wbExFinale = obExFinale.Workbooks.Open(FileNameFinale)



Dim i
For i = 1 To wbEx1.Worksheets.Count

Dim newWs

Set newWs = wbExFinale.Worksheets.Add

With wbEx1.Worksheets.Item(i)

newWs.Name = "A" & " " & i 'wbEx1.Worksheets.Item(i).Name & " " & i

For in_Col = 1 To MAX_COL
For in_Row = 1 To MAX_ROW

newWs.Cells(in_Row, in_Col) = .Cells(in_Row, in_Col)

Next
Next


End With
Next


Dim j
For j = 1 To wbEx2.Worksheets.Count

Dim newWs1

Set newWs1 = wbExFinale.Worksheets.Add

With wbEx2.Worksheets.Item(j)

newWs1.Name = "B" & " " & j 'wbEx2.Worksheets.Item(j).Name & " " & j

For in_Col = 1 To MAX_COL
For in_Row = 1 To MAX_ROW

newWs1.Cells(in_Row, in_Col) = .Cells(in_Row, in_Col)

Next
Next


End With
Next

obExFinale.Visible = True

come fare?

[SDB]

Ciao a tutti...
ho creato una macro che effettua un semplice copia ed incolla di alcuni fogli di excel da un excel all'altro...
ecco il codice :

Const FileName1 = "C:\file1.xls"
Const FileName2 = "C:\file2.xls"
Const FileNameFinale = "C:\provafinale.xls"

Dim obEx1
Dim obEx2
Dim obExFinale
Dim wbEx1
Dim wbEx2
Dim wbExFinale

'' LE PARTI COMMENTATE SERVONO AL VBS, MA NON ALLA MACRO

'Set obEx1 = WScript.CreateObject("Excel.Application")
'Set obEx2 = WScript.CreateObject("Excel.Application")
'Set obExFinale = WScript.CreateObject("Excel.Application")
Set wbEx1 = Workbooks.Open(FileName1) 'obEx1.Workbooks.Open(FileName1)
Set wbEx2 = Workbooks.Open(FileName2) 'obEx2.Workbooks.Open(FileName2)
Set wbExFinale = Workbooks.Open(FileNameFinale) 'obExFinale .Workbooks.Open(FileNameFinale)


Windows("file2.xls").Activate
wbEx2.Sheets(Array("TOTAL", "NON-LOCAL CCY", "LOCAL CCY")).Select
wbEx2.Sheets("NON-LOCAL CCY").Activate

''QUI VA IN ERRORE : EXCPECTED STATMENT

wbEx2.Sheets(Array("TOTAL", "NON-LOCAL CCY", "LOCAL CCY")).Copy Before:=Workbooks("provafinale.xls").Sheets(1)
wbExFinale.Sheets("TOTAL").Select
wbExFinale.Sheets("TOTAL").Name = "TOTAL PRIOR WEEK"
wbExFinale.Sheets("NON-LOCAL CCY").Select
wbExFinale.Sheets("NON-LOCAL CCY").Name = "NON-LOCAL PRIOR WEEK"
wbExFinale.Sheets("LOCAL CCY").Select
wbExFinale.Sheets("LOCAL CCY").Name = "LOCAL PRIOR WEEK"
wbExFinale.Sheets("NON-LOCAL PRIOR WEEK").Select
wbExFinale.Sheets("NON-LOCAL PRIOR WEEK").Move Before:=Sheets(4)
Windows("file1.xls").Activate
wbEx1.Sheets(Array("TOTAL", "NON-LOCAL CCY", "LOCAL CCY")).Select
wbEx1.Sheets("NON-LOCAL CCY").Activate
wbEx1.Sheets(Array("TOTAL", "NON-LOCAL CCY", "LOCAL CCY")).Copy Before:=Workbooks("provafinale.xls").Sheets(1)
wbExFinale.Sheets("NON-LOCAL CCY").Select
wbExFinale.Sheets("NON-LOCAL CCY").Move Before:=Sheets(4)
wbExFinale.Sheets("NON-LOCAL CCY").Select
wbExFinale.Sheets("NON-LOCAL CCY").Name = "NON-LOCAL CURRENT WEEK"
wbExFinale.Sheets("LOCAL CCY").Select
wbExFinale.Sheets("LOCAL CCY").Name = "LOCAL CURRENT WEEK"
wbExFinale.Sheets("TOTAL").Select
wbExFinale.Sheets("TOTAL").Name = "TOTAL CURRENT WEEK"
wbExFinale.Sheets("LOCAL CURRENT WEEK").Select

la macro funzione perfettamente se lanciata da qualsiasi excel ma ad vbs no!!
Ho inserito dei commenti in maiuscolo...
aiuto!!!