Create worksheet in Excel from Access 1

[RevenueSwede]

Hi!

I have a working function that produces a graph on one worksheet and the data is placed on another worksheet. Now I want to add a third worksheet. I'm confused by the syntax..

I have a workbookobject called xlWrkbk and I have put a Dim xlWks as Excel.Worksheet on the top

Set xlWks = xlWrkbk.Sheets.Add( it wants before, after as parameters. What are the parameters, I want it as number three.

 

[AlexCuse]

I think you need to create the worksheet, then give it a name, then obtain your object reference to that sheet.

hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

Now I want to add a third worksheet
If xlWrkbk.Sheets.Count = 2 Then
Set xlWks = xlWrkbk.Sheets.Add(, xlWrkbk.Sheets(2))
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RevenueSwede]

Yes that what I'm after. Copy and pasted part of the vba code produced by Excel macro and it worked as well.

Set xlWks = xlWrkbk.Worksheets.Add(Before:=Sheets(2))
xlWks.Name = "Data"
xlWks.Range("A1").Select
xlApp.ActiveCell = "testing"
xlApp.Sheets(1).Activate

Tomorrow is demo day of my app then on Wednesday, Austria here I come (skiing), then on Monday it's skiing in Sweden for a week

 

[PHV]

To avoid ghost excel.exe in your task manager, replace this:
Before:=Sheets(2)
with this:
Before:=xlWrkbk.Sheets(2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RevenueSwede]

Thanks for the help, yep I noticed excel.exe stayed in task manager.

 

[RevenueSwede]

So does this ghost excel.exe as well?

xls.Range(startCell, endCell).Select
With Selection.Interior
.ColorIndex = GetColor(rsPeriod!Category)
End With

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

 

[PHV]

Sure, replace this:
With Selection
with something like this:
With yourXLappObject.Selection

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RevenueSwede]

Wow! Thanks it works great now, you saved my day. Noticed that this seems to be a common problem when programming against Excel. The confusing part is that intellisense is lost for some properties which makes you think it's not going to work.

 

[PHV]

Bottom line, when you automate an application, always use full qualified objects ...