Method 'Cells' of Object' _Global' failed ACCESS to EXCEL with VBA

[ChiomaJennifer]

hi...
i have an access application in which i need to open an existing excel sheet, find a date (already in the sheet) and populate a row (with the date cell column) with either 1 or 0

this means i have to convert the datecell.column to the alphabet equivalent before i can set a cell to be populated.

I used a function, which i have used in excel before (as seen below)

Function Col_Letter(lngcol) As String
Dim vArr
vArr = Split(Cells(1, lngcol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
 

when called, this runs fine on first run, gives an error (Method 'Cells' of Object' _Global' failed) the second run, runs fine the third run, and gives same error the fourth run...etc

i have tried more explicit methods of referring to the cell... seeing as i am working with excel from assess, but non has worked.
i tried

Function Col_Letter(lngcol) As String
Dim vArr
With Worksheets("August2013")
vArr = Split(Cells(1, lngcol).Address(True, False), "$")
Col_Letter = vArr(0)
End With
End Function
 

Function Col_Letter(lngcol) As String
Dim vArr
With Worksheets(1)
vArr = Split(Cells(1, lngcol).Address(True, False), "$")
Col_Letter = vArr(0)
End With
End Function  

Function Col_Letter(lngcol) As String
Dim vArr
With Sheets(1)
vArr = Split(Cells(1, lngcol).Address(True, False), "$")
Col_Letter = vArr(0)
End With
End Function
 

Function Col_Letter(lngcol) As String
Dim vArr
vArr = Split(Sheets(1).Cells(1, lngcol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
 

(these act in a similar way to the first issue)

Function Col_Letter(lngcol) As String
Dim vArr
Dim app As New Excel.Application
app.Visible = True
Dim Book As Excel.Workbook
Set Book = app.Workbooks.Add("D:\...\timesheeteng.xlsm")
With Book.Worksheets("August2013")
vArr = Split(Cells(1, lngcol).Address(True, False), "$")
Col_Letter = vArr(0)
End With
End Function
 

(this returns an object required error)

Any idea what i doing wrong?

[MikeTheBike]

Hi

I cannot simulate you error, as this works

Function Col_Letter(lngcol) As String
    Dim vArr() As String
    vArr = Split(Cells(1, lngcol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function
 
Sub test()
    Dim i As Integer
    For i = 25 To 30
        MsgBox Col_Letter(i)
    Next i
End Sub

However, I would like to know why you need to find out what the column letter is, as you do not need to know this to write info to that coulmn when you have the index already??


MTB

[zmbd]

Set Book = app.Workbooks.Add("D:\...\timesheeteng.xlsm") 

Is malformed.
"\...\" should be the full path such as
"D:\username\us ersubfolder1\us ersubfolder2\ti mesheeteng.xlsm "

app.Workbooks.Add

Normally, I would not use this method, change "add" to "open"

Also You should look at the following insights article on applicaition automation.


In fact I borrowed the code from there and another source for the following to transfer a query I use from time to time, you should be able to modify this to your application.

You can of course modify this for latebinding.

Sub zj_excel_query2sheet_2()
'
'You must set a reference to the EXCEL Library for the following to work
'
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim acRng As Variant
    Dim xlRow As Integer
    Dim qry As QueryDef
    Dim rst As Recordset
    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Open("C:\Documents and Settings\All Users\Workbook1.xlsx")
    Set xlWS = xlWB.Worksheets("BBData")
 
    'first empty cell in the worksheet.
    ' or hardcode to example xlRow = 3 to always startin the third row...
    xlRow = (xlWS.Columns("A").End(xlDown).row)
 
    Set qry = CurrentDb.QueryDefs("QueryA")
    Set rst = qry.OpenRecordset
 
    Dim c As Integer
    'column 1 = a
    c = 1
    xlRow = xlRow + 1
 
    Do Until rst.EOF
        For Each acRng In rst.Fields
            'starting in the first empty cell in the column indicated....
            'index with the loop.
            xlWS.Cells(xlRow, c).Formula = acRng
            c = c + 1
        Next acRng
 
        xlRow = xlRow + 1
        c = 1
        rst.MoveNext
        If xlRow > 25 Then GoTo rq_Exit
    Loop
 
rq_Exit:
    rst.Close
    Set rst = Nothing
    Set xlWS = Nothing
    xlWB.Close acSaveYes
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function
End Sub

[NeoPa]

The code is good, but it suffers from being run from Access. See Application Automation for how and why this happens. It's basically down to the fact that each application has a set of defaults. Thus, Cells in Excel refers automatically to Excel.Applicati on.ActiveSheet. Cells. When referring from foreign applications this must be stated explicitly.

Your last posted attempt was nearer, but assuming the code up to line #7 works as expected you still have a problem in that you have used With ... on line #7 but when you reference Cells on line #8 you do so without the ".". It should say .Cells(... instead.

[ADezii]

Why not use the Range Object instead? The following Code will:

1. Open an Excel Spreadsheet via Automation.
2. Loop through every Cell in a specified Range.
3. Check the Cell Value for a valid Date.
4. If the Date is valid, print the Cell's Address along with the Column designator.
   

   Dim appExcel As New Excel.Application
   Dim wb As Excel.Workbook
   Dim ws As Excel.Worksheet
   Dim rng1 As Excel.Range
   Dim rng2 As Excel.Range
    
   appExcel.Visible = False
    
   Set wb = appExcel.Workbooks.Open("C:\Security\Test2.xlsm")
   Set ws = wb.Worksheets("Sheet1")
    
   ws.Activate
    
   Set rng1 = ws.Range("A1:CC100")
    
   For Each rng2 In rng1
     If IsDate(rng2.Value) Then
       Debug.Print rng2.Address, Split(rng2.Address, "$")(1)
     End If
   Next
    
   appExcel.Quit
    

5. OUTPUT
   

   $D$1          D
   $D$2          D
   $D$3          D
   $CA$7         CA
   $B$12         B
   $AG$12        AG
   $AW$14        AW
   $H$24         H
   $K$28         K
   $U$44         U
   $E$48         E
   $AB$56        AB
   $BC$75        BC
   $O$78         O
   $L$90         L
   $T$90         T
   $AB$94        AB
   $X$100        X
    

6. The rest should be easy to figure out.