Solved Method 'Cells' of object '_Global' failed in Excel session opened via Access

[Ben_Entrew]

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

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]

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]

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:

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

 

[arnelgp]

which workbook/sheet are you inserting and from which workbook/sheet?

 

[Ben_Entrew]

It opens both Excel files out of Access.
I'm using this:

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]

this is ambiguous:

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

which workbokk/sheet?

 

[Ben_Entrew]

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]

oExcel is an Excel application, so it does not have Range as its object.
you use sht.Range.

 

[Ben_Entrew]

This also doesn't work
sht.Range(Cells(2, 3), Cells(" & lastrow_BD & ", " & lastcolumn_BD - 1 & ")).Select

 

[arnelgp]

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

 

[Ben_Entrew]

Thank you, that was the missung point. You made my day

Regards,
Ben

 

[Isaac]

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:

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.