1

Topic: Error: <Method ' Cells ' of object ' _Global ' failed> at check of an amount of lines

Good afternoon!
There is a code in Access which opens an Excell-file and loads from it a line in the table.
Before loading there is a line of check of an amount of lines in Excel:

lLastRow = Cells. SpecialCells (xlLastCell).Row

If the code to launch the first time it fulfills successfully. Thus, in processes Windows there is a new process: EXCEL.EXE. For some reason after procedure end this process is not closed (though is xlt. Close (False)).
If at once again to launch the same procedure there is an error: &lt; Method ' Cells ' of object ' _Global ' failed &gt; at line:

lLastRow = Cells. SpecialCells (xlLastCell).Row

Thus, naturally, in processes there was the second EXCEL.EXE which was not closed because of an error.
But if not to delete these two processes EXCEL.EXE, and to restart client Access and then procedure all works correctly, procedure it is possible to launch some times successively, thus, after its working off the second process EXCEL.EXE opens successfully and closed, and the first simply hangs...
If not to restart client Access, and in the manager to complete all processes EXCEL.EXE and to launch procedure there will be other error: &lt; The remote server machine does not exist or is unavaliable &gt;.
And at last, if to restart the client and to delete ALL processes EXCEL.EXE again the first time fulfills correctly...
The request to help to understand, in what an error essence why under (in appearance) same conditions, the code that works is not present. I can is wrong I close Excel?
Thankful in advance!
Here procedure entirely:

Private Sub RisunokZagruzitFajl_Click ()
Dim Sht As String
Dim ln As String
Dim sg As String
Dim tm24 As String
Dim tm48 As String
Dim Line As String
Dim m_str () As String
Dim Shd_sur As String
Dim Shd_fir As String
Dim Shd_par As String
Dim time24 As Date
Dim time48 As Date
' ###############################################
DoCmd. SetWarnings False
DoCmd. RunSQL "delete from the arch" ' deleted all from the table "arch"
DoCmd. SetWarnings True
' #############################################
DoCmd. SetWarnings False
Dim file As String
' it is checked, whether there is a file in a directory:
Dim path, nameFile
path = "\\tb-fs05\Department-2\SSI\&#192;&#237;&#228;&#240;&#229;&#233; \&#211;&#208;&#194;&#202;&#202; \&#194;&#251;&#227;&#240;&#243;&#231;&#234;&#224;_CRM"
nameFile = "Sozdat_dokument.xlsx"
If Dir (path and "\" and nameFile) = "" Then ' if file is not present, we deduce the message and we quit procedure:
MsgBox "In a folder" and path and "there is no file" and filename"
Exit Sub
Else
file = "\\tb-fs05\Department-2\SSI\\\_.xlsx"
Set xl = CreateObject ("Excel. Application")
Set xlt XL. Workbooks. Open (file)
Dim llastrow As Long
Dim k As Long
llastrow = Cells. SpecialCells (xlLastCell).row ' found an amount of the filled lines in Excel
For k = 1 To llastrow
Shd = xlt. ActiveSheet. Cells (k + 1, 9).Value ' 
Sht = xlt. ActiveSheet. Cells (k + 1, 10).Value ' registration date
DrN = xlt. ActiveSheet. Cells (k + 1, 14).Value ' number
Car = xlt. ActiveSheet. Cells (k + 1, 18).Value ' a stage
sg = xlt. ActiveSheet. Cells (k + 1, 3).Value ' group
' we delete an apostrophe if it is found in 
Shd = Replace (Shd, "'", "")
' =================================================================
' calculation of an amount of words in a cell:
Dim iStr As String ' an initial line
Dim b As String ' a line without gaps
Dim x As String ' the current character in line
Dim i As Integer ' number of the current character
Dim j As Integer ' the counter of gaps
Dim y As Integer ' an amount of words
iStr = Shd
b = Trim (iStr)
j = 0
For i = 1 To Len (b)
x = Mid (b, i, 1)
If x = "" Then j = j + 1
Next i
y = j + 1 ' wrote down an amount of words in a variable
' ====================================================================
m_str () = Split (Shd, "")
If Shd &lt;&gt; "" Then ' &#239; a cell with  on value NULL
If y = 3 Then ' if full 
Shd_sur = m_str (0) ' selected a surname
Shd_fir = m_str (1) ' a name
Shd_par = m_str (2) ' a patronymic
Set rstLine = Nothing
' in the table users found the given employee:
Sqltext = "Select users.surname, users.first_name, users.patronymic, users.line_id" _
& "FROM users WHERE (CAST (surname as varchar) = '" AND Shd_sur AND "') And (CAST (first_name as varchar) = '" AND Shd_fir AND "') And (CAST (patronymic as varchar) = '" AND Shd_par AND "');"
rstLine. Open Sqltext, cn, adOpenKeyset, adLockOptimistic
ElseIf y = 2 Then ' if there is no patronymic:
Shd_sur = m_str (0) ' a surname
Shd_fir = m_str (1) ' a name
Shd_par = "" ' it is empty
Set rstLine = Nothing
Sqltext = "Select users.surname, users.first_name, users.patronymic, users.line_id" _
& "FROM users WHERE (CAST (surname as varchar) = '" AND Shd_sur AND "') And (CAST (first_name as varchar) = '" AND Shd_fir AND "');"
rstLine. Open Sqltext, cn, adOpenKeyset, adLockOptimistic
End If
End If
If rstLine. Whether RecordCount&gt; 0 Then the employee is checked, found in the table users
If (rstLine. Fields (3) = 26) Or (rstLine. Fields (3) = 27) Then ' it is checked . Conditions
If (sg = "Complaints") Or (sg = "Claims") Then
If Shd Like FIO Then
' ###################### further calculation of time intervals ################################
Sht = DateAdd ("h", 7, CDate (Sht))
Sht = DateAdd ("h",-24, CDate (Sht)) ' &#241;&#237;&#224;&#247;&#224;&#235;&#224; &#238;&#242;&#237;&#232;&#236;&#224;&#229;&#236; 24 &#247;&#224;&#241;&#224; &#247;&#242;&#238;&#225;&#251; &#226; &#246;&#232;&#234;&#235;&#229; &#239;&#240;&#232;&#225;&#224;&#226;&#232;&#242;&#252; &#232; &#241;&#237;&#238;&#226;&#224; &#226;&#251;&#233;&#242;&#232; &#237;&#224; &#226;&#240;&#229;&#236;&#255; &#240;&#229;&#227;&#232;&#241;&#242;&#240;&#224;&#246;&#232;&#232;
Do
Sht = DateAdd ("h", 24, CDate (Sht))
Set rstTimeReg = Nothing
Sqltext = "Select id, ddmmyy, output, holiday FROM Calendar WHERE ddmmyy = '" AND (Format (Sht, "yyyy-mm-dd")) AND "';"
rstTimeReg. Open Sqltext, cn, adOpenKeyset, adLockOptimistic
Loop While ((rstTimeReg. Fields (2) = 1) Or (rstTimeReg. Fields (3) = 1))
tm24 = Sht
Do
tm24 = DateAdd ("h", 24, CDate (tm24))
Set rstTime24 = Nothing
Sqltext = "Select id, ddmmyy, output, holiday FROM Calendar WHERE ddmmyy = '" AND (Format (tm24, "yyyy-mm-dd")) AND "';"
rstTime24.Open Sqltext, cn, adOpenKeyset, adLockOptimistic
Loop While ((rstTime24.Fields (2) = 1) Or (rstTime24.Fields (3) = 1))
tm48 = tm24
Do
tm48 = DateAdd ("h", 24, CDate (tm48))
Set rstTime48 = Nothing
Sqltext = "Select id, ddmmyy, output, holiday FROM Calendar WHERE ddmmyy = '" AND (Format (tm48, "yyyy-mm-dd")) AND "';"
rstTime48.Open Sqltext, cn, adOpenKeyset, adLockOptimistic
Loop While ((rstTime48.Fields (2) = 1) Or (rstTime48.Fields (3) = 1))
' #################################################################################
' if all checks transited also intervals calculated, we add a line in the table access:
DoCmd. RunSQL "INSERT INTO the arch (, Date, Number, the Stage, _24, _48) select '" and Shd and "', '" and Sht and "', '" and DrN and "', '" and Car and "', '" and tm24 and "', '" both tm48 and "'"
End If
End If
End If
End If
Next k ' passed to the following record
xlt. Close (False) ' closed Excel
End If
DoCmd. SetWarnings True
' it is updated form fields:
Sqltext = "SELECT the Code, , Date, Number, the Stage, _24, _48" _
& "FROM the arch order by CDate (_48) DESC"
Set rstaccess_local = Nothing
rstaccess_local. Open Sqltext, CurrentProject. Connection, adOpenKeyset, adLockOptimistic
Set Me. Form. Recordset = rstaccess_local
End Sub

2

Re: Error: <Method ' Cells ' of object ' _Global ' failed> at check of an amount of lines

nik.an wrote:

Before loading there is a line of check of an amount of lines in Excel:

lLastRow = Cells. SpecialCells (xlLastCell).Row

... And here if there were explicit instructions, in what book yes on what sheet to use Cells...

3

Re: Error: <Method ' Cells ' of object ' _Global ' failed> at check of an amount of lines

Replaced a line on:

lLastRow = Workbooks ("Sozdat_dokument.xlsx").Sheets (1).Cells. SpecialCells (xlLastCell).Row

If to launch in the very first time - it is successfully executed, and if at once the second time, an error (but truth another): Subscript out of range
If to restart the client (without deleting process Excel) - again works normally, besides successively some times it is possible to launch.

4

Re: Error: <Method ' Cells ' of object ' _Global ' failed> at check of an amount of lines

Akina wrote:

it is passed...
... And here if there were explicit instructions, in what book yes on what sheet to use Cells...

Thanks!
As a result your version appeared true!
Once again altered a line on:

lLastRow = xlt. ActiveSheet. Cells. SpecialCells (xlLastCell).Row

Began to work correctly.