Method of range of object Global failed

[ili_sophia]

Hello,

I would like to export my access data to excel which also creates a graph based on the exported data. I have recorded my macro in excel and added it to my export command button on my userform.

The chart was able to be successfully created the first time i use the export command button but the second time it only export the data without the graph and it gives the error of Method of range of object Global failed.

The error pops up every other time i use the export command button

Does anyone know why this is happening?

Also is there a way to create the graph without having a specific range? Because the exported data might not always be the same number of rows


my code for the export command button:
Private Sub DyeingExportExcel_Click()
Dim sht As Object

If Me.Dirty Then Me.Dirty = False

Dim rsClone As DAO.Recordset

Set rsClone = Me.subDyeing.Form.RecordsetClone

If (rsClone.BOF And rsClone.EOF) Then
MsgBox "No records found."
Set rsClone = Nothing
Exit Sub
End If

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Add

Set sht = .Sheets("Sheet1")

sht.Activate

' put the data first
rsClone.MoveFirst
sht.Range("A2").CopyFromRecordset rsClone


' next put the Column Header
For i = 1 To rsClone.Fields.count
sht.Cells(1, i).Value = rsClone.Fields(i - 1).Name
Next i


With sht
Dim lngRow As Long

' instead well use the Recordcount
lngRow = rsClone.RecordCount + 1

.Range("$A$" & lngRow + 2).Value = "Total:"
' 1. Budget (Sample Machine)
.Range("$B$" & lngRow + 2).Formula = "=Sum($B$2:$B$" & lngRow & ")"

' 2. Actual (Sample Machine)
.Range("$C$" & lngRow + 2).Formula = "=Sum($C$2:$C$" & lngRow & ")"

.Range("$D$" & lngRow + 2).Formula = "=Sum($D$2:$D$" & lngRow & ")"

' 3. Budget (Mass Machine)
.Range("$E$" & lngRow + 2).Formula = "=Sum($E$2:$E$" & lngRow & ")"

' 4. Actual (Mass Machine)
.Range("$F$" & lngRow + 2).Formula = "=Sum($F$2:$F$" & lngRow & ")"

.Range("$G$" & lngRow + 2).Formula = "=Sum($G$2:$G$" & lngRow & ")"

' 5. Hours
.Range("$H$" & lngRow + 2).Formula = "=Sum($H$2:$H$" & lngRow & ")"

' 6. Mass Machine Capacity
.Range("$I$" & lngRow + 2).Formula = "=Sum($I$2:$I$" & lngRow & ")"

' 7. Mass Machine Used
.Range("$J$" & lngRow + 2).Formula = "=Sum($J$2:$J$" & lngRow & ")"

' insert calculated columns
Dim j As Integer

xlApp.Columns("D").Select
xlApp.Selection.Insert Shift:=-4152
xlApp.Selection.NumberFormat = "0%"

.Range("D1") = "Sample Machine Efficiency (LBS)"
For j = 2 To lngRow
.Range("D" & j).Formula = "=IF(C" & j & "=0,0,C" & j & "/B" & j & ")"
.Range("$D$" & lngRow + 2).Formula = "=Sum(C" & lngRow + 2 & "/B" & lngRow + 2 & ")"
Next

xlApp.Columns("G:G").Select
xlApp.Selection.Insert Shift:=-4152
xlApp.Selection.NumberFormat = "0%"
.Range("G1") = "Mass Machine Efficiency"
For j = 2 To lngRow
.Range("G" & j).Formula = "=IF(F" & j & "=0,0,F" & j & "/E" & j & ")"
.Range("$G$" & lngRow + 2).Formula = "=Sum(F" & lngRow + 2 & "/E" & lngRow + 2 & ")"
Next

xlApp.Columns("K:K").Select
xlApp.Selection.Insert Shift:=-4152
xlApp.Selection.NumberFormat = "0%"
.Range("K1") = "Mass Machine Utilization"
For j = 2 To lngRow
.Range("K" & j).Formula = "=IF(J" & j & "=0,0,J" & j & "/I" & j & ")"
.Range("$K$" & lngRow + 2).Formula = "=AVERAGE($K$2:$K$" & lngRow & ")"

Next

End With

' autofit Column
xlApp.Cells.EntireColumn.AutoFit

' NOW, the formatting of each column

xlApp.Columns("A:A").Select
xlApp.Selection.NumberFormat = "d-mmm-yy"

xlApp.Columns("C:C").Select
xlApp.Selection.NumberFormat = "#,##0"

xlApp.Columns("F:F").Select
xlApp.Selection.NumberFormat = "#,##0"

xlApp.Columns("E:E").Select
xlApp.Selection.NumberFormat = "#,##0"

xlApp.Columns("H:H").Select
xlApp.Selection.NumberFormat = "#,##0"


xlApp.Cells(2, 1).Select

' Freeze the Column Header
xlApp.ActiveWindow.FreezePanes = True

End With

Call DyeingChart

End Sub

My Macro:
Sub DyeingChart()
'
' Macro4 Macro
'

'
Range("A18").Select
ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(3).ChartType = xlLine
ActiveChart.FullSeriesCollection(3).AxisGroup = 2
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Sample Production Output Efficiency"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Sample Production Output Efficiency"
With Selection.Format.TextFrame2.TextRange.Characters(1, 35).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 17).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(18, 18).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
Range("E1:G8").Select
Range("G1").Activate
ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(3).ChartType = xlLine
ActiveChart.FullSeriesCollection(3).AxisGroup = 2
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Mass Pro "
Selection.Format.TextFrame2.TextRange.Characters.Text = "Mass Pro "
With Selection.Format.TextFrame2.TextRange.Characters(1, 9).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(5, 5).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
ActiveChart.Parent.Delete
Range("A1:A8,E1:G8").Select
Range("G1").Activate
ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(3).ChartType = xlLine
ActiveChart.FullSeriesCollection(3).AxisGroup = 2
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Mass Production Output Efficiency"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Mass Production Output Efficiency"
With Selection.Format.TextFrame2.TextRange.Characters(1, 33).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 15).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(16, 18).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
End Sub

 

[Cronk]

There is a lot of lines of code there. Which one gives the error?

 

[ili_sophia]

the error that is highlighted: Range("A18").Select

The first line under macro

 

[JHB]

You need the reference to Excel, like you've it in your first Sub.
Something like below.

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

 

[ili_sophia]

Im not sure on how to set the reference

I tried to
Dim ActiveChart as chartobject and it did not work

 

[JHB]

It hasn't to do with the graph object, but with the reference to Excel. You need them all in the second Sub, (DyeingChart()), like you've them in the first Sub.

An example:

..
[B][COLOR=Red]ReferenceToExcel.[/COLOR][/B]Range("A1[IMG]https://accessworld-accessworld.netdna-ssl.com/forums/images/smilies/biggrin.gif[/IMG]8").Select
[B][COLOR=Red]ReferenceToExcel.[/COLOR][/B]ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
..

 

[ili_sophia]

it gives me the error of object required and it highlights the first line:
ReferenceToExcel.Range("A18").Select

 

[JHB]

You don't understand what I'm writing about, I'll try a last time.
In you first sub you've:

..
Dim xlApp As Object
'[B][COLOR=Red]Here you set the reference to Excel[/COLOR][/B]
Set xlApp = CreateObject("Excel.Application")

You need the reference in you second sub and the way you've build your code you need to transfer in, (remember to correct "A1

8") :

Sub DyeingChart([B][COLOR=Red]xlApp As Object[/COLOR][/B])
[B][COLOR=Red]xlApp.[/COLOR][/B]Range("A1[IMG]https://accessworld-accessworld.netdna-ssl.com/forums/images/smilies/biggrin.gif[/IMG]8").Select
[B][COLOR=Red]xlApp.[/COLOR][/B]ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
...
[B][COLOR=Red]'Remember to add the reference to all the code lines where you are doing something in Excel.
[/COLOR][/B]

And in the first Sub change the following code line from:

Call DyeingChart

To:

Call DyeingChart([B][COLOR=Red]xlApp)[/COLOR][/B]

If you can't get it post your database with some sample data.

 

[ili_sophia]

thanks JHB for explaining to me step by step

I have another macro but there seem to be the same error of method Range of object global fail for this particular line.
I have used the custom combo chart to create it

I have highlighted the line in red

My macro:
Sub PrintingChart(xlApp)
'
' PrintingChart Macro
'

'
xlApp.Range("A1:B7,I1:J7").Select
xlApp.Range("I1").Activate
xlApp.ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
xlApp.ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
xlApp.ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
xlApp.ActiveChart.FullSeriesCollection(3).ChartType = xlLine
xlApp.ActiveChart.FullSeriesCollection(3).AxisGroup = 2
xlApp.ActiveChart.ChartTitle.Select
xlApp.ActiveWindow.ScrollColumn = 3
xlApp.ActiveWindow.ScrollColumn = 2
xlApp.ActiveWindow.ScrollColumn = 1
xlApp.ActiveChart.ChartTitle.Text = "Production Output Efficiency"
xlApp.Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Production Output Efficiency"
With xlApp.Selection.Format.TextFrame2.TextRange.Characters(1, 28).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With xlApp.Selection.Format.TextFrame2.TextRange.Characters(1, 10).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
With xlApp.Selection.Format.TextFrame2.TextRange.Characters(11, 18).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
xlApp.Range("A1:A7,D1:H7").Select
xlApp.Range("D1").Activate
xlApp.ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
xlApp.ActiveChart.SetSourceData
Source:=Range("Sheet1!$A$1:$A$7,Sheet1!$D$1:$H$7")
xlApp.ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
xlApp.ActiveChart.FullSeriesCollection(1).AxisGroup = 1
xlApp.ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
xlApp.ActiveChart.FullSeriesCollection(2).AxisGroup = 1
xlApp.ActiveChart.FullSeriesCollection(3).ChartType = xlColumnClustered
xlApp.ActiveChart.FullSeriesCollection(3).AxisGroup = 1
xlApp.ActiveChart.FullSeriesCollection(4).ChartType = xlLine
xlApp.ActiveChart.FullSeriesCollection(4).AxisGroup = 1
xlApp.ActiveChart.FullSeriesCollection(5).ChartType = xlLine
xlApp.ActiveChart.FullSeriesCollection(5).AxisGroup = 1
xlApp.ActiveChart.FullSeriesCollection(3).ChartType = xlLine
xlApp.ActiveChart.FullSeriesCollection(4).ChartType = xlColumnClustered
xlApp.ActiveChart.FullSeriesCollection(3).AxisGroup = 2
xlApp.ActiveChart.FullSeriesCollection(5).AxisGroup = 2
xlApp.ActiveChart.ChartTitle.Select
xlApp.ActiveChart.ChartTitle.Text = "Production Output Efficiency"
xlApp.Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Production Output Efficiency"
With xlApp.Selection.Format.TextFrame2.TextRange.Characters(1, 28).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With xlApp.Selection.Format.TextFrame2.TextRange.Characters(1, 17).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
With xlApp.Selection.Format.TextFrame2.TextRange.Characters(18, 11).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With
End Sub

 

[JHB]

..
I have another macro but there seem to be the same error of method Range of object global fail for this particular line.
...

Yes because you need the reference to Excel:
You've:

xlApp.ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$A$7,Sheet1!$D$1:$H$7")

Range is a thing that is allocated to Excel, so ..:.

xlApp.ActiveChart.SetSourceData Source:=[B][COLOR=Red]xlApp.[/COLOR][/B]Range("Sheet1!$A$1:$A$7,Sheet1!$D$1:$H$7")