Create a new Excel worksheet in an exisiting workbook from Access

[AChir]

I am exporting versions of a table from Access to Excel each month and would like to use an existing workbook by adding a new sheet (and naming the sheet appropriately). I have drawn a blank so far. Any advice or pointers gratefull received. Thanks for your support

 

[ajetrumpet]

you can get the portion of the code you need from here:

http://www.access-programmers.co.uk/forums/showthread.php?goto=newpost&t=191123

 

[AChir]

Thanks for this, Adam. I had already tried to use your code and it works really well the first time but I can't seem to adapt it to add a sheet to an existing spreadsheet or to adapt the code I had previously to add a sheet and rename it.

I have tried tinkering with

Set wkbk = xl.Workbooks.Add
'[COLOR="YellowGreen"] but I don't think I want to add a new workbook as I have one[/COLOR]
    With wkbk
       .SaveAs FileName:="NEW EXCEL FILE PATH"
'[COLOR="YellowGreen"] I want to open it on an existing path[/COLOR]

but don't understand what I am doing well enough to get any further.

 

[DCrake]

A a matter of interest Adding workbooks is add ing a new workbook Adding worksheets is adding worksheets to existing workbooks. Are you doing the adding at the wrong point?

 

[AChir]

I have reveretd to creating a new workbook at present purely because I can't seem to add a worksheet to an existing book... Feeble, but true

 

[DCrake]

When you have opened the workbook in which you want to add a new worksheet use

                    .Sheets.Add after:=Sheets(Sheets.Count)
                    .Sheets(Sheets.Count).Name = "Your Month"

Alternatively why don't you first create a workbook with 12 monthly worksheets pre added and populate the appropriate one.