HELP!!!! Copying excel worksheets via Access VBA

[sll810]

I am writing VBA in access controlling Excel. I need to open a new workbook and copy the first sheet, called Summary into a new sheet in the new workbook and then save the new workbook. I keep getting an error saying "Copy method of Worksheet class failed"

Here is my copy code below:

Set xlapp = New Excel.Application
xlapp.Visible = True
Set reportbook = xlapp.Workbooks.Add
scrapbook.Worksheets("Summary").Copy_
Before:=reportbook.Worksheets(1)

I have tried tio use the name of a worksheet also when referencing the reportbook.

Any ideas are MUCH appreciated.

Thanks!!

 

[Norie]

Is that all the code?

I'm asking because I don't see scrapbook defined/declared anywhere.

 

[sll810]

Yeah there is more code but I didn't want to put it all for public viewing. I got the sheet to copy but I am having a bigger problem now. Here is part of the relevant code below:

Sub Generate_Report()

Dim xlapp As Excel.Application
Dim scrapbook As Excel.Workbook
Dim reportbook As Excel.Workbook
Dim ptablesheet As Excel.Worksheet
Dim datasheet As Excel.Worksheet, summarysheet As Excel.Worksheet, avgdailysheet As Excel.Worksheet
Dim PivTable As Excel.PivotTable
Dim PivTable2 As Excel.PivotTable
Dim DataRange, DestRange, DestRange2 As Excel.Range
Dim fname As String

Call Get_Dates


Set scrapbook = ActiveWorkbook
Set ptablesheet = scrapbook.Worksheets.Add
Set summarysheet = scrapbook.Worksheets.Add
summarysheet.Name = ("Summary")
ptablesheet.Name = ("PTable")
Set datasheet = scrapbook.Worksheets("Data")
Set avgdailysheet = scrapbook.Worksheets("AvgDailyEandO")
:
:
:


Set xlapp = New Excel.Application
xlapp.DisplayAlerts = False
xlapp.Visible = True
Set reportbook = xlapp.Workbooks.Add
fname = "Z:\Reports\BigMover\bigmovertest1"
scrapbook.Worksheets("Summary").Select
scrapbook.Worksheets("Summary").Copy
reportbook.Worksheets(1).Activate
reportbook.Worksheets(1).Paste
reportbook.Worksheets(1).SaveAs Filename:=fname


Set scrapbook = Nothing
Set PivTable = Nothing
Set PivTable2 = Nothing
Set xlapp = Nothing


End Sub[/i]

The problem is that I get my base scrap sheet to come out OK, then two other books are created instead of one. I get one book with just one sheet called summary (exactly what I want), the problem is this shows up as book 3. There is a book 2 that is saved as bigmoverstest1, but I want book3 saved as that. Book2 has nothing on it except in A1 a few cells are selected and the corresponding formula says =EMBED("Excel.Chart.8","").


Do you know how I can get book 3 to save and why a third book is being created? The original sourcebook is created through a function. I tried to save just the workbook report book also, not reportbook.worksheets(1).

Thanks!

 

[sll810]

Yeah there is more code but I didn't want to put it all for public viewing. I got the sheet to copy but I am having a bigger problem now. Here is part of the relevant code below:

Sub Generate_Report()

Dim xlapp As Excel.Application
Dim scrapbook As Excel.Workbook
Dim reportbook As Excel.Workbook
Dim ptablesheet As Excel.Worksheet
Dim datasheet As Excel.Worksheet, summarysheet As Excel.Worksheet, avgdailysheet As Excel.Worksheet
Dim PivTable As Excel.PivotTable
Dim PivTable2 As Excel.PivotTable
Dim DataRange, DestRange, DestRange2 As Excel.Range
Dim fname As String

Call Get_Dates


Set scrapbook = ActiveWorkbook
Set ptablesheet = scrapbook.Worksheets.Add
Set summarysheet = scrapbook.Worksheets.Add
summarysheet.Name = ("Summary")
ptablesheet.Name = ("PTable")
Set datasheet = scrapbook.Worksheets("Data")
Set avgdailysheet = scrapbook.Worksheets("AvgDailyEandO")
:
:
:


Set xlapp = New Excel.Application
xlapp.DisplayAlerts = False
xlapp.Visible = True
Set reportbook = xlapp.Workbooks.Add
fname = "Z:\Reports\BigMover\bigmovertest1"
scrapbook.Worksheets("Summary").Select
scrapbook.Worksheets("Summary").Copy
reportbook.Worksheets(1).Activate
reportbook.Worksheets(1).Paste
reportbook.Worksheets(1).SaveAs Filename:=fname


Set scrapbook = Nothing
Set PivTable = Nothing
Set PivTable2 = Nothing
Set xlapp = Nothing


End Sub[/i]

The problem is that I get my base scrap sheet to come out OK, then two other books are created instead of one. I get one book with just one sheet called summary (exactly what I want), the problem is this shows up as book 3. There is a book 2 that is saved as bigmoverstest1, but I want book3 saved as that. Book2 has nothing on it except in A1 a few cells are selected and the corresponding formula says =EMBED("Excel.Chart.8","").


Do you know how I can get book 3 to save and why a third book is being created? The original sourcebook is created through a function. I tried to save just the workbook report book also, not reportbook.worksheets(1).

Thanks!

 

[sll810]

Yeah there is more code but I didn't want to put it all for public viewing. I got the sheet to copy but I am having a bigger problem now. Here is part of the relevant code below:

Sub Generate_Report()

Dim xlapp As Excel.Application
Dim scrapbook As Excel.Workbook
Dim reportbook As Excel.Workbook
Dim ptablesheet As Excel.Worksheet
Dim datasheet As Excel.Worksheet, summarysheet As Excel.Worksheet, avgdailysheet As Excel.Worksheet
Dim PivTable As Excel.PivotTable
Dim PivTable2 As Excel.PivotTable
Dim DataRange, DestRange, DestRange2 As Excel.Range
Dim fname As String

Call Get_Dates


Set scrapbook = ActiveWorkbook
Set ptablesheet = scrapbook.Worksheets.Add
Set summarysheet = scrapbook.Worksheets.Add
summarysheet.Name = ("Summary")
ptablesheet.Name = ("PTable")
Set datasheet = scrapbook.Worksheets("Data")
Set avgdailysheet = scrapbook.Worksheets("AvgDailyEandO")
:
:
:


Set xlapp = New Excel.Application
xlapp.DisplayAlerts = False
xlapp.Visible = True
Set reportbook = xlapp.Workbooks.Add
fname = "Z:\Reports\BigMover\bigmovertest1"
scrapbook.Worksheets("Summary").Select
scrapbook.Worksheets("Summary").Copy
reportbook.Worksheets(1).Activate
reportbook.Worksheets(1).Paste
reportbook.Worksheets(1).SaveAs Filename:=fname


Set scrapbook = Nothing
Set PivTable = Nothing
Set PivTable2 = Nothing
Set xlapp = Nothing


End Sub[/i]

The problem is that I get my base scrap sheet to come out OK, then two other books are created instead of one. I get one book with just one sheet called summary (exactly what I want), the problem is this shows up as book 3. There is a book 2 that is saved as bigmoverstest1, but I want book3 saved as that. Book2 has nothing on it except in A1 a few cells are selected and the corresponding formula says =EMBED("Excel.Chart.8","").


Do you know how I can get book 3 to save and why a third book is being created? The original sourcebook is created through a function. I tried to save just the workbook report book also, not reportbook.worksheets(1).

Thanks!

 

[sll810]

scrapbook.Worksheets("Summary").Activate
scrapbook.Worksheets("Summary").Copy
Set objNewWorkbook = xlapp.Workbooks(2)
objNewWorkbook.SaveAs Filename:=fname


I added this to try and save the second workbook which I now realize when I do the copy command it opens a new workbook with the sheet in it but it won't save. the set command doesn't work. I added a definition of it as excel.application and

Set xlapp = CreateObject("Excel.Application")

 

[Norie]

ActiveWorkbook just doesn't exist in Access VBA.

So this isn't right.

Set scrapbook = ActiveWorkbook

In fact you aren't even referencing an instance of the Excel application there, and as far as I can see you've not created one yet.