Run Time 1004

[LB79]

Hello,

I'm having a problem with Run Time 1004 – Method worksheet of object global failed.
My VB opens an Excel template and copies data too it. It works every other time, debugging at ‘Set xlSheet = Worksheets("TestSheet")’ on the times it fails.
Can anyone see aht could be going wrong here?
This is a cut down piece of the code .

Thanks

[SIZE=3][COLOR=#000080][FONT=Arial]Public db As DAO.Database[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public RS As DAO.Recordset[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public xlApp As Object[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Public xlSheet As Object[/FONT][/COLOR][/SIZE]
 
 
[SIZE=3][COLOR=#000080][FONT=Arial]Private Sub REF_cmd1_Click()[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set db = CurrentDb[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set xlApp = CreateObject("Excel.Application")[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]xlApp.Visible = True[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]xlApp.Workbooks.Open "C:\Test.xlsm", True, False[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set xlSheet = Worksheets("TestSheet") 'Date Range[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set RS = CurrentDb.OpenRecordset("qry_QryTest1", dbOpenSnapshot)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]xlSheet.Range("A1").CopyFromRecordset RS[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set xlSheet = Worksheets("Data") [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set RS = CurrentDb.OpenRecordset("qry_ QryTest2 ", dbOpenSnapshot)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]xlSheet.Range("A2").CopyFromRecordset RS[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]RS.Close[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set RS = Nothing[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set db = Nothing[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set xlApp = Nothing[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set xlSheet = Nothing[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]End Sub[COLOR=#000000]

[/COLOR][/FONT][/COLOR][/SIZE]

 

[Trevor G]

Indicated below is a method I use using ADODB which you have to set in the VBA screen and use Tools and References then look down the list for ActiveX Data Objects (in my case) 2.6 Library and of course Excel

Function myInvestigation()
'***************************************************************************************
'VBA Code created by Trevor
'***************************************************************************************
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strPath As String
Dim ws As Excel.Application
Dim i As Long
'***************************************************************************************
'First stage is to take the first query and place it
'On sheet1 and rename sheet1 to "Combined" which is to
'identify that this has come from the Combined table in COD
'***************************************************************************************
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM qryInvestination17032010"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
rst.MoveFirst
Set ws = CreateObject("Excel.Application")
With ws
.Workbooks.Add
.Visible = True
End With
ws.Sheets("sheet1").Select
For i = 0 To rst.Fields.Count - 1
ws.ActiveCell.Offset(0, i).Value = rst.Fields(i).Name
Next
ws.Range("a2").CopyFromRecordset rst
ws.Columns("A:Q").EntireColumn.AutoFit
rst.Close
ws.Sheets("sheet1").Name = "Combined"
ws.ActiveSheet.PageSetup.LeftHeader = "&A & &D"
ws.Columns("E:H").NumberFormat = "0"
ws.Columns("E:H").EntireColumn.AutoFit
'***************************************************************************************
'Second stage is to take the second query and place it
'On sheet2 and rename sheet2 to "DBO" which is to
'identify that this has come from workflow but converting
'the MI Reference to 19 Characters which is done in the query
'I am also using a named range "DBO"
'***************************************************************************************
strSQL = "SELECT * FROM qryDBOGroup10022010New"
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
rst.MoveFirst
ws.Sheets("Sheet2").Select
For i = 0 To rst.Fields.Count - 1
ws.ActiveCell.Offset(0, i).Value = rst.Fields(i).Name
Next
ws.Range("a2").CopyFromRecordset rst
ws.Columns("A:h").EntireColumn.AutoFit
rst.Close
ws.Sheets("sheet2").Name = "DB0"
ws.ActiveSheet.PageSetup.LeftHeader = "&A & &D"
ws.Columns("B:B").NumberFormat = "0"
ws.Columns("B:B").EntireColumn.AutoFit
ws.Range("a2").Select
ws.Selection.CurrentRegion.Name = "dbo"
'***************************************************************************************

End Function

 

[LB79]

Thanks. I still have problems though and I think Ive tracked down what it is. Excel is closing but the process remains. Ive looked through loads of threads about this and I 'think' it may be because of non explicit references?
Ive tried the below code but clearly im not getting it right. Any suggestions on where its going wrong?

Private Sub REF_cmd1_Click()
Set DB = CurrentDb
Agent = [Forms]![x_test]![PMA_lst1]
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("C:\Templates\01. PMA Report (RMO).xlsm")
Set xlSheet = Worksheets("Data")
Set QDF = DB.QueryDefs("qry_REF_GenericExport")
PMASQL = "SELECT tbl_PMA_Monitor.FY, tbl_PMA_Monitor.FY_QTR, tbl_PMA_Monitor.RepMnth, tbl_PMA_Monitor.CstMnth, tbl_PMA_Monitor.[Agency Name], tbl_PMA_Monitor.[UN Code], tbl_PMA_Monitor.Agent, tbl_PMA_Monitor.SubAgent, tbl_PMA_Monitor.ZONE, tbl_PMA_Monitor.LINE, tbl_PMA_Monitor.VESSEL, tbl_PMA_Monitor.VOYAGE, tbl_PMA_Monitor.PORT, tbl_PMA_Monitor.SAILING_DATE, tbl_PMA_Monitor.Item, tbl_PMA_Monitor.ChargeType, tbl_PMA_Monitor.Currency, tbl_PMA_Monitor.Indicator, tbl_PMA_Monitor.[Current(NET)], tbl_PMA_Monitor.[PMA(NET)], tbl_PMA_Monitor.[Current(ABS)], tbl_PMA_Monitor.[PMA(ABS)], tbl_PMA_Monitor.[PMA+], tbl_PMA_Monitor.[PMA-], tbl_PMA_Monitor.[PMA-(ABS)]"
PMASQL = PMASQL & " FROM tbl_PMA_Monitor"
PMASQL = PMASQL & " WHERE (((tbl_PMA_Monitor.[UN Code])Like'" & Agent & "'));"
QDF.SQL = PMASQL
Set RS = CurrentDb.OpenRecordset("qry_REF_GenericExport", dbOpenSnapshot)
With xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset(RS)
RS.Close
xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit
Set RS = Nothing
Set xlApp = Nothing
Set xlSheet = Nothing
Set QDF = Nothing
Set DB = Nothing
End With
End Sub

 

[Trevor G]

xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit
Set RS = Nothing
Set xlApp = Nothing
Set xlSheet = Nothing
Set QDF = Nothing
Set DB = Nothing
End With
End Sub

comment out the close excel quit etc

xlApp.DisplayAlerts = False
'xlBook.Close
'xlApp.Quit
Set RS = Nothing
'Set xlApp = Nothing
'Set xlSheet = Nothing
Set QDF = Nothing
Set DB = Nothing
End With
End Sub

Note that the exclamation mark has been added at the beginning of each line highlighted in green. If this doesn' work for you then just add an extra with at the end to open the workbook again

with xlApp
.visible=true
.workbooks.open"Enter the path and name of the workbook.xls"
end with

 

[LB79]

Thanks - I still cant seem to Kill Excel though. It stays running as a process and then when I export again it debugs.

 

[Trevor G]

What about trying to run this to a new workbook rather than your macro template book.

Are you trying just to transfer data into the book and then close Excel down, or do you need to keep Excel open and the workbook?

 

[LB79]

The idea was that the data transferes to the xlsm, the xlsm macros run, which creates and saves a named xlsx workbook. The reason for this is that the data makes the file size quite big (for email). The xlsm macro refreshes pivots, changes them from pivots to normal tables, deletes the source data, then saves as an xlsx.

 

[SOS]

Your problem is this code:

Set xlSheet = Worksheets("Data")

it needs to be

Set xlSheet = xlBook.Worksheets("Data")

 

[LB79]

Thanks - doing that gives me Runtime 13 - Type Mismatch

Private Sub REF_cmd1_Click()
Set DB = CurrentDb
Agent = [Forms]![x_test]![PMA_lst1]
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("C:\Templates\01. PMA Report (RMO).xlsm")
Set xlSheet = xlBook.Worksheets("Data")
Set QDF = DB.QueryDefs("qry_REF_GenericExport")
PMASQL = "SELECT tbl_PMA_Monitor.FY, tbl_PMA_Monitor.FY_QTR, tbl_PMA_Monitor.RepMnth, tbl_PMA_Monitor.CstMnth, tbl_PMA_Monitor.[Agency Name], tbl_PMA_Monitor.[UN Code], tbl_PMA_Monitor.Agent, tbl_PMA_Monitor.SubAgent, tbl_PMA_Monitor.ZONE, tbl_PMA_Monitor.LINE, tbl_PMA_Monitor.VESSEL, tbl_PMA_Monitor.VOYAGE, tbl_PMA_Monitor.PORT, tbl_PMA_Monitor.SAILING_DATE, tbl_PMA_Monitor.Item, tbl_PMA_Monitor.ChargeType, tbl_PMA_Monitor.Currency, tbl_PMA_Monitor.Indicator, tbl_PMA_Monitor.[Current(NET)], tbl_PMA_Monitor.[PMA(NET)], tbl_PMA_Monitor.[Current(ABS)], tbl_PMA_Monitor.[PMA(ABS)], tbl_PMA_Monitor.[PMA+], tbl_PMA_Monitor.[PMA-], tbl_PMA_Monitor.[PMA-(ABS)]"
PMASQL = PMASQL & " FROM tbl_PMA_Monitor"
PMASQL = PMASQL & " WHERE (((tbl_PMA_Monitor.[UN Code])Like'" & Agent & "'));"
QDF.SQL = PMASQL
Set RS = CurrentDb.OpenRecordset("qry_REF_GenericExport", dbOpenSnapshot)
With xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset(RS)
RS.Close
xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit
Set RS = Nothing
Set xlApp = Nothing
Set xlSheet = Nothing
Set QDF = Nothing
Set DB = Nothing
End With
End Sub

 

[SOS]

On which line does it highlight for the error when you click DEBUG?

 

[LB79]

Sorry - that would have been helpful wouldnt it...

Its line:
With xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset(RS)

 

[LB79]

The gap in the Recordset isnt in the code. For some reason it appeared after i submitted that post.
With xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset(RS)

 

[SOS]

Get rid of the WITH part and the last parens.

xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset RS

 

[SOS]

The gap in the Recordset isnt in the code. For some reason it appeared after i submitted that post.
With xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset(RS)

Yeah, it is a problem the forum software has.

 

[LB79]

Ive tried what you just said and still debugs on the same line.

 

[namliam]

xlSheet is already assigned as being xlSheet = xlBook.Worksheets("Data")

So using it like so: xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset(RS)

Doesnt make sence.... instead....
xlSheet.Range("A2").CopyFromRecordset(RS)
or in effect
xlBook.Worksheets("Data").Range("A2").CopyFromRecordset(RS)

Would be more sensible.... I think.

Also remove the With and End With lines/parts, they serve no purpose

And I think you will want to do something other than:
xlBook.Close
xlApp.Quit

Your basicaly throwing away the excel book you just created not even saving it or nothing? At the very least you need to insert a "save as" or something simular.

 

[LB79]

Hi namliam,

Ive tried the
xlBook.Worksheets("Data").Range("A2").CopyFromRecordset (RS)
but it debugs with runtime 91 object variable or with block not set.
This is my test code and at the moment all I want it to do is open and close without leaving Excel running as a process. Once thats problems out the way I can add the other items back in.

 

[LB79]

Sorry - gave you the wrong error code there. I put that line in the wrong place.
This is what I currently have which gives me runtime 430 class does not support automation or doesn not support expected interface.

Private Sub REF_cmd1_Click()
Set DB = CurrentDb
Agent = [Forms]![x_test]![PMA_lst1]
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("P:\Liner Support Team\SAISAN_RECAP\PMA Monitor\Templates\01. PMA Report (RMO).xlsm")
Set xlSheet = xlBook.Worksheets("Data")
Set QDF = DB.QueryDefs("qry_REF_GenericExport")
PMASQL = "SELECT tbl_PMA_Monitor.FY, tbl_PMA_Monitor.FY_QTR, tbl_PMA_Monitor.RepMnth, tbl_PMA_Monitor.CstMnth, tbl_PMA_Monitor.[Agency Name], tbl_PMA_Monitor.[UN Code], tbl_PMA_Monitor.Agent, tbl_PMA_Monitor.SubAgent, tbl_PMA_Monitor.ZONE, tbl_PMA_Monitor.LINE, tbl_PMA_Monitor.VESSEL, tbl_PMA_Monitor.VOYAGE, tbl_PMA_Monitor.PORT, tbl_PMA_Monitor.SAILING_DATE, tbl_PMA_Monitor.Item, tbl_PMA_Monitor.ChargeType, tbl_PMA_Monitor.Currency, tbl_PMA_Monitor.Indicator, tbl_PMA_Monitor.[Current(NET)], tbl_PMA_Monitor.[PMA(NET)], tbl_PMA_Monitor.[Current(ABS)], tbl_PMA_Monitor.[PMA(ABS)], tbl_PMA_Monitor.[PMA+], tbl_PMA_Monitor.[PMA-], tbl_PMA_Monitor.[PMA-(ABS)]"
PMASQL = PMASQL & " FROM tbl_PMA_Monitor"
PMASQL = PMASQL & " WHERE (((tbl_PMA_Monitor.[UN Code])Like'" & Agent & "'));"
QDF.SQL = PMASQL
Set RS = CurrentDb.OpenRecordset("qry_REF_GenericExport", dbOpenSnapshot)
xlBook.Worksheets("Data").Range("A2").CopyFromRecordset (RS)
'xlBook.Worksheets(xlSheet).Range("A2").CopyFromRecordset RS
RS.Close
xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit
Set RS = Nothing
Set xlApp = Nothing
Set xlSheet = Nothing
Set QDF = Nothing
Set DB = Nothing
End Sub

 

[SOS]

Sorry - gave you the wrong error code there. I put that line in the wrong place.
This is what I currently have which gives me runtime 430 class does not support automation or doesn not support expected interface.

Does it highlight a line as the error?

 

[LB79]

Yes - it highlighhts
xlBook.Worksheets("Data").Range("A2").CopyFromRecordset (Rs)