Error 424 in xl.Workbooks.Open(sExcelWB)

**seb** · 10-21-2018, 10:19 AM

Hello.
I need to export a query to MS Excel.
In order to test if my query opens, I use below piece of code and works perfectly.

Code:

DoCmd.OpenQuery "qry_123", , acReadOnly

However, trying to execute the following code and Error occurred in line:

Code:

Set wb = xl.Workbooks.Open(sExcelWB)

Here is my full code:

Code:

Option Compare DatabaseOption Explicit
Sub cmbexport_toexcel_Click()
Dim xl, wb, ws, ch, mychart, qry_123, Target As Object ''ch is excel chart
Dim sExcelWB As String


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


sExcelWB = CurrentProject.Path & "qry_123"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_123", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB) '' here error 424 ocurred
Set ws = wb.Sheets("qry_123")
Set ch = ws.Shapes.AddChart58
Set mychart = ws.ChartObjects("Chart 1")
ws.Columns.Autofit
ws.Columns("B:C").HorizontalAlignment = xlCenter
ws.Columns(4).TextToColumns , , , , -1, 0, 0, 0
ws.Columns(5).TextToColumns , , , , -1, 0, 0, 0


wb.Save
xl.Visible = True
xl.UserControl = True
Set ws = Nothing
Set wb = Nothing


End Sub

I am worried due I cannot test my code after:

Code:

Set wb = xl.Workbooks.Open(sExcelWB)

I spent hours googling to find a solution with no success.

I hope someone helps me.

Cheers

**June7** · 10-21-2018, 12:05 PM

Variable datatypes must be explicitly declared else they default to Variant. So only the last variable is declared as Object.

Application has two p's. What does ":confused:" mean? Was that supposed to be a smiley?

Set xl = CreateObject("excel.application")

**CJ_London** · 10-21-2018, 12:08 PM

it always helps if you provide the description for the error - it will often provide a clue. I haven't looked it up so this is a guess - perhaps this

sExcelWB = CurrentProject.Path & "qry_123"

needs to be

sExcelWB = CurrentProject.Path & "\qry_123.xlsx"

**seb** · 10-21-2018, 01:52 PM

Thank you for your prompt reply.
Your correct. Error 424 an object is required is gone

writing:

Code:

Set xl = CreateObject("excel.application")

Now to run the code, I had to erase 58 in this line

Code:

Set ch = ws.Shapes.AddChart

VBA, displayed error 438 "object doesn't support this property or method occurred in the above line.
I was wrong thinking that VBA will create a Chart58.

Last question if possible. What is the meaning of characters to the wrigth of TextToColumns?

Code:

ws.Columns(4).TextToColumns , , , , -1, 0, 0, 0

Cheers

**CJ_London** · 10-21-2018, 02:01 PM

I'm not an expert in excel vba, so googled addchart and found this

http://excelvbatutor.com/index.php/e...phs-excel-vba/

similarly for texttocolumns there is this

https://docs.microsoft.com/en-us/off....texttocolumns

google yourself and you will find plenty of links on both functions

**seb** · 10-22-2018, 12:14 AM

Thank you.
I am working with MS Access.
I really appreciate your reply.

With Charts, with this lines of code, I am looking how export query to excel and create an xlBarStacked:

Code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryname", sExcelWB, True
Set ch = ws.Shapes.AddChart

Having in mind that the first series of data is:

Code:

Range("A2", Range("A2").End(xlDown))

and the second series of data is:

Code:

Range("C2", Range("C2").End(xlDown))

I'm still looking how to create in Excel Worksheet an xlBarStacked directly from MS Access.

I hope someone can help me.

The second link is very useful, I could not find it.

Cheers

**CJ_London** · 10-22-2018, 01:59 AM

Sorry I can't help with the excel related vba, perhaps someone else can step in.

Not sure what this means

The second link is very useful, I could not find it.

if you couldn't find it, how was it useful?

Did you google the two functions? I found pages of links on them. If not sure how to google, try searching for 'Excel VBA texttocolumns'

**seb** · 10-22-2018, 02:25 AM

Thank you.
If I find a solution, I will share here.
Cheers

**June7** · 10-22-2018, 03:19 AM

I got this much to work:

Code:

Sub cmbexport_toexcel_Click()
Dim xl As Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet
Dim sExcelWB As String

sExcelWB = CurrentProject.Path & "\qry_123.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_123", sExcelWB, True
On Error Resume Next
Set xl = CreateObject("excel.application")
Err.Clear
On Error GoTo 0
Set wb = xl.Workbooks.Open(sExcelWB)
xl.Visible = True
xl.UserControl = True
Set ws = wb.Sheets("qry_123")

'ws.Columns.AutoFit
'ws.Columns("B:C").HorizontalAlignment = xlCenter
'ws.Columns(4).TextToColumns , , , , -1, 0, 0, 0
'ws.Columns(5).TextToColumns , , , , -1, 0, 0, 0

ws.Shapes.AddChart.Select
ws.ChartObjects(1).Chart.ChartType = xlColumnStacked

'wb.Save
Set ws = Nothing
Set wb = Nothing
End Sub

**seb** · 10-22-2018, 10:32 PM

June.
With your valuable help now my code works correctly.

I am very happy to know another way to export a query to Excel and create a Chart.

Thank you very much indeed.

On the other hand, I have been working in chart edition

such as:
(i) Create xlBarStacked
(ii) Add new data series
(iii), Barr colour
(iv) Axis min-max values

I think I have to send another post.

Cheers

**June7** · 10-23-2018, 02:20 AM

I experimented a little with setting colors, review: https://www.accessforums.net/showthread.php?t=29178

I also have code that sets axis scale. Here is an excerpt:

Code:

'format y axis scale
If booMetric = True Then
    intMaxD = intMaxD + IIf(intMaxD - intMinD < 125, 50, IIf(intMaxD - intMinD < 250, 25, 0))
    .Axes(xlValue).MaximumScale = intMaxD
    .Axes(xlValue).MinimumScale = intMaxD - 250
    .Axes(xlValue).MajorUnit = 50
    .Axes(xlValue).MinorUnit = 10
Else
    intMaxD = intMaxD + IIf(intMaxD - intMinD < 6, 2, IIf(intMaxD - intMinD < 10, 1, 0))
    .Axes(xlValue).MaximumScale = intMaxD
    .Axes(xlValue).MinimumScale = intMaxD - 10
    .Axes(xlValue).MajorUnit = 2
    .Axes(xlValue).MinorUnit = 0.4
End If
'format x axis scale
If Int(dblOMC) > 6 Then
    intM = Int(dblOMC) + IIf(dblOMC - Int(dblOMC) >= 0.5, 1, 0)
    .Axes(xlCategory).MaximumScale = intM + 7
    .Axes(xlCategory).MinimumScale = intM - 5
End If

All code manages Access graphs, not Excel, but should be adaptable.

@Ajax, I think OP meant they couldn't find page on their own. The statement threw me at first as well.

**seb** · 10-23-2018, 09:52 AM

June7. Thanks againg.
Your post about Access Charts is very interesting, I hope to study it.

Going back to my code, I think a have a door to edit xlBarStacked Chart.

Instead of using these lines of code

Code:

ws.Shapes.AddChart.Select
ws.ChartObjects(1).Chart.ChartType = xlColumnStacked

I am trying to test:

Code:

Dim a, b As RangeDim MinVal, MaxVal As Double
Dim xlch As Excel.ChartObject


Set xlch =

My problem is that I don't know what I have to write after Set xlch = ???????. Here Intellisense is not working
I think after "=" I have to write for example ChartOjects.Add(left, top, width, height).

Now If I have the correct code after "=" I can be able to teste the With xlch - End With blocks as follow:

Code:

Set xlch = xxxx(left, top, width, height)With xlch
        .RoundedCorners = True
        With .Chart
            .ChartType = xlBarStacked
            .HasTitle
            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", Range("A2").End(xlDown))
            .SeriesCollection(1).XValues = ws.Sheet.Range("C2", Range("C2").End(xlDown))


            .SeriesCollection.NewSeries
            .SeriesCollection(2).Values = wsSheet.Range("D2", Range("D2").End(xlDown))
            .Axes(xlCategory).ReversePlotOrder = True


	    'Set axis min-max values
             Set a = Range("A2:A" & Rows.Count)
             Set b = Range("A2:B" & Rows.Count)
             MinVal = WorksheetFunction.Min(a)
             MaxVal = WorksheetFunction.Max(b)
             ch.Axes(xlValue).MinimumScale = MinVal
             ch.Axes(xlValue).MaximumScale = MaxVal


          End With    'end .Chart
End With    'end xlch

I got the above code from excel worksheet with the macro recording on.

However, I found something that keeps my attention. In some example

Code:

seriescollection

is written:

Code:

.fullseriescollection

In other examples the code is written:

Code:

.series collectionm

Can you tell me the difference?

I really appreciate your help

Cheers

**June7** · 10-23-2018, 01:47 PM

No idea. That is beyond my experience.

**seb** · 10-23-2018, 02:04 PM

Thank you

I've googled for hours, but none of the suggestions helped.
Anybody know how to complete Set xlch = ????? and make it run?

Cheers

**seb** · 10-23-2018, 04:14 PM

Hello

Going back to my first question in this post, I found this code and works fine

Code:

Set xlChart = ws.ChartObjects.Add(300, 0, 500, 300)

But in this line:

Code:

.SeriesCollection.NewSeries

Error 438 object doesn't support this property or method ocurred.

Can someone please tell me how I'm going wrong?

Cheers

Error 424 in xl.Workbooks.Open(sExcelWB)

Thread Tools

Error 424 in xl.Workbooks.Open(sExcelWB)

Similar Threads

Workbooks Activate - script out of range error

Subtotalling multiple workbooks

Importing alot of Excel Workbooks at once

Error In Code (Aggregating Data from All Excel Workbooks in Folder)

Macro runs query that exports to Excel, now how to open additional Excel Workbooks?

Tags for this Thread

Posting Permissions