Method 'Columns' of object '_Global' failed???

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have a on-click event on a button which will generate the Excel report on
the fly. It is working fine for one time only each time when opening the
Access
application and it will fail the second attempt. I have to close the Access
application and open it again and it will open the Excel report. If I try to
create the Excel report in the second time, it will return with an error
"Method 'Columns' of object '_Global' failed" Thanks.
 
Paul wrote in message said:
I have a on-click event on a button which will generate the Excel report on
the fly. It is working fine for one time only each time when opening the
Access
application and it will fail the second attempt. I have to close the Access
application and open it again and it will open the Excel report. If I try to
create the Excel report in the second time, it will return with an error
"Method 'Columns' of object '_Global' failed" Thanks.
Click to expand...

Hi,

I think this is most likely caused by implicit referencing of Excel
objects,
properties or methods, see
http://support.microsoft.com/default.aspx?kbid=178510
I wouldn't be surprised if you've got an extra instance of Excel
running,
too, check Task Manager.
 
Thanks for replying....here is my code

Private Sub BtnExcelStore_Click()
Dim loqd As QueryDef
Dim stSQL As String
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim stLinkCriteria As String
Dim stWhat As String
Dim stCriteria As String
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Dim strPurchaseOrder As String
Dim strStore As String
Dim strHDDescription As String
Dim strSize As String
Dim strSKU As String
Dim strQuantity As Long
On Error GoTo BtnExcelStore_Click_Err

stWhat = "": stCriteria = ","
Set frm = [Forms]![Frm_Pull Sheet]
Set ctl = frm!lstStorePO
For Each varItem In ctl.ItemsSelected
stWhat = stWhat & "'" & ctl.ItemData(varItem) & "'"
stWhat = stWhat & stCriteria
Next varItem
stLinkCriteria = CStr(Left$(stWhat, Len(stWhat) - Len(stCriteria)))

Set loqd = CurrentDb.QueryDefs("Qry_Store PO")
stSQL = "SELECT Orders.OrderID, Orders.PurchaseOrder, Orders.Status " &
_
"FROM Orders INNER JOIN Order_Details ON Orders.OrderID =
Order_Details.OrderID " & _
"WHERE (((Orders.PurchaseOrder) IN (" & stLinkCriteria & ")) And
((Orders.Status) = 1) And ((Orders.StoreID)= " & [Forms]![Frm_Pull
Sheet]![cboStore] & ")) " & _
"ORDER BY Orders.PurchaseOrder;"
loqd.SQL = stSQL
loqd.Close

Set rs1 = CurrentDb.OpenRecordset("SELECT DISTINCT [Qry_Store Purchase
Order Pull Sheet].Store, [Qry_Store Purchase Order Pull Sheet].PurchaseOrder
" & _
"FROM [Qry_Store Purchase Order Pull
Sheet] " & _
"ORDER BY [Qry_Store Purchase Order
Pull Sheet].PurchaseOrder;", dbOpenSnapshot)

Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Name = "Pull Sheet"
'Format WorkSheet and assign Store #, Name to Excel
.Columns("A:A").Select
With Selection
.ColumnWidth = 31
End With
.Columns("B:D").Select
With Selection
.ColumnWidth = 10
End With
.Columns("E:E").Select
With Selection
.ColumnWidth = 25
End With
.Columns("F:G").Select
With Selection
.ColumnWidth = 5
End With
.Columns("A:G").Select
With Selection.Font
.Name = "Comic Sans MS"
.SIZE = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A1").Select
ActiveCell.FormulaR1C1 = "HOME DEPOT:"
With ActiveCell.Characters(Start:=1, Length:=25).Font
.Name = "Comic Sans MS"
.FontStyle = "Regular"
.SIZE = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("A2").Select
ActiveCell.FormulaR1C1 = "SHIP DATE:"
With Selection.Font
.Name = "Comic Sans MS"
.SIZE = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("B1:G1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range("B2:G2").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A4:G5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A4").Select
ActiveCell.FormulaR1C1 = "PLANT DESCRIPTION"
Range("B4").Select
ActiveCell.FormulaR1C1 = "SIZE"
Range("C4").Select
ActiveCell.FormulaR1C1 = "SKU"
Range("D4").Select
ActiveCell.FormulaR1C1 = "QTY."
Range("E4").Select
ActiveCell.FormulaR1C1 = "LOCATION"
Range("F4").Select
ActiveCell.FormulaR1C1 = "TAGS"
Range("G4").Select
ActiveCell.FormulaR1C1 = "TAGS"
Range("F5").Select
ActiveCell.FormulaR1C1 = "PSL"
Range("G5").Select
ActiveCell.FormulaR1C1 = "PIC"
Range("A5:G5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone

Range("B1").Select
ActiveCell.FormulaR1C1 = rs1!Store
With ActiveCell.Characters(Start:=1, Length:=25).Font
.Name = "Comic Sans MS"
.FontStyle = "Regular"
.SIZE = 16
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A6").Select
If Not rs1.BOF Then
rs1.MoveFirst
Do While Not rs1.EOF
strPurchaseOrder = rs1!PurchaseOrder
'Assign Purchase Order to Excel
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "PURCHASE ORDER:"
With ActiveCell.Characters(Start:=1, Length:=25).Font
.Name = "Comic Sans MS"
.FontStyle = "Regular"
.SIZE = 16
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

ActiveCell.Offset(0, 1).Select
Range(ActiveCell.Offset(0, 0).Address & ":" &
ActiveCell.Offset(0, 4).Address).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
ActiveCell.FormulaR1C1 = strPurchaseOrder
With ActiveCell.Characters(Start:=1, Length:=25).Font
.Name = "Comic Sans MS"
.FontStyle = "Regular"
.SIZE = 16
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(1, -1).Select
Set rs2 = CurrentDb.OpenRecordset("SELECT [Qry_Store
Purchase Order Pull Sheet].* " & _
"FROM [Qry_Store Purchase Order
Pull Sheet] " & _
"WHERE ((([Qry_Store Purchase
Order Pull Sheet].PurchaseOrder)='" & strPurchaseOrder & "'));",
dbOpenSnapshot)
If Not rs2.BOF Then
rs2.MoveFirst
Do While Not rs2.EOF
strHDDescription = rs2!HomeDepotDescription
strSize = rs2!SIZE
strSKU = rs2!SKU
strQuantity = rs2!ShippingQuantity
'Assign Store Purchase Order Details to Excel
ActiveCell.FormulaR1C1 = strHDDescription
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = strSize
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = strSKU
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = strQuantity
ActiveCell.Offset(1, -3).Select
rs2.MoveNext
Loop
rs2.Close
End If
rs1.MoveNext
Loop
rs1.Close
End If
End With
End With
Set rs1 = Nothing
Set rs2 = Nothing
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
 
I'm sorry, I haven't got the time at the moment to revise the whole
code,
but here's some comments on some of the implicit referencing, there
are lot of them in your code, I'm afraid, and they will need to be made
explicit.

I e - all usage of Excel objects (Range, Selection, ActiveCell,
ActiveWorkbook, Cell...) will need to be qualified through one of the
Excel
objects variables

For the small samples in the code, I'm not entirely sure I've got it
100%
which object to prefix with, I'm not with Access in front of me at the
moment,
but using intellisence, you should find which fit. If not, I or someone
else
will most likely come back and assist, should this not help enough,
just post back.

Paul wrote in message said:
Thanks for replying....here is my code

Private Sub BtnExcelStore_Click()
Dim loqd As QueryDef
Dim stSQL As String
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim stLinkCriteria As String
Dim stWhat As String
Dim stCriteria As String
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet
Dim strPurchaseOrder As String
Dim strStore As String
Dim strHDDescription As String
Dim strSize As String
Dim strSKU As String
Dim strQuantity As Long
On Error GoTo BtnExcelStore_Click_Err

stWhat = "": stCriteria = ","
Set frm = [Forms]![Frm_Pull Sheet]
Set ctl = frm!lstStorePO
For Each varItem In ctl.ItemsSelected
stWhat = stWhat & "'" & ctl.ItemData(varItem) & "'"
stWhat = stWhat & stCriteria
Next varItem
stLinkCriteria = CStr(Left$(stWhat, Len(stWhat) - Len(stCriteria)))

Set loqd = CurrentDb.QueryDefs("Qry_Store PO")
stSQL = "SELECT Orders.OrderID, Orders.PurchaseOrder, Orders.Status " & _
"FROM Orders INNER JOIN Order_Details ON Orders.OrderID =
Order_Details.OrderID " & _
"WHERE (((Orders.PurchaseOrder) IN (" & stLinkCriteria & ")) And
((Orders.Status) = 1) And ((Orders.StoreID)= " & [Forms]![Frm_Pull
Sheet]![cboStore] & ")) " & _
"ORDER BY Orders.PurchaseOrder;"
loqd.SQL = stSQL
loqd.Close

Set rs1 = CurrentDb.OpenRecordset("SELECT DISTINCT [Qry_Store Purchase
Order Pull Sheet].Store, [Qry_Store Purchase Order Pull Sheet].PurchaseOrder
" & _
"FROM [Qry_Store Purchase Order Pull
Sheet] " & _
"ORDER BY [Qry_Store Purchase Order
Pull Sheet].PurchaseOrder;", dbOpenSnapshot)

Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
With objSht
.Name = "Pull Sheet"
'Format WorkSheet and assign Store #, Name to Excel
.Columns("A:A").Select
Click to expand...

' With Selection
With objXL.Selection
.ColumnWidth = 31
End With
.Columns("B:D").Select
With Selection
.ColumnWidth = 10
End With
.Columns("E:E").Select
With Selection
.ColumnWidth = 25
End With
.Columns("F:G").Select
With Selection
.ColumnWidth = 5
End With
.Columns("A:G").Select
Click to expand...

' With Selection.Font
With Selection.Font
.Name = "Comic Sans MS"
.SIZE = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Click to expand...

' Range("A1").Select
' ActiveCell.FormulaR1C1 = "HOME DEPOT:"
' With ActiveCell.Characters(Start:=1, Length:=25).Font

objSheet.Range("A1").Select
' think perhaps also
objXL.Range("A1").Select ' is possible

objXL.ActiveCell.FormulaR1C1 = "HOME DEPOT:"
With objXL.ActiveCell.Characters(Start:=1,
Length:=25).Font
.Name = "Comic Sans MS"
.FontStyle = "Regular"
.SIZE = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("A2").Select
ActiveCell.FormulaR1C1 = "SHIP DATE:"
With Selection.Font
.Name = "Comic Sans MS"
.SIZE = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Range("B1:G1").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range("B2:G2").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A4:G5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A4").Select
ActiveCell.FormulaR1C1 = "PLANT DESCRIPTION"
Range("B4").Select
ActiveCell.FormulaR1C1 = "SIZE"
Range("C4").Select
ActiveCell.FormulaR1C1 = "SKU"
Range("D4").Select
ActiveCell.FormulaR1C1 = "QTY."
Range("E4").Select
ActiveCell.FormulaR1C1 = "LOCATION"
Range("F4").Select
ActiveCell.FormulaR1C1 = "TAGS"
Range("G4").Select
ActiveCell.FormulaR1C1 = "TAGS"
Range("F5").Select
ActiveCell.FormulaR1C1 = "PSL"
Range("G5").Select
ActiveCell.FormulaR1C1 = "PIC"
Range("A5:G5").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone

Range("B1").Select
ActiveCell.FormulaR1C1 = rs1!Store
With ActiveCell.Characters(Start:=1, Length:=25).Font
.Name = "Comic Sans MS"
.FontStyle = "Regular"
.SIZE = 16
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A6").Select
If Not rs1.BOF Then
rs1.MoveFirst
Do While Not rs1.EOF
strPurchaseOrder = rs1!PurchaseOrder
'Assign Purchase Order to Excel
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "PURCHASE ORDER:"
With ActiveCell.Characters(Start:=1, Length:=25).Font
.Name = "Comic Sans MS"
.FontStyle = "Regular"
.SIZE = 16
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

ActiveCell.Offset(0, 1).Select
Range(ActiveCell.Offset(0, 0).Address & ":" &
ActiveCell.Offset(0, 4).Address).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
ActiveCell.FormulaR1C1 = strPurchaseOrder
With ActiveCell.Characters(Start:=1, Length:=25).Font
.Name = "Comic Sans MS"
.FontStyle = "Regular"
.SIZE = 16
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(1, -1).Select
Set rs2 = CurrentDb.OpenRecordset("SELECT [Qry_Store Purchase
Order Pull Sheet].* " & _
"FROM [Qry_Store Purchase Order
Pull Sheet] " & _
"WHERE ((([Qry_Store Purchase Order
Pull Sheet].PurchaseOrder)='" & strPurchaseOrder & "'));", dbOpenSnapshot)
If Not rs2.BOF Then
rs2.MoveFirst
Do While Not rs2.EOF
strHDDescription = rs2!HomeDepotDescription
strSize = rs2!SIZE
strSKU = rs2!SKU
strQuantity = rs2!ShippingQuantity
'Assign Store Purchase Order Details to Excel
ActiveCell.FormulaR1C1 = strHDDescription
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = strSize
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = strSKU
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = strQuantity
ActiveCell.Offset(1, -3).Select
rs2.MoveNext
Loop
rs2.Close
End If
rs1.MoveNext
Loop
rs1.Close
End If
End With
End With
Set rs1 = Nothing
Set rs2 = Nothing
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Click to expand...
 
Back
Top