Solved Method 'Cells' of object '_Global' failed in Excel session opened via Access (1 Viewer)

Ben_Entrew

Registered User.
Local time
Today, 14:54
Joined
Dec 3, 2013
Messages
177
Dear all,

from time to time this code doesn't run giving the error message:
Method 'Cells' of object '_Global' failed

It opens both Excel tables, but it fails to insert a column.

Every help is much appreciated.

Thank you.

Regards,
Ben

Code:
Public Sub Format_BD_Excel()

'Get all Excel files in proper format
Dim oExcel As Object
Dim oBook, oBook2 As Object
Dim lastcolumn_BD, lastrow_BD As Long
Dim lastcolumn_BD2, lastrow_BD2 As Long
Dim i, a As Integer
Dim sht, sht2 As Object
Dim rs As Recordset
Dim file2 As String
Dim s As String

'folderlocation_BD = "C:\Users\dveb8x\Desktop\888\BD List\"
'repmonth = "202108"


file2 = "C:\Users\dveb8x\Desktop\*.xlsx"
    
    
    Set oExcel = CreateObject("Excel.Application")
    oExcel.Application.DisplayAlerts = False
    oExcel.Visible = True
    
    Set oBook = oExcel.Workbooks.Open("" & folderlocation_BD & "BD_List_" & s & "_" & repmonth & ".xls")
    
    Set oBook2 = oExcel.Workbooks.Open("" & file2 & "")
      
    Set sht = oBook.Sheets(1)
    Set sht2 = oBook2.Sheets(1)
    
    With oBook2
    
    sht2.Activate
          
    sht2.UsedRange 'Refresh UsedRange
    
      
    oExcel.Columns("C:C").Select
    
        
    oExcel.Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
      
    End With
    
  With oBook
  sht.Range("A11").Select
  End with 
oExcel.Quit

End Sub
 

bastanu

AWF VIP
Local time
Today, 14:54
Joined
Apr 13, 2010
Messages
1,401
You need to change your declarations:

Dim sht, sht2 As Object

In the one above only sht2 is declared as object, the first is a variant (default).
Plus you are not using the With block properly, you need to precede each line with a dot (like in .Sheets(1)).
 

Ben_Entrew

Registered User.
Local time
Today, 14:54
Joined
Dec 3, 2013
Messages
177
Thank you for the feedback.
Unfortunately your proposed changes doesn't work out yet.
I found out that this explicit code makes issues:
It calculates the lastrow and lastcolum but breaks at the select statement:

Code:
lastrow_BD = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    lastcolumn_BD = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
 
  
    oExcel.Range(Cells(2, 3), Cells(" & lastrow_BD & ", " & lastcolumn_BD - 1 & ")).Select

I also tried with sht.Range(....)

Kind regards,
Ben
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:54
Joined
May 7, 2009
Messages
19,169
which workbook/sheet are you inserting and from which workbook/sheet?
 

Ben_Entrew

Registered User.
Local time
Today, 14:54
Joined
Dec 3, 2013
Messages
177
It opens both Excel files out of Access.
I'm using this:

Code:
Dim oExcel As Object
Dim oBook As Object
Dim oBook2 As Object
Dim lastcolumn_BD, lastrow_BD As Long
Dim lastcolumn_BD2, lastrow_BD2 As Long
Dim sht As Object
Dim sht2 As Object

 Set oExcel = CreateObject("Excel.Application")
  oExcel.Application.DisplayAlerts = False
  oExcel.Visible = True
  Set oBook = oExcel.Workbooks.Open("" & folderlocation_BD & "BD_Bericht_" & s & "_" & repmonth & ".xls")
  Set oBook2 = oExcel.Workbooks.Open("" & file2 & "")
  Set sht = oBook.Sheets(1)
  Set sht2 = oBook2.Sheets(1)
  sht.Activate
  sht.UsedRange 'Refresh UsedRange
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:54
Joined
May 7, 2009
Messages
19,169
this is ambiguous:

oExcel.Columns("C:C").Select

which workbokk/sheet?
 

Ben_Entrew

Registered User.
Local time
Today, 14:54
Joined
Dec 3, 2013
Messages
177
I replaced it by
sht2.Columns("C:C").Select
now this works.

However the select statement later on makes issues:

oExcel.Range(Cells(2, 3), Cells(" & lastrow_BD & ", " & lastcolumn_BD - 1 & ")).Select
or
sht.Range(Cells(2, 3), Cells(" & lastrow_BD & ", " & lastcolumn_BD - 1 & ")).Select

Regards,
Ben
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:54
Joined
May 7, 2009
Messages
19,169
oExcel is an Excel application, so it does not have Range as its object.
you use sht.Range.
 

Ben_Entrew

Registered User.
Local time
Today, 14:54
Joined
Dec 3, 2013
Messages
177
This also doesn't work :(
sht.Range(Cells(2, 3), Cells(" & lastrow_BD & ", " & lastcolumn_BD - 1 & ")).Select
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:54
Joined
May 7, 2009
Messages
19,169
sht.Range(sht.Cells(2, 3), sht.Cells(" & lastrow_BD & ", " & lastcolumn_BD - 1 & ")).Select
 

Ben_Entrew

Registered User.
Local time
Today, 14:54
Joined
Dec 3, 2013
Messages
177
Thank you, that was the missung point. You made my day :)

Regards,
Ben
 

Isaac

Lifelong Learner
Local time
Today, 14:54
Joined
Mar 14, 2017
Messages
8,738
Thank you for the feedback.
Unfortunately your proposed changes doesn't work out yet.
I found out that this explicit code makes issues:
It calculates the lastrow and lastcolum but breaks at the select statement:

Code:
lastrow_BD = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
    lastcolumn_BD = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

 
    oExcel.Range(Cells(2, 3), Cells(" & lastrow_BD & ", " & lastcolumn_BD - 1 & ")).Select

I also tried with sht.Range(....)

Kind regards,
Ben
Never use select or activate in Excel VBA.
 

Users who are viewing this thread

Top Bottom