method 'Range' of Object '_global' failed 1

[quicklearner]

i am new to vb and i am writing a small code to get records from access database based on a selection criteria and then create an excel sheet using that data.

the sequence is as follows:
-Starting the VB application goes to main selection screen
-Select a particular record and create excel sheet.
-creates an excel sheet , saves it and control comes back to main selection screen.
now if i select another record and try to create an excel sheet i get an error " error 1004 method 'Range' of object '_global' failed'

but if i go out of the vb application and select another recoed i do not get this error . can some tell me how to code so as not ot get the error.
thank you in advance

 

[milesada]

I know this post is a little old, but I'm experiencing the exact same thing... did you ever get a resolution?

a.

 

[Guest_imported]

i have the same problem: error 1004. Nothing in help. Also no info in MSDN web site. Can anyone help?

 

[mutidjo]

i am getting this problem as well... has anyone found a solution? thanks.

 

[MarkSweetland]

Usually the Excel object has gone out of scope and the workbook/sheet doesn't exist anymore. Or it could be that there is no longer a selected Range after another method has completed and the code is assuming the Range still exists.

Post some code and we'll look at it...




Mark

 

[mutidjo]

I got this code from the web:

Private Sub Command0_Click()
ADOImportFromAccessTable "C:\WINNT\Profiles\emutidjo\desktop\db2.mdb", "AWARD", Range("C1&quot
End Sub

Sub ADOImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", "TableName", Range("C1&quot
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
' open the recordset
.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' all records
'.Open "SELECT * FROM " & TableName & " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText ' filter records

' RS2WS rs, TargetRange ' write data from the recordset to the worksheet

' ' optional approach for Excel 2000 or later (RS2WS is not necessary)
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Thanks for your help!

 

[MarkSweetland]

Try this. Also make sure you have the Microsoft Excel Object Library selected in your project references (as well as ADO).

Option Explicit
Dim xlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Private Sub Command0_Click()
Dim strTargetRange As String
Dim strOutputpath As String
Dim strOutputFile As String
Dim strSourceDB As String
Dim strSourceTable As String

strSourceDB = "C:\WINNT\Profiles\emutidjo\desktop\db2.mdb"
strSourceTable = "Award"
strTargetRange = "B3"
strOutputpath = "C:\WINNT\Profiles\emutidjo\desktop\"
strOutputFile = "AwardTable2Excel"
ADOImportFromAccessTable strSourceDB, strSourceTable, strTargetRange, strOutputpath, strOutputFile

' ADOImportFromAccessTable "C:\WINNT\Profiles\emutidjo\desktop\db2.mdb", "AWARD", strTargetRange

End Sub

Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, _
strTargetRange As String, _
strOutputpath As String, _
strOutputFilename As String)

Dim ExcelWasNotRunning As Boolean
Dim TargetRange As Excel.Range

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set xlApp = GetObject(, "Excel.Application&quot
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo err_handler ' capture any new errors
If ExcelWasNotRunning Then
Set xlApp = CreateObject(&quot;Excel.application&quot
End If

' Set the next line to false if you don't want to watch
xlApp.Visible = True

If xlApp.Workbooks.Count > 0 Then
'if there are any workbooks, select the first one.
Set xlwb = xlApp.Workbooks(1)
xlwb.Select
Else
' otherwise create a new wb
Set xlwb = xlApp.Workbooks.Add
End If

If xlwb.Sheets.Count > 0 Then
' iff there are any sheets in the wb, select first one
Set xlSheet = xlwb.Sheets(1)
xlSheet.Select
Else
' otherwise create a worksheet
Set xlSheet = xlwb.Worksheets.Add
xlSheet.Name = &quot;Sheet1&quot;
End If

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer

' Set the Range object
Set TargetRange = xlSheet.Range(strTargetRange)
TargetRange.Select

' open the database
Set cn = New ADODB.Connection
cn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & DBFullName & &quot;;&quot;
Set rs = New ADODB.Recordset
With rs
' open the recordset
.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' all records
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data
End With
err_exit:
If rs.State = adStateOpen Then rs.Close
Set rs = Nothing
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
'if you want to save the imported file, just uncomment the next line
'xlwb.SaveAs strOutputpath & strOutputFilename
'close the work book, uncomment out next 2 if you don't want to leave Excel open
' don't forget to set xlApp.visible to true if to be left open.
'xlwb.Close False
'xlApp.Quit
Exit Sub
err_handler:
Resume err_exit
End Sub





Mark

 

[tripleaaa]

Hi MarkSweetland

I was looking at your code and was wondering what this section actually does :

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set xlApp = GetObject(, &quot;Excel.Application&quot
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo err_handler ' capture any new errors
If ExcelWasNotRunning Then
Set xlApp = CreateObject(&quot;Excel.application&quot
End If

Does it only check if the process is running and if it it running do not execute &quot;Set xlApp=CreateObject(...) again?

Is there anyway to kill the excel.exe process completely so that i can create multiple files with excel.

Thanks again!

 

[MarkSweetland]

GetObject(pathname,[class]) returns a new object instance of the specified type. If the pathname argument is omitted or is a zero-length string(&quot;&quot, GetObject returns a [red]currently active object [/red] of the specified type. If no object of the specified type exists, an error occurs.

The error condition is handled with the &quot;On Error Resume Next&quot; statement if an error does occur from the GetObject() call. The following statement actually checks the Error Object to see if an error did occur (Err.Number >0). If an error did occur, sets the boolean flag to signal later to call the CreateObject(class, [servername]) and actually create a new instance of the class (Excel) and continue.

If you want to create multiple files with Excel, you only need one instance of the Excel Application to manipulate the Excel Workbook/Worksheets. You can open, save, and close these workbook files as needed. If you need multiple instances of Excel Applications, such as the need to open multiple workbooks at one time, you'll use the CreateObjects to create as many Excel.Applications as you need.


You can kill the Excel process(es) through API calls on Win9x machines, but I've not tried it on WinNT/2K/XP yet. But I've noticed that if you declare the Excel.Application variable as global or at the module level, the Excel app tends to remain in memory until the VB app is closed; even if you've called the app.close and released the variable ( set xlapp = nothing).





Mark