Error in this code.

[psk]

All,
I am using this VB code with some applications for exporting the
stuff from report to excel by saving it as text and opening it in excel
sheet.

I am getting error in this code when i move the sheets from one book to
final workbook in this line. Final code is in the down.


"*
source_workbook.Worksheets.Move
Before:=final_workbook.Worksheets("Sheet1") "*

I don't know is this right forum..
But if possible, can anyone help me ..?


Sub expToExcel()
'Things to Add
'Add a sheet in the final Excel file depending upon the number of books
in the report
'as by defalut there
'****************************************************************************'
Dim doc As Document
Dim rep As Report
Dim HtmlFile As String
Dim Path As String
Dim ExcelFile As String
Dim excelfinal As Excel.Application
Dim excelsource As Excel.Application
Dim source_workbook As Excel.Workbook
Dim final_workbook As Excel.Workbook
Set excelfinal = New Excel.Application
Set excelsource = New Excel.Application
Set final_workbook = excelfinal.Workbooks.Add 'this is the
final workbook that we are going to use

'****************************************************************************'
'Declare a FSO to kill the Master file
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.fileexists("c:\master.xls") Then fso.deletefile
("c:\master.xls")
'****************************************************************************'
Set doc = ActiveDocument
Path = "C:\"
Set excelsource = New Excel.Application
'Now loop through each report and save it as temp text file
For i = 1 To doc.Reports.Count
Set rep = doc.Reports(i)
rep.ExportAsText ("C:\" & rep.Name & ".txt")

Set source_workbook =
excelsource.Workbooks.Open("c:\" & rep.Name & ".txt")

source_workbook.Worksheets.Move
Before:=final_workbook.Worksheets("Sheet1")

If i = 1 Then
final_workbook.SaveAs
("c:\Master.xls")
End If
excelsource.Workbooks.Close
Kill ("c:\" & rep.Name & ".txt")
'excelsource.Quit
'Set excelsource = Nothing
Next i
final_workbook.Save
excelfinal.Quit
Set excelfinal = Nothing
On Error Resume Next
excelfinal.Quit
Set excelfinal = Nothing
excelsource.Quit
Set excelsource = Nothing
MsgBox ("Master Excel File Created")
End Sub



Thanks in advance

 

[Dick Kusleika]

psk

What error do you get? Make sure there is a sheet in final_workbook named
Sheet1. If you want to move the sheet to the start of the workbook, just
use

Before:=final_workbook.Sheets(1)

 

[Guest]

If you are coming up with an error "Subscript out of range" then check that
"Sheet1" exists at the point the code comes up with that error.

Alok Joshi

 

[psk]

I am getting error as move objects failed ...?

Can anyone help m