Create a new Excel worksheet in an exisiting workbook from Access (1 Viewer)

AChir

Registered User.
Local time
Today, 19:03
Joined
Oct 4, 2001
Messages
51
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
 

AChir

Registered User.
Local time
Today, 19:03
Joined
Oct 4, 2001
Messages
51
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

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

Remembered
Local time
Today, 19:03
Joined
Jun 8, 2005
Messages
8,632
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

Registered User.
Local time
Today, 19:03
Joined
Oct 4, 2001
Messages
51
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 :eek:
 

DCrake

Remembered
Local time
Today, 19:03
Joined
Jun 8, 2005
Messages
8,632
When you have opened the workbook in which you want to add a new worksheet use

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

Users who are viewing this thread

Top Bottom