Problem with Excel reporting

[JackKaptijn]

I have a generic module for creating Excel reports.
This code works, but only once.....

 Set rstBron = dbOFA.OpenRecordset("SELECT Year([Date]) AS Year, Cint(Format([Date],'mm')) AS Month, Amount FROM Invoices"
  
 apXL.Visible = True
Set xlWsh = xlWbk.Worksheets(1)
xlWsh.Name = "Data"
 xlWsh.Range("A1").Select
        
For Each fld In rstBron.Fields
  apXL.ActiveCell = fld.Name
   apXL.ActiveCell.Offset(0, 1).Select
Next fld
            
rstBron.MoveFirst
xlWsh.Range("A2").CopyFromRecordset rstBron
            
apXL.Sheets.Add
Set xlWsh = xlWbk.Worksheets(1)
xlWsh.Name = "Table"
                
xlWbk.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
     "Data!A:C", Version:=xlPivotTableVersion12).CreatePivotTable _
     TableDestination:=xlWsh.Range("A3"), TableName:="Turnover", DefaultVersion _
                :=xlPivotTableVersion12
                
With xlWsh.PivotTables("Turnover").PivotFields("Year")
  .Orientation = xlColumnField
  .Position = 1
End With
 With xlWsh.PivotTables("Turnover").PivotFields("Maand")
   .Orientation = xlRowField
   .Position = 1
End With
            
xlWsh.PivotTables("Turnover").AddDataField xlWsh. _
    PivotTables("Turnover").PivotFields("Amount"), "Sum of Amount", xlSum
                
Set xlCht = apXL.Charts.Add()
With xlCht
    .Name = "Grafics"
End With

 xlCht.SetSourceData Source:=Sheets("Table").Range("A3")xlWbk.ShowPivotTableFieldList = False

When I close the Excel sheet and start it a second time, I get an error.
Error 1004, Methode Sheets of object _Global failed

It occurs at the end...at the line:
xlCht.SetSourceData Source:=Sheets("Table").Range("A3")

Anyone a suggestion?

Jack

 

[JHB]

I think you are missing the Excel object in front of Sheets(".., because "Sheets" doesn't means anything in MS-Access.

xlCht.SetSourceData Source:=[B][COLOR=Red]apXL.[/COLOR][/B]Sheets("Table").Range("A3")

 

[JackKaptijn]

Sorry, a piece of code was missing. At the beginning af the module is:

     Dim apXL As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Dim xlCht As Excel.Chart
    
    Set apXL = CreateObject("Excel.Application")
    Set xlWbk = apXL.Workbooks.Add
     Set dbOFA = CurrentDb

So that's not the problem.....

 

[JHB]

That you have declare and set some variables but it doesn't help if you don't use them.
You've the code line below:

xlCht.SetSourceData Source:=Sheets("Table").Range("A3")

Why do you think MS-Access knows what "Sheets" is?
Did you try what I suggested?

 

[JackKaptijn]

Thanks JHB!
Your solution works.
What I still don't understand is why it works the first time.

 

[JHB]

..
What I still don't understand is why it works the first time.

As I answer in another thread with the exact the same problem:

Yes it is a pity that MS-Access allows it to run the first time though lacking references to Excel in the code, because it misleads people into thinking the code is okay, which it is not.

Good you got it to work, luck with it, (then I'll forget the glance of ignorance you gave my first answer. ).

 

[Rx_]

Yep, that is it exactly. Been there done that.
It runs the first time with out an object reference, then totally fails the 2nd time.

Just a reminder for anyone who is searching for a solution later.
When running Excel Automation on Citrix or Remote Automation this can be a huge problem. The lack of a reference can spawn a new instance of Excel. So, the failure can leave an "orphaned" Excel instance on the server.

For a desktop instance that probably gets re-booted every evening, not a huge deal.
For a Server with 50 users who run multiple reports a day, this can add up on the server.

Wearing my Quality Assurance hat, let me strongly recommend testing Excel (Word, Powerpoint, ...) remote automation twice to see if fails the second time.
Likewise, be sure to go into Task Manager to determine if the Excel.Exe closes as the object variable is set to Nothing.

Once place to watch for this is in using the recorder or using more advanced functions. Example look at the CountIf function objXL.Range("G:G"

''Color duplicate items between columns G and H
'    For i = 1 To objXL.Range("H" & LastRow).End(xlUp).ROW
'         If objXL.Application.WorksheetFunction.CountIf(objXL.Range("G:G"), objXL.Range("H" & i)) = 1 Then
'            With objXL.Range("H" & i).Font
'                .ColorIndex = 4 ' green
'                .Bold = True
'            End With
'         End If
'    Next i

Using the Excel Recorder will just add .Range

Congratulations on finding your error. However, with the great power of Excel automation from Access comes the great responsibility of learning when to add the extra application reference.