Макрос изменения свойств подключения

[Barkovnick]

Доброго времени суток, уважаемые спецы!

Помогите человеку, не имеющего опыта на VBA....
Есть таблица, в которую подцепляются данные (4 столбца) из xml, периодически выгружаются новые xml файлы, и выполняется перерасчет свода.

Вопрос номер один собственно состоит в том, как макросом изменять путь к файлу в "Свойствах подключения", тот, что на вкладке "Определение".
Удалять подключение и создавать новое не катит...

Рекордер макросов записывает только:
With ActiveWorkbook.Connections("Внешние Данные")
.Name = "ВнешниеДанные"
.Description = ""
End With
ActiveWorkbook.Connections("Внешние Данные").Refresh

Я так понимаю тут должно быть что то вроде:
Workbooks("Готовый отчет.xlsm").Connections.AddFromFil e _
"C:\Users\BarkovN\Desktop\Data 30.12.xml"


А второй вопрос заключается в том, что из xml подтягиваются данные с "лишними строками", которые я удаляю перебором:
Sheets("Исходные данные").Select
LastRow = Sheets("Исходные данные").UsedRange.Row - 1 + Sheets("Исходные данные").UsedRange.Rows.Count
For r = LastRow To 2 Step -1
If Cells(r, 4) <= 0 Or Cells(r, 2) = "" Then
Rows(r).Delete

В таблице свыше 35 тыс строк (в итоге проц загружен на 50% на 30-40 минут), и что-то мне подсказывает, что есть более оптимальные (жизнеспособные) варианты очистки "лишних строк" (знаю что вопрос заезжен, но работоспособного не нашел)

Помогите, добрые люди!

[EducatedFool]

которые я удаляю перебором:

это плохой метод (ибо крайне медленно работает)

возьмите за основу этот макрос:
http://ExcelVBA.ru/code/ConditionalRowsDeleting
и строки удалятся за несколько секунд.

-------------------------

как макросом изменять путь к файлу в "Свойствах подключения"

With ActiveWorkbook.Connections("Внешние Данные")
  ' попробуйте так
  .SourceConnectionFile = "C:\Users\BarkovN\Desktop\Data 30.12.xml"
  ' или так (оставьте один из 2 вариантов)
  .SourceDataFile = "C:\Users\BarkovN\Desktop\Data 30.12.xml"
End With

(код не проверял - просто поглядел в редакторе VBA свойства объекта Connection)
Список свойств открывается нажатием F2. В поиске там вводим Connection - и смотрим, что у него можно поменять.

[Barkovnick]

возьмите за основу этот макрос:
http://ExcelVBA.ru/code/ConditionalRowsDeleting
и строки удалятся за несколько секунд.

Натыкался на этот метод, заканчивается ошибкой 1004
"Метод Delete из класса Range завершен неверно"
Возможно это связано с форматом таблицы, т.к. подтягиваясь из xml
применяется автофильтр, но даже если его убрать картина таже.
возможно я что то не так делаю в условиях:

Dim ra As Range, delra As Range, i As Long: i = 2
    Application.ScreenUpdating = False
    For Each ra In Sheets("Исходные данные").UsedRange.Rows
    If ra.Cells(4) <= 0 Or ra.Cells(2) = "" Then
    If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
    End If
    Next
    If Not delra Is Nothing Then delra.EntireRow.Delete

With ActiveWorkbook.Connections("Внешние Данные")
  ' попробуйте так
  .SourceConnectionFile = "C:\Users\BarkovN\Desktop\Data 30.12.xml"
  ' или так (оставьте один из 2 вариантов)
  .SourceDataFile = "C:\Users\BarkovN\Desktop\Data 30.12.xml"
End With

Пробовал так. Ошибка 438 "Object doesn't support this property or metod"

[Barkovnick]

Если пытаюсь удалить строку сразу

Dim ra As Range, delra As Range, i As Long: i = 2
    Application.ScreenUpdating = False
    For Each ra In Sheets("Исходные данные").UsedRange.Rows
    If ra.Cells(1, 4) <= 0 Or ra.Cells(1, 2) = "" Then
    ra.Rows.Delete

Получаю ошибку "Недопустимая операция. Была предпринята попытка сдвинуть ячейки таблицы на листе"

[27102014]

Попробуйте настроить под себя такую конструкцию

With Worksheets("CheckList")
For iRow& = 260 To 1 Step -1
If Cells(iRow&, "A:A").Value = "" Then .Rows(iRow&).Delete
Next
End With

[Barkovnick]

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

Попробуйте настроить под себя такую конструкцию

With Worksheets("CheckList")
For iRow& = 260 To 1 Step -1
If Cells(iRow&, "A:A").Value = "" Then .Rows(iRow&).Delete
Next
End With

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

Dim r As Long, LastRow As Long, i As Long: i = 2
    Sheets("ВнешниеДанные").Select
    LastRow = Sheets("ВнешниеДанные").UsedRange.Row - 1 + Sheets("ВнешниеДанные").UsedRange.Rows.Count
    For r = LastRow To 2 Step -1
    If Cells(r, 4) <= 0 Or Cells(r, 2) = "" Then
    Rows(r).Delete
    End If
    Next

Это же тот же построчный перебор???

[27102014]

Да, простой перебор строк и удаление пустых строк, если про мой пример.
В цикле можно задать счетчик, мне не был нужен, т.к. использую ровно столько строк
В Вашей конструкции смущает строка
If ra.Cells(1, 4) <= 0 Or ra.Cells(1, 2) = "" Then
Было бы удобнее, если бы выложили файл, если есть возможность

[Barkovnick]

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

Да, простой перебор строк и удаление пустых строк, если про мой пример.
В цикле можно задать счетчик, мне не был нужен, т.к. использую ровно столько строк
В Вашей конструкции смущает строка
If ra.Cells(1, 4) <= 0 Or ra.Cells(1, 2) = "" Then
Было бы удобнее, если бы выложили файл, если есть возможность

Вы предлагаете тоже самое, "лишние строки" в данных не пустые, условие проверяет 2-ве ячейки в строке.
Перебор очень медленно работает с массивами более 5000 строк.

И главный вопрос все же с тем, как изменить пусть к файлу в свойствах подключения.

[EducatedFool]

как изменить пусть к файлу в свойствах подключения

запустите такой макрос:

sub test()
set x = ActiveWorkbook.Connections("Внешние Данные")
stop
end sub

макрос остановится, - и вы сможете просмотреть содержимое объекта X
(для просмотра, в редакторе VBA нажмите View - Locals Window
в этом окне найдёте X - развернёте плюсиком список свойств, - и увидите, в каком из свойств хранится путь к файлу)

[Barkovnick]

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

развернёте плюсиком список свойств, - и увидите, в каком из свойств хранится путь к файлу)

Как-то нету пути....


http://pixs.ru/showimage/SnimokPNG_1833993_15580548.png


Печаль в общем