Member 12692000 Ответов: 4

Как мне избавиться от ошибки, которую я получаю в VBA excel

У меня есть книга excel с 2 текстовыми полями, в которые я в основном помещаю файл для копирования данных(динамически получаю диапазон для копирования) и еще один для копирования данных.
Ниже приведен мой код:

Что я уже пробовал:

Private Sub copydata_Click()
	Workbooks.Open Filename:=TextBox1.Text
    Workbooks.Open Filename:=TextBox2.Text
    
    Windows("f_database.xlsx").Activate

    'Range("A1").Select
    Cells.Find(What:="ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("data_output.xlsx").Activate
    Range("I17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                  :=False, Transpose:=False




    Windows("f_database.xlsx").Activate
    Range("A1").Select
    Cells.Find(What:="Address", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("data_output.xlsx").Activate
    Range("A17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                  :=False, Transpose:=False


    MsgBox "Done!"
End Sub


Когда я запускаю код с помощью кнопки, я получаю ошибку(в строке Range("A1").Select ):
Run-time error '1004'
Application-defined or object-defined error


Если я комментирую строку, то получаю другую ошибку :
Run-time error '91'
Object variable or With block variable not set


Почему это происходит и как мне это исправить?

форма.файл xlsm
f_database.xlsx
data_output.xlsx

Richard MacCutchan

Вы пропустили вызов активации для выбора активного листа?

Member 12692000

Даже если я добавлю activeworkbook.рабочие листы("база данных").активация , ошибки все еще появляются. Я в замешательстве...

Dave Kreskowiak

Сообщение об ошибке означает, что переменная, которую вы пытаетесь использовать, является null или Nothing в VB.

Ваш код предполагает, что что-то сработало и вернуло то, что ожидалось, в переменную, но этого не произошло. Проверьте содержимое переменных объекта, прежде чем пытаться их использовать.

Member 12692000

Я не могу понять, что и почему является нулем или ничем в моем коде. Кажется, все в порядке, но это явно не так...

4 Ответов

Рейтинг:
33

Maciej Los

Я бы избегал использования Activate и Select методы, обусловленные несколькими причинами. Видеть:
excel-vba - избегайте использования SELECT или ACTIVATE | excel-vba Tutorial[^]
Как избежать метода Select в VBA & Why - Excel Campus[^]
Мощность VBA для Excel секрет, старайтесь не использовать выберите[^]

Основная идея, чтобы избежать использования вышеуказанных методов, заключается в том, чтобы работать в контексте. Что это значит? Например, если вы хотите вставить данные в ячейку на определенном листе, вам нужно "сказать" приложению MS Excel, чтобы сделать это:

'define variables
Dim sFileName1 As String, sFileName2 As String 
Dim wbk1 As Workbook, wbk2 As Workbook
Dim wsh1 As Worksheet, wsh2 As Worksheet

'get the name of workbooks
sFileName1 = TextBox1.Text
sFileName2 = TextBox2.Text
'open workbooks
Set wbk1 = Application.Workbooks.Open(sFileName1)
Set wbk2 = Application.Workbooks.Open(sFileName2)
'define sheets you want to works with
Set wsh1 = wbk1.Worksheets("Sheet1")
Set wsh2 = wbk2.Worksheets("Sheet1")
'copy data!
wsh1.Range("A1") = wsh2.Range("A1") 
'further code
'...
'finally - clean up
Set wbk1 = Nothing
Set wbk2 = Nothing
Set wsh1 = Nothing
Set wsh2 = Nothing


Примечание: приведенный выше код не содержит обработчика ошибок. Рекомендуется использовать его!

Кстати: есть несколько других способов копирования данных. Пожалуйста, прочтите это: Копирование данных между листами Excel с помощью VBA[^]

CHill60

Опереди меня! 5 б

Maciej Los

Спасибо, Кэролайн.

Member 12692000

Привет, Мацей, спасибо за ответ. Можете ли вы показать мне, как избежать использования методов Activate и Select в моем приведенном выше коде, где мне нужно найти соответствующий диапазон для копирования данных? Диапазон, который я пытаюсь скопировать, не фиксирован в разных файлах базы данных, поэтому я не могу жестко закодировать диапазон, который я пытаюсь скопировать, он должен быть динамическим(следовательно, я использовал ячейки.метод find).

Заранее спасибо

Maciej Los

Я уже показал вам, как избежать использования метода активации и выбора. Все, что вам нужно сделать, это изменить код в соответствии с вашими потребностями. Ничего сложного...

Member 12692000

Я имел в виду, как я могу использовать клетки.найдите способ копирования данных без использования select и/или activate...
Вы проверили мой код в вопросе?. Я не могу придумать другого способа копирования данных без использования метода find

Member 12692000

Я также опубликовал решение, следуя вашему подходу, однако все еще используя ячейки.метод find. Не могли бы вы пролистать мой код и предложить какие-либо изменения в этом коде, чтобы сделать его более быстрым, так как мне придется делать копию с помощью ячеек.найдите технику больше, чем просто дважды...
Спасибо

Рейтинг:
1

CHill60

Из комментариев ... это раздел вашего текущего кода ...

'copy data!
Dim rgFound As Range
Set rgFound = wsh1.Cells.Find(What:="ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                               :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                              False, SearchFormat:=False)
Dim x As Range, y As Range, z As Range
Set x = rgFound.Offset(1, 0)
Set y = x.End(xlDown)
Set z = Range(x.Address, y.Address)

wsh1.Range(z.Address).Copy
wsh2.Range("I17").PasteSpecial xlPasteValues
Попробуйте заменить его на
'Find the start of the data - title is "ID"
Dim rgFound As Range
Set rgFound = wsh1.Cells.Find(What:="ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                               :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                              False, SearchFormat:=False)
Dim x As Range, y As Range, rngSource As Range, rngTarget As Range
Set x = rgFound.Offset(1, 0)
'Find end of the data
Set y = x.End(xlDown)
'rngSource now contains the source range of data
Set rngSource = wsh1.Range(x.Address, y.Address)
'Work out the target range
Set rngTarget = wsh2.Range("$I$17:$I$" & CStr(17 + rngSource.Rows.Count))
'Set the target values
rngTarget.Value = rngSource.Value

Maciej Los

5ed!

CHill60

Спасибо - вы сделали всю начальную работу ног, хотя!

Рейтинг:
1

raddevus

Если вы сделаете следующее, Вы увидите, что он начинает работать:

'replace Windows("f_database.xlsx").Activate with the following line:
 Sheet1.Activate
' as a test replace Range("A1").Select with the following:
Range("A1..B5").Select
' you will see the code run and the cells will be selected.
' Hopefully from there you'll see that the Activate of the Windows("f_database.xslx") is failing and work backward from there.


После того, как OP прокомментировал, я обнаружил, что OP нужно добавить новый модуль в проект VBA, а затем добавить туда код.

Смотрите это изображение, чтобы узнать, как добавить новый модуль в VBA (Excel) : https://i.stack.imgur.com/OTELN.png[^]

Как только код будет добавлен в модуль, он будет работать без ошибок.

Вот пример, который вы можете добавить в модуль, чтобы убедиться, что он работает:
Sub Test()
    Windows("f_database.xlsx").Activate

    Range("A1..A5").Select
End Sub

Member 12692000

Если я сделаю то, что вы скажете, то sheet1 form.xlsm активируется не так f_database.xlsx-с. Кроме того, в чем причина для Windows("f_database.xlsx").Активация не удалась?

raddevus

Это интересно, потому что я попробовал его с локальным дополнительным файлом электронной таблицы, и он тоже потерпел неудачу. Это вопрос безопасности. Вам нужно добавить модуль, а затем добавить свой код в этот модуль, и вы сможете его запустить.
Вот как вы добавляете новый модуль ---> https://i.stack.imgur.com/OTELN.png
Я действительно решил эту проблему в другой раз, и вы можете увидеть мой ответ (который отображает это изображение) в StackOverflow: https://stackoverflow.com/questions/33741488/cannot-run-the-macro/42773999#42773999
Мой ответ не является официальным ответом, но был поддержан больше, чем официальный. :)
Это поможет вам найти решение. В конце концов, проблема действительно связана с новыми функциями безопасности Excel-он не хочет, чтобы другие файлы открывались, если код не находится в защищенной области модуля. Пожалуйста, дайте мне знать, если это сработает.

Member 12692000

Привет, раддевус, ваше отдельное модульное решение все еще выдает ту же ошибку..

Maciej Los

Что ж...
Я бы избегал использования Activate и Select методы. Пожалуйста, смотрите мой ответ.

Рейтинг:
0

Member 12692000

Ну я изменил свой код используя предложенный Мацей Лосом метод и до сих пор он кажется работает но я не уверен что это лучше чем использовать его

Цитата:
Активация и выбор методов
....

Private Sub CommandButton1_Click()
'define variables
    Dim sFileName1 As String, sFileName2 As String
    Dim wbk1 As Workbook, wbk2 As Workbook
    Dim wsh1 As Worksheet, wsh2 As Worksheet

    'get the name of workbooks
    sFileName1 = TextBox1.Text
    sFileName2 = TextBox2.Text
    'open workbooks
    Set wbk1 = Application.Workbooks.Open(sFileName1)
    Set wbk2 = Application.Workbooks.Open(sFileName2)
    'define sheets you want to works with
    Set wsh1 = wbk1.Worksheets("database")
    Set wsh2 = wbk2.Worksheets("Sheet1")

    'copy data!
    Dim rgFound As Range
    Set rgFound = wsh1.Cells.Find(What:="ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                                   :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                                  False, SearchFormat:=False)
    Dim x As Range, y As Range, z As Range
    Set x = rgFound.Offset(1, 0)
    Set y = x.End(xlDown)
    Set z = Range(x.Address, y.Address)

    wsh1.Range(z.Address).Copy
    wsh2.Range("I17").PasteSpecial xlPasteValues



    Set rgFound = wsh1.Cells.Find(What:="Address", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                                        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                                  False, SearchFormat:=False)

    Set x = rgFound.Offset(1, 0)
    Set y = x.End(xlDown)
    Set z = Range(x.Address, y.Address)

    wsh1.Range(z.Address).Copy
    wsh2.Range("A17").PasteSpecial xlPasteValues


    'finally - clean up
    Set wbk1 = Nothing
    Set wbk2 = Nothing
    Set wsh1 = Nothing
    Set wsh2 = Nothing
End Sub


Можно ли сделать это более эффективным ?

CHill60

Да - прекратите использовать копирование и вставку! См. решение по @Мачей-Лос и использовать 

wsh2.Range("A17") = wsh2.Range(z.Address)

Member 12692000

Это не сработало в моем коде, поэтому я использовал метод копирования и вставки вместо `wsh2.Range("A17") = wsh1.Диапазон(z.адрес)` и так далее...

CHill60

Попробуй 

wsh2.Range("A17").Value = wsh2.Range(z.Address).Value

Единственная другая проблема может заключаться в том, что ваш источник и цель-это разные "формы".
Проблема с копированием и вставкой заключается в том, что вставленные данные могут оказаться в любом месте - попробуйте начать копирование и вставку большого диапазона ячеек по одной, а затем, пока он работает, щелкните по текстовому документу ... все ваши данные "excel" окажутся в документе вместо этого.

CHill60

Я также должен отметить, что недавно я заменил некоторый(чужой) код копирования и вставки на targetrange.value = sourcerange.value и сократил время, необходимое для запуска, примерно с 30 минут до примерно 10 секунд!

Member 12692000

Хорошо, если я использую `wsh2.Range("A17").Value = wsh1.Ассортимент(з.- Адрес).Value`, то вставляет только одно значение, а не весь диапазон в разные ячейки. Итак, каково же решение этой проблемы?

CHill60

Определите весь диапазон вместо A17 и z.адрес, например 

wsh2.Range("$A$2:$BD$3001").Value = …
Оба диапазона должны быть одинакового размера и формы

Member 12692000

Я не могу жестко закодировать диапазон значений вставки, он должен быть динамическим, поэтому я попробовал его как ` wsh2.Range("B17:B" & z.Count&"""). Value = wsh1.Ассортимент(з.- Адрес).Value ` но он показывает синтаксическую ошибку...что я здесь делаю не так?

CHill60

Какая синтаксическая ошибка?