Error 424 in xl.Workbooks.Open(sExcelWB)

[June7]

Recall from post 2: "Variable datatypes must be explicitly declared else they default to Variant. So only the last variable is declared as Object." So in the code:

Dim a, b As Range
Dim MinVal, MaxVal As Double

Only the second variable on each line is explicitly declared, the first ones default to Variant. Code will probably still work, but it is a fine point to be aware of.

Following runs without error. I see the chart is modified, even with my data.

Sub cmbexport_toexcel_Click()
Dim xl As Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet, xlch As Excel.ChartObject
Dim a As Excel.Range, b As Excel.Range
Dim sExcelWB As String
Dim MinVal As Double, MaxVal As Double

On Error Resume Next
Set xl = CreateObject("excel.application")
Err.Clear
On Error GoTo 0

sExcelWB = CurrentProject.Path & "\qry_123.xlsx"
xl.Visible = True
xl.UserControl = True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_123", sExcelWB
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_123")
ws.Columns.AutoFit
ws.Columns("B:C").HorizontalAlignment = xlCenter
ws.Shapes.AddChart.Select
Set xlch = ws.ChartObjects(1)
With xlch
        .RoundedCorners = True
        With .Chart
            .ChartType = xlBarStacked
            .HasTitle = True
            With .ChartTitle
                .Text = "Title"
                With .Font  'begin font
                    .Name = "Arial"
                    .Size = 14
                    .FontStyle = "bold"
                End With ''end font
            End With    'end chart title
            
            .HasLegend = False
                        
'            begin set source data
            .SeriesCollection.NewSeries
            .SeriesCollection(1).Values = ws.Range("A2", ws.Range("A2").End(xlDown))
            .SeriesCollection(1).XValues = ws.Range("C2", ws.Range("C2").End(xlDown))
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Values = ws.Range("D2", ws.Range("D2").End(xlDown))
            .Axes(xlCategory).ReversePlotOrder = True
        'Set axis min-max values
             Set a = ws.Range("A2:A" & Rows.Count)
             Set b = ws.Range("A2:B" & Rows.Count)
             MinVal = WorksheetFunction.Min(a)
             MaxVal = WorksheetFunction.Max(b)
             .Axes(xlValue).MinimumScale = MinVal
             .Axes(xlValue).MaximumScale = MaxVal
          End With    'end .Chart
End With    'end xlch
'wb.Save
Set ws = Nothing
Set wb = Nothing
End Sub

[seb]

Hello.'
I really appreciated your reply.

Including your recommendation and executing the code, the query and the Chart are displayed, but 'Error 434 Object does not support this property or method', occurred here:

.SeriesCollection.NewSeries

I have no idea to fix this error.

[June7]

What I am encountering is a residual Excel instance running in background, showing in Task Manager, even when Excel is not open. If I manually end the task, the VBA does not run through the first time, I manually close Excel without saving workbook, run code again and it works. Close Excel and the instance appears again. And repeat. This is a known issue of manipulating Excel from Access VBA and can be very frustrating. See http://www.utteraccess.com/forum/ind...pic=2051224&hl

Changed my code to not display Excel and now I get the same error you get (and code is much slower). But now when I manual end Excel instance it stays ended.

[seb]

Hello.
I already fixed the Error moving the last two blocks of code here

 With .Chart            .ChartType = xlBarStacked
            .HasTitle = True
            .HasLegend = False
                
            .SeriesCollection.NewSeries
            .SeriesCollection(1).Values = ws.Range("A2", ws.Range("A2").End(xlDown))
            .SeriesCollection(1).XValues = ws.Range("C2", ws.Range("C2").End(xlDown))
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Values = ws.Range("D2", ws.Range("D2").End(xlDown))
            .Axes(xlCategory).ReversePlotOrder = True
            
            Set a = ws.Range("A2:A" & Rows.Count)
            Set b = ws.Range("A2:B" & Rows.Count)
            MinVal = WorksheetFunction.Min(a)
            MaxVal = WorksheetFunction.Max(b)
            .Axes(xlValue).MinimumScale = MinVal
            .Axes(xlValue).MaximumScale = MaxVal
            
            With .ChartTitle
'
                .Text = "Plot " & Me.txtplot.Value & " " & " Task Calendar" & vbLf & _
                "Between" & " " & "(" & Me.txtfrom.Value & " And" & Me.txtto.Value & ")"
                With .Font
                    .Name = "Arial"
                    .FontStyle = "Bold"
                    .Size = 12
                End With  ''end fon

Now code

.chartType =xlBarStacked

displays by default the CurrentRegion Bars at a time with my lines of code.

In the chart I need to delete this bars:

=SERIES(qry_123!$E$1;qry_123!$A$2:$B$15;qry_123!$E$2:$E$15;3)

and set invisible this line:

=SERIES(qry_123!$C$1;qry_123!$C$2:$C$15;qry_123!$A$2:$A$15;1)

I have no idea how to do it.

Finally, with your last paragraph, I have to thanks all your help.

Cheers

[June7]

No longer getting the residual Excel instance. But got an error on the .ChartTitle line. Had to add just before the title block: .SetElement (msoElementChartTitleAboveChart)

You need to delete a series? From a thread I found:

"The basic code would be:
Code:

Dim n As Long
   With ActiveChart
          For n = .SeriesCollection.Count To 1 Step -1
                 .SeriesCollection(n).Delete
          Next n
   End With

but it might be easier to use SetSourceData to change the data range."


Don't know what you mean by 'set invisible this line'.

[seb]

June7.
I apologize for my unclear explanation. I mean for "invisible:

.SeriesCollection(1).Format.Fill.Visible = msoFalse

A few minutes ago, I finished the SeriesCollection code for my xlBarStacked graphic form MS Access.
I really appreciate your help.
Cheers

Here, I share the code if some:

ws.Shapes.AddChart.Select
Set xlChart = ws.ChartObjects(1)
    With xlChart
        .RoundedCorners = True
        
            With .Chart
                .ChartArea.Height = 300
                .ChartArea.Width = 500
                .ChartArea.Top = 1
                .ChartType = xlBarStacked
                .HasLegend = False
                .HasTitle = True
                
                For n = .SeriesCollection.Count To 1 Step -1
                    .SeriesCollection(n).Delete
                Next n
                
                .SeriesCollection.NewSeries
                .SeriesCollection(1).Values = ws.Range("A2", ws.Range("A2").End(xlDown))
                .SeriesCollection.NewSeries '
                .SeriesCollection(2).Values = ws.Range("C2", ws.Range("C2").End(xlDown))
                .SeriesCollection(2).XValues = ws.Range("E2", ws.Range("E2").End(xlDown))


                Set a = Range("A2:A" & Rows.Count)
                Set b = Range("A2:B" & Rows.Count)
                MinVal = WorksheetFunction.Min(a)
                MaxVal = WorksheetFunction.Max(b)
                .Axes(xlValue).MinimumScale = MinVal
                .Axes(xlValue).MaximumScale = MaxVal
                .Axes(xlCategory).ReversePlotOrder = True
                .SeriesCollection(1).Format.Fill.Visible = msoFalse
                    
                    With .ChartTitle
                        .Text = "Bla bla"
                    End With
            End With
    
    End With