Поиск содержимого поля формы Access в ячейках Excel (части или полного)

@intersk · Регистрация: 07.09.2014

Студворк — интернет-сервис помощи студентам

нужно осуществить поиск значения полей из Access в Excel (сначала туда вношу, потом надо найти сроку...)
использую код (покажу часть). Сам код рабочий,но ищет только если указать в поле формы одно слово или их сочетание, точно соответствующее тем словам что есть в ячейке.
В остальных случаях не работает...Почему?
Может дело в типах данных?

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
Dim Poisk1
Dim Poisk2
 
 
Poisk1 = Forms!Данные!Номер_платежки
Poisk2 = Forms!Данные!Сумма_долга + Forms!Данные!Сумма_процентов
 
.....
    Set c = .Find(What:=Poisk1, LookIn:=xlValues, LookAt:=xlPart)
.....
                 If Not xlWbkEx.Worksheets(L).Rows(c.Row).Find(What:=Poisk2, LookIn:=xlValues, LookAt:=xlPart) Is Nothing Then

mobile · 19.03.2015, 15:33

Прежде всего проверил бы Poisk2 (Debug.Print Poisk2). Та ли там величина, которую Вы ищете? Например, если акс посчитает, что в полях Сумма_долга и Сумма_процентов строки, а не числа, то вместо ожидаемого, к примеру 100+50 равного 150 будет 10050. Или в полях есть длинная мантисса, которая на форме скрывается форматом. Или одно из полей NULL. Вообщем, эту величину надо проверять. И только потом можно двигаться дальше, если поиск не идет по иной причине.

Да и вопрос стоит задавать на форуме ВБА или Excel.

@intersk · 19.03.2015, 23:01 **[ТС]**

Сообщение от mobile

Прежде всего проверил бы Poisk2 (Debug.Print Poisk2).

складывает числа

Сообщение от mobile

Или в полях есть длинная мантисса, которая на форме скрывается форматом

формат полей с разделителями
всё же из access ищу....

mobile · 19.03.2015, 23:21

intersk, попробуйте поискать только целую часть. Если поиск состоится, то можно искать причины

Visual Basic
1
Poisk2 = Int(Forms!Данные!Сумма_долга + Forms!Данные!Сумма_процентов)

Если же опять ничего, то есть предположение, что числа в екселе на самом деле строки. И тогда надо искать с пробелами.

@intersk · 19.03.2015, 23:37 **[ТС]**

Сообщение от mobile

попробуйте поискать только целую часть

не найдено, формат вроде не строковый

mobile · 20.03.2015, 00:52

Попробуем иначе. Ниже процедура с 4 параметрами, которую надо выполнять в Access. Можно обращение к процедуре записать сразу после формирования параметров Poisk1 и Poisk2:
- путь к Excel-файлу с его именем
- имя листа
- значение первого параметра поиска
- значение второго параметра поиска
Если у Вас файл *.XLS, то замените в 4-й строке Excel 12.0 на Excel 8.0

Найденное, если будет такое, запишется в Immediate (область отладки в редакторе ВБА). Если пройдет, подскажу, что делать дальше

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
Sub FindInExcel(Path, lst, par1, par2)
    Dim i, rst As Object, s, p
    Set rst = CreateObject("ADODB.recordset")
    s = "select * from [" & lst & "$] in '" & Path & "'[Excel 12.0;hdr=No;]"
    rst.Open s, CurrentProject.Connection
    p = Split(rst.GetString(, , "|", vbCrLf), vbCrLf)
    For i = 0 To UBound(p)
        If p(i) Like "*" & par1 & "*" And p(i) Like "*" & par2 & "*" Then
            Debug.Print i, p(i)
        End If
    Next
End Sub

Пример обращения к процедуре

Visual Basic
1
FindInExcel "C:\Temp\МойФайл.xlsx", "Лист1", Poisk1, Poisk2

@intersk · 20.03.2015, 09:30 **[ТС]**

mobile, ваш код сработал верно!
Просьба адаптировать его к моему коду, потому что у меня поиск был организован по другому и шел намного дольше!

Мой код полностью (его смысл чтобы заполнить ComboBox, а потом выбрав нужную строку (по номеру) добавить в эту строку с этим номером, нужные значения в ячейки этой строки)

Ваш код который работает

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Option Compare Database
Option Explicit
 
Sub FindInExcel(Path, lst, par1, par2)
    Dim i, rst As Object, s, p
    Set rst = CreateObject("ADODB.recordset")
    s = "select * from [" & lst & "$] in '" & Path & "'[Excel 12.0;hdr=No;]"
    rst.Open s, CurrentProject.Connection
    p = Split(rst.GetString(, , "|", vbCrLf), vbCrLf)
    For i = 0 To UBound(p)
        If p(i) Like "*" & par1 & "*" And p(i) Like "*" & par2 & "*" Then
            Debug.Print i, p(i)
        End If
    Next
End Sub
 
 
Sub ПоискСтрок()
 
Dim Poisk1
Dim Poisk2
 
Poisk1 = Forms!Данные!Номер_платежки
Poisk2 = Forms!Данные!Сумма_долга + Forms!Данные!Сумма_процентов
 
Call FindInExcel("c:\iSKi_X\ДЕЛА 2015 ОПОСД.xlsx", "Сводная таблица_2015г", Poisk1, Poisk2)
 
End Sub

мой код

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Option Compare Database
Option Explicit
 
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function LookupAccountName Lib "advapi32.dll" Alias "LookupAccountNameA" (lpSystemName As String, ByVal lpAccountName As String, sid As Any, cbSid As Long, ByVal ReferencedDomainName As String, cbReferencedDomainName As Long, peUse As Long) As Long
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private CompName As String 'объявляем переменную доступную для всего проекта
 
Dim Workbooks As Object
Dim xlWbkEx As Object, xlAppEx As Object
 
Const MyFile = "c:\iSKi_X\ДЕЛА 2015 ОПОСД.xlsx" ' "P:\Судебные дела\СУДЕБНЫЕ ДЕЛА 2015 ОПОСД.xlsx"
Const L = "Сводная таблица_2015г" 'l = "Лист1" 'можно просто цифрой номер листа указывать
 
Private Sub кнПоискСтроки_Click()
 
Dim spisok, Poisk1, Poisk2
Dim c, firstAddress
 
Poisk1 = Forms!Данные!Номер_платежки
Poisk2 = Forms!Данные!Сумма_долга + Forms!Данные!Сумма_процентов
 
Me.плСписСтрокЯчеек.ColumnCount = 4
Me.плСписСтрокЯчеек.RowSourceType = "Value List"
 
Set xlAppEx = CreateObject("Excel.Application.15") 'создаем объект Excel, чтобы можно было работать с его методами и свойствами
Set xlWbkEx = xlAppEx.Workbooks.Open(MyFile) ' запускаем процесс excel и открываем файл
 
With xlWbkEx.Worksheets(L).UsedRange.Cells
    Set c = .Find(What:=Poisk1, LookIn:=xlValues, LookAt:=xlPart)
 
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
                 
                 If Not xlWbkEx.Worksheets(L).Rows(c.Row).Find(What:=Poisk2, LookIn:=xlValues, LookAt:=xlPart) Is Nothing Then
                      spisok = c.Row & ";" & xlWbkEx.Worksheets(L).Range("F" & c.Row) & ";" & xlWbkEx.Worksheets(L).Range("G" & c.Row) & ";" & xlWbkEx.Worksheets(L).Range("J" & c.Row)
                      Me.плСписСтрокЯчеек.AddItem Item:=spisok
                 End If
            
            Set c = .Find(What:=Poisk1, After:=c, LookIn:=xlValues, LookAt:=xlPart)
        
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
 
'xlAppEx.Visible = True 'запускаем приложение Excel, можно сдвинуть вниз
'xlAppEx.ScreenUpdating = False    ' отключаем обновление экрана
 
xlWbkEx.Close True
Set xlAppEx = Nothing
Set xlWbkEx = Nothing
 
Call StatusBarNo
 
Me.НадпПоискЗавершен.Visible = True
 
End Sub

в прикрепленном файле этот код для удобства редактирования в текстовом файле

@intersk · 20.03.2015, 14:02 **[ТС]**

mobile, дополнение предыдущего сообщения - сам попытался адаптировать свой код. Ошибка - не происходит заполнение списка в строке

Visual Basic
1
spisok = i.Row & ";" & xlWbkEx.Worksheets(L).Range("F" & i.Row) & ";" &......

То есть не определяется номер строки. Я посчитал что переменная i это и есть номер строки

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
Option Compare Database
Option Explicit
 
 
Dim Workbooks As Object
Dim xlWbkEx As Object, xlAppEx As Object
 
Const MyFile = "c:\iSKi_X\ДЕЛА 2015 ОПОСД.xlsx" 'Const MyFile = "P:\Судебные дела\СУДЕБНЫЕ ДЕЛА 2015 ОПОСД.xlsx"
Const L = "Сводная таблица_2015г" 'l = "Лист1" 'можно просто цифрой номер листа указывать
 
Sub FindInExcel(Path, lst, par1, par2)
    
Dim spisok
 
Forms!Список!плСписСтрокЯчеек.ColumnCount = 4
Forms!Список!плСписСтрокЯчеек.RowSourceType = "Value List"
 
Set xlAppEx = CreateObject("Excel.Application.15") 'создаем объект Excel, чтобы можно было работать с его методами и свойствами
Set xlWbkEx = xlAppEx.Workbooks.Open(MyFile) ' запускаем процесс excel и открываем файл
 
 
Dim i, rst As Object, s, p
    Set rst = CreateObject("ADODB.recordset")
    s = "select * from [" & lst & "$] in '" & Path & "'[Excel 12.0;hdr=No;]"
    rst.Open s, CurrentProject.Connection
    p = Split(rst.GetString(, , "|", vbCrLf), vbCrLf)
    
    For i = 0 To UBound(p)
        If p(i) Like "*" & par1 & "*" And p(i) Like "*" & par2 & "*" Then
 
                spisok = i.Row & ";" & xlWbkEx.Worksheets(L).Range("F" & i.Row) & ";" & xlWbkEx.Worksheets(L).Range("G" & i.Row) & ";" & xlWbkEx.Worksheets(L).Range("J" & i.Row)
                
                Forms!Список!плСписСтрокЯчеек.AddItem Item:=spisok
 
            Debug.Print i, p(i)
            Debug.Print i
        
        End If
 
    Next
 
xlWbkEx.Close True
Set xlAppEx = Nothing
Set xlWbkEx = Nothing
 
End Sub
 
Sub ПоискСтрок()
 
Dim Poisk1
Dim Poisk2
 
Poisk1 = Forms!Данные!Номер_платежки
Poisk2 = Forms!Данные!Сумма_долга + Forms!Данные!Сумма_процентов
 
Call FindInExcel("c:\iSKi_X\ДЕЛА 2015 ОПОСД.xlsx", "Сводная таблица_2015г", Poisk1, Poisk2)
 
End Sub

Добавлено через 2 часа 9 минут
mobile, извините что забросал сообщениями - немного сам разобрался и код теперь делает то, что мне нужно.
Можно ли его ещё как-то оптимизировать?

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
Option Compare Database
Option Explicit
 
Dim Workbooks As Object
Dim xlWbkEx As Object, xlAppEx As Object
 
Const MyFile = "c:\iSKi_X\ДЕЛА 2015 ОПОСД.xlsx" 'Const MyFile = "P:\Судебные дела\СУДЕБНЫЕ ДЕЛА 2015 ОПОСД.xlsx"
Const L = "Сводная таблица_2015г" 'l = "Лист1" 'можно просто цифрой номер листа указывать
 
Sub ПоискСтрок()
 
Dim Poisk1, Poisk2
 
Poisk1 = Forms!Данные!Номер_платежки
Poisk2 = Forms!Данные!Сумма_долга + Forms!Данные!Сумма_процентов
 
Call FindInExcel("c:\iSKi_X\ДЕЛА 2015 ОПОСД.xlsx", "Сводная таблица_2015г", Poisk1, Poisk2)
 
End Sub
 
Sub FindInExcel(Path, lst, par1, par2)
    
Dim i, rst As Object, s, p
Dim spisok As String
Dim irow As Integer
    
Forms!Список!плСписСтрокЯчеек.ColumnCount = 4
Forms!Список!плСписСтрокЯчеек.RowSourceType = "Value List"
    
Set xlAppEx = CreateObject("Excel.Application.15") 'создаем объект Excel, чтобы можно было работать с его методами и свойствами
Set xlWbkEx = xlAppEx.Workbooks.Open(MyFile) ' запускаем процесс excel и открываем файл
    
    
    Set rst = CreateObject("ADODB.recordset")
    s = "select * from [" & lst & "$] in '" & Path & "'[Excel 12.0;hdr=No;]"
    rst.Open s, CurrentProject.Connection
    p = Split(rst.GetString(, , "|", vbCrLf), vbCrLf)
    
    For i = 0 To UBound(p)
        If p(i) Like "*" & par1 & "*" And p(i) Like "*" & par2 & "*" Then
            
            irow = i + 1
            spisok = irow & ";" & xlWbkEx.Worksheets(L).Range("F" & irow) & ";" & xlWbkEx.Worksheets(L).Range("G" & irow) & ";" & xlWbkEx.Worksheets(L).Range("J" & irow)
            Forms!Список!плСписСтрокЯчеек.AddItem Item:=spisok
 
            'Debug.Print irow, i, p(i)
        
        End If
    Next
 
xlWbkEx.Close True
Set xlAppEx = Nothing
Set xlWbkEx = Nothing
 
End Sub

mobile · 20.03.2015, 14:47

Не нужно вообще открывать ексель. Вся необходимая инфа уже есть в запросе

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub FindInExcel(Path, lst, par1, par2) 'Поиск по нескольким параметрам в екселе
    Dim i, rst As Object, s, p, r
    Dim nRow
    Set rst = CreateObject("ADODB.recordset")
    s = "select * from [" & lst & "$] in '" & Path & "'[Excel 12.0;IMEX=1;hdr=no;]"
    rst.Open s, CurrentProject.Connection, 1 'adOpenKeyset
    p = Split(rst.GetString(, , "|", vbCrLf), vbCrLf)
    Debug.Print rst.RecordCount
    For i = 0 To UBound(p)
        If p(i) Like "*" & par1 & "*" And p(i) Like "*" & par2 & "*" Then
            nRow = i + 1
            r = Split(p(i), "|")
            Forms!Список!плСписСтрокЯчеек.AddItem Item:=nRow & ";" & r(5) & ";" & r(6) ";" & r(9)
            Exit For
        End If
    Next
End Sub

@intersk · 20.03.2015, 15:53 **[ТС]**

mobile, файл Excel, с которым работает код это сетевой файл для работы нескольких пользователей. Поэтому мне надо как-то заблокировать файл от изменений на момент поиска, на момент работы кода.
Моя идея - открыть файл, тогда другим пользователям он будет недоступен для изменения (установлены ограничение на одновременное редактирование).
Как-то ещё можно пометить файл на запрет изменения, на блокировку?
вы умеете работать с таблицами напрямую, это круто! я пока этого не умею.

Добавлено через 7 минут
При тестировании новый код нашел только одну строку (это неправильно). Мой код с открытием Excel ищет две строки (что правильно). Где-то опечатка в коде или цикл прерывается....

Добавлено через 7 минут
обнаружил Exit For , он останавливал поиск на первом значении

Добавлено через 8 минут
ищет и выводит только по тестовой (упрощенной) записи, по реальной ищет (это видно по отладочному окну - там выводиться количество строк столько раз, сколько найдено строк, у вас есть Debug.print в коде), но вот список почему то не заполняется. Вроде и кавычки не причем...

mobile · 20.03.2015, 19:22

Исправлю некоторые ошибки и дополню программу

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Sub FindInExcel(Path, lst, par1, par2, CleanSource as Boolean) 'Поиск по нескольким параметрам в екселе
'CleanSource - параметр заполнения поля со списком. 
'Если CleanSource=True, то вновь найденные строки добавляются к имеющимся. 
'Если CleanSource=False, то источник списка очищается перед заполнением
    Dim i, rst As Object, s, p, r, rs, frs
    Dim nRow
    Set rst = CreateObject("ADODB.recordset")
    s = "select * from [" & lst & "$] in '" & Path & "'[Excel 12.0;IMEX=1;hdr=no;]"
    rst.Open s, CurrentProject.Connection, 1 'adOpenKeyset
    p = Split(rst.GetString(, , "|", vbCrLf), vbCrLf)
'    Debug.Print rst.RecordCount
    If CleanSource Then 
        frs=Forms!Список!плСписСтрокЯчеек.RowSource & ";"
    Else
        frs=""
    End If
    For i = 0 To UBound(p)
        If p(i) Like "*" & par1 & "*" And p(i) Like "*" & par2 & "*" Then
            nRow = i + 1
            r = Split(p(i), "|")
            rs=nRow & ";" & r(5) & ";" & r(6) & ";" & r(9)
            frs=frs & rs & ";"
        End If
    Next
    Forms!Список!плСписСтрокЯчеек.RowSource=frs
End Sub

@intersk · 20.03.2015, 23:22 **[ТС]**

mobile,

Сообщение от mobile

Исправлю некоторые ошибки и дополню программу

тестирую... добавил в Call FindInExcel(........, False) всё находит!
понял почему не шел поиск в некоторых записях - у меня в поле "платежное_поручение" либо 3 или 4 цифры. 4 цифры ищет, а 3 не ищет. Потому что указана маска (надо чтобы просто было введено не больше 4 цифр, а проверку "нажатие клавиш" только цифры не больше 4 не знаю как сделать, не проверку, а не дать внести 5 цифру....). Появляющаяся звездочка мешает.

Новые блоги и статьи Все статьи Все блоги /
Конвертировать закладки radiotray-ng в m3u-плейлист damix 19.02.2026 Это можно сделать скриптом для PowerShell. Использование . \СonvertRadiotrayToM3U. ps1 <path_to_bookmarks. json> Рядом с файлом bookmarks. json появится файл bookmarks. m3u с результатом. # Check if. . .	Семь CDC на одном интерфейсе: 5 U[S]ARTов, 1 CAN и 1 SSI Eddy_Em 18.02.2026 Постепенно допиливаю свою "многоинтерфейсную плату". Выглядит вот так: https:/ / www. cyberforum. ru/ blog_attachment. php?attachmentid=11617&stc=1&d=1771445347 Основана на STM32F303RBT6. На борту пять. . .	Камера Toupcam IUA500KMA Eddy_Em 12.02.2026 Т. к. у всяких "хикроботов" слишком уж мелкий пиксель, для подсмотра в ESPriF они вообще плохо годятся: уже 14 величину можно рассмотреть еле-еле лишь на экспозициях под 3 секунды (а то и больше),. . .	И ясному Солнцу zbw 12.02.2026 И ясному Солнцу, и светлой Луне. В мире покоя нет и люди не могут жить в тишине. А жить им немного лет.
«Знание-Сила» zbw 12.02.2026 «Знание-Сила» «Время-Деньги» «Деньги -Пуля»	SDL3 для Web (WebAssembly): Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 12.02.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами и вызывать обработчики событий столкновения. . . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL_LoadPNG (без SDL3_image) 8Observer8 11.02.2026 Содержание блога Библиотека SDL3 содержит встроенные инструменты для базовой работы с изображениями - без использования библиотеки SDL3_image. Пошагово создадим проект для загрузки изображения. . .	SDL3 для Web (WebAssembly): Загрузка PNG с прозрачным фоном с помощью SDL3_image 8Observer8 10.02.2026 Содержание блога Библиотека SDL3_image содержит инструменты для расширенной работы с изображениями. Пошагово создадим проект для загрузки изображения формата PNG с альфа-каналом (с прозрачным. . .

Поиск содержимого поля формы Access в ячейках Excel (части или полного)

Решение

Решение

mobile 26826 / 14506 / 3192 Регистрация: 28.04.2012 Сообщений: 15,782
	19.03.2015, 15:33
	Прежде всего проверил бы Poisk2 (Debug.Print Poisk2). Та ли там величина, которую Вы ищете? Например, если акс посчитает, что в полях Сумма_долга и Сумма_процентов строки, а не числа, то вместо ожидаемого, к примеру 100+50 равного 150 будет 10050. Или в полях есть длинная мантисса, которая на форме скрывается форматом. Или одно из полей NULL. Вообщем, эту величину надо проверять. И только потом можно двигаться дальше, если поиск не идет по иной причине. Да и вопрос стоит задавать на форуме ВБА или Excel. 1

@intersk 114 / 4 / 0 Регистрация: 07.09.2014 Сообщений: 329
	20.03.2015, 15:53 [ТС]
	mobile, файл Excel, с которым работает код это сетевой файл для работы нескольких пользователей. Поэтому мне надо как-то заблокировать файл от изменений на момент поиска, на момент работы кода. Моя идея - открыть файл, тогда другим пользователям он будет недоступен для изменения (установлены ограничение на одновременное редактирование). Как-то ещё можно пометить файл на запрет изменения, на блокировку? вы умеете работать с таблицами напрямую, это круто! я пока этого не умею. Добавлено через 7 минут При тестировании новый код нашел только одну строку (это неправильно). Мой код с открытием Excel ищет две строки (что правильно). Где-то опечатка в коде или цикл прерывается.... Добавлено через 7 минут обнаружил Exit For , он останавливал поиск на первом значении Добавлено через 8 минут ищет и выводит только по тестовой (упрощенной) записи, по реальной ищет (это видно по отладочному окну - там выводиться количество строк столько раз, сколько найдено строк, у вас есть Debug.print в коде), но вот список почему то не заполняется. Вроде и кавычки не причем... 0