HELP!!!! Copying excel worksheets via Access VBA

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
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!!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Is that all the code?

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

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
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:

Code:
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

Board Regular
Joined
Jun 29, 2007
Messages
86
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:

Code:
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

Board Regular
Joined
Jun 29, 2007
Messages
86
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:

Code:
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

Board Regular
Joined
Jun 29, 2007
Messages
86
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
ActiveWorkbook just doesn't exist in Access VBA.

So this isn't right.
Code:
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.:eek:
 

Forum statistics

Threads
1,181,362
Messages
5,929,534
Members
436,676
Latest member
Mavri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top