Есть текстовые значения в таблице со столбцом ar[АРТИКУЛ], мне надо их воспользовать в запросе sql в разделе where Ниже приведен код использования даты в запросе sql который рабочий, дата находится в другом документе.
Код
let
MyData = Text.From(Excel.Workbook(File.Contents("D:\test\test.xlsx", null,true,{[Item="key1",Kind="Table"]}[Data]{[Parameter="MyData"]}[Value])
Источник=Oracle.Database("OMPAW", [Query="
With prm as (Select to_date('" & MyData & "', 'dd.mm.yyyy') as dat from dual)
Далее идёт SQL запрос
Мне надо точно так же, но в MyData вставить текст - все записи ar[АРТИКУЛ] через разделитель ',' Text.Combine([АРТИКУЛ],"','") - не знаю как правильно прописать Ещё отличие что не надо брать данные для MyData из другого документа, таблица ar и запрос sql находятся в одном документе. файл, наверное, сильно не поможет, я могу в него поместить только таблицу ar, код sql Oracle работает только в рабочей сети Буду благодарна за помощь.
Добрый день! Необходима помощь по Power Query. Надо в столбце KPI заменить значения например "зап.части, руб" на "ZR" и есть список то ЧТО заменить и НА что зап.части, руб ZR деталь, шт DQ смесь, банки SQ стекло, руб GR
Я прочитала что есть функция Table.TransformColumns, но как передать не таблицу а список прописанных значений не знаю. Видела такой вариант, но мне не подходит, т.к. у меня слово ЧТО заменить с пробелами и запятыми
Код
= Table.TransformColumns(YourTable, {"Cars", each Record.FieldOrDefault([Volkswagen = "VW", Renault = "RN", Dacia = "DC"],_,_)})
Когда я открываю файл экселя на общем диске и кто-то в нем сидит, мне пишет имя пользователя, того, кто сидит. Можно как то в переменную получить это имя пользователя в код VBA?
(WriteReservedBy пишет мое имя пользователя, не смотря на то что файл открыт другим сотрудником и у меня документ открыт в режиме чтения.)
Имеется массив arr (1 to 5, 1 to 4). Есть Listbox из ActiveX Как заполнить ListBox этим массивом? Важно! Без участия ячеек = не беря заполнение из диапазона на листе. (В тесте диапазон берется из ячеек [A8:D12], в фактическом использовании – создается массив и он заполняется виртуально)
Мои попытки с .List успехом не увенчаплись, не работает. Неужели только циклом в каждую ячеечку, прописывать в ListBox? (Мне не обязательно, будет это ListBox из ActiveX или из элементов управления.)
Скрытый текст
Не работает:
Код
Sub test()
arr = [A8:D12]
Set lbx = ThisWorkbook.Sheets("Лист1").ListBoxUser
lbx.List = arr
End Sub
Вставка в книгу данных из другой с возможностью обновления по запросу., Как вставить данные в книгу из выгрузки-xlsx и обновлять через контекстное меню обновить (без копи-пейста).
Делаю выгрузку из САП и сохраняю в файл xlsx на диске. В текущем процессе – я открываю второй файл, копи-пэйст выгрузку в этот второй файл, протягиваю формулы, обновляю сводные – мой отчет готов, рассылаю.
Хотелось бы так:
Я сохранила выгрузку. Открываю второй файл, нажимаю на листе “обновить” – на листе появляются данные из только что сохраненной выгрузки. Далее надеюсь освоить PowerPivot построенный на основании этой обновленной выгрузки, и получать отчет без промежуточных звеньев и протягиваний формул.
На работе стоит Excel 2013 Professional Plus. PowerPivot есть, Power Query нет! и пока не будет.
Как я могу подлючить этот файл-выгрузку если он в xlsx?
У меня в группе «Данные» 1100 вариантов: Access, интернет, текст, БД. А Excel-книгу как?
1) Все отдельностоящие слова полностью из заглавных букв сделать в формате Первая Буква Заглавная (Proper). Например: «НОВЫЙ СВЕТ СоЛнЦе Луч9 20 САДОВОДСТВО дорога» превратить в: «Новый Свет СоЛнЦе Луч9 20 Садоводство дорога» ?
2) Вытащить улицу и номер дома часто можно с помощью следующего правила: смотрим первую попадающуюся цифру в строке и берем то слово перед ней, которое попадается первым написанным с большой буквы. Пример: «Санкт-Петербург, Обводный канал 45/1 строение 25Б Литер М» вытащит строку «Обводный 45»., потому что 45 - первая цифра в строке. Обводный - первое написанное с заглавной буквы перед 45.
3) Есть улицы-исключения в их название входит цифра (пример: 8 линия В.О. д.10) У меня имеется список исключеений. Как для них сделать чтобы они вытащили ближайшее впереди и ближайший сзади номер? Например "Санкт-Петербург г. 8-я Красноармейская ул. 27 кв.40" -> "8 Красноармейская 27" (?:Кадетская|линия|Советская|Рабфаковский|Предпортовый|Января|Красноармейская|Утиная|Мая|Комсомольская|Муринский|Лахта|Верхний|_) Пыталась сделать сама. Но именно эти задачки у меня не получились. Подскажите, пожалуйста, как они решаются?
Код
Function street_extract_test()
'если слово в строке полностью из заглавных букв превратить его в "Первая Заглавная"
txt = "НОВЫЙ СВЕТ СоЛнЦе Луч9 20 САДОВОДСТВО дорога"
Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "([А-ЯЁ]+{2,})"
regex.Global = True
'txt1 = regex.Replace(txt, Application.WorksheetFunction.Proper("$1"))
'Первая цифра в строке + первое перед этой цифрой слово с Заглавной буквы
txt = "2Санкт-Петербург, Обводный канал 45/1 строение 25Б Литер М"
regex.Global = False
regex.Pattern = "([\s|^]+)([А-Я]+)([А-Яа-я0-9A-Za-z\s]+)(\d+)(\s|$)?"
If regex.test(txt) Then
Set matches = regex.Execute(txt)
txt3 = matches.Item(0)
End If
End Function
Я пыталась сделать такое: если первая отдельностоящая группа это цифры (без вкрапления букв) и есть второе слово за пробелом, то соединить их нижним подчеркиванием. Например "ООО 7 континент мира" -> "7_континент", или "ЗАО 29 спуск" -> "29_спуск", но! "7Б Куликово ЧП" - это "7Б", но! "777 фирма" -> "777" Вы могли бы помочь? Как проверить, что первая группа до пробела это только числа?
Я представляла бы себе паттерн для задачки с цифрами таким: "(^+\d{1,}\s+[А-Яа-яёЁ0-9A-Za-z\-]{1,})(\s|^)" - но в примере ниже он не работает.
Вот этот блок у меня вылетает:
Код
If regex.Test(txt) Then
Set matches = regex.Execute(txt)
txt = matches.Item(0)
txt = Replace(txt, " ", "_")
Else
regex.Pattern = "(^+[А-Яа-яёЁ0-9A-Za-z\-]{1,})(\s|^)"
If regex.Test(txt) Then
Set matches = regex.Execute(txt)
txt = matches.Item(0)
Else
txt = "-"
End If
End If
Смысл кода выше - если первое слово - все_знаки_цифры - то обрабатывем, как я описала выше. Если это буквы, то берем _только_ первое слово. Если букв-цифр не имеется - т.е. пусто или пробелы, то ставим "-".
Подскажите, как правильно написать паттерн в регулярных выражениях, чтобы заменить отдельностоящие слова или выражния. Например: Заменить на "пробел" если попадается что-н из этого: ИП, ООО, ЗАО, Торговая Компания. Я думала что это должно выглядеть примерно так: "{\b(ИП|ООО|ЗАО|Торговая Компания)\b}" Поправьте как правильно?
В книге-примере в ячейке А1 и А2 в ячейках вставлены символы - замок закрытый и замок открытый. Пытаюсь расшифровать - какой код у символа командами Asc, AscB, AscW - они не определяют. Получается что командами Chr, ChrB, ChrW - я вставить эти замки не могу. Других команд не знаю.
Здравствуйте, не получилось у меня создать примера! Но если не получу ответ то попробую дождаться такого косяка и выложу уже с примером. Просто может это уже известная проблема?
Есть таблица-источник с данными, на ней построена сводная (включённый диапазон - вся колонка). Данные постепенно добавляются вниз в таблицу - и бывают такие случаи что обновляю сводную, а вновь добавленные данные не появились. Решение - заново перестраиваю сводную и данные появляются.
Есть ли альтернативный вариант решения проблемы? Буду благодарна за любую помощь и направление в решении этого вопроса! Спасибо!
Добрый день! Помогите пожалуйста понять что не так с Validation. Формирую список общей длинной 56 символов (т.е. под эти ограничения не попадает). Формирование повешено на событие активации листа. Иногда это работает, а иногда выдает ошибку.
Ошибка закономерно появляется если: 1 шаг) копировать лист "обработка заказа" (в эту же книгу) - соответственно активируется новый скопированный лист. 2 шаг) активировать обратно лист "обработка заказа" - тогда срабатывает событие и вылезает ошибка на строке [A3].Validation.Add Type:=xlValidateList, Formula1:=Join(dic.keys, ",")
((Странно, что если я удалю из файла-примера кнопку Сохранить, то ошибка появляться в файле примере не будет. Если я эту кнопку удалю со своего оригинального файла, ошибка по прежнему будет появляться.))
Может я что-то не вижу.. помогите осознать ошибку. Спасибо за рассмотрение.
Здравствуйте! Мне надо проверить что в введенной ячейке нет иных символов, кроме как: ' - может быть может не быть в начале, 0-9 , и точка - если присутствует то между двумя цифрами. Т.е. например '7.1. Наверное это лучше с регулярными выражениями делать? Попыталась поковырять RegExp вроде отрабатывает верно, но может я каких-н подводных камней не учла?
Код
Sub regexp1()
s = "'7.1"
s2 = "7/1"
Set RegExp = CreateObject("VBScript.RegExp")
RegExp.Pattern = "[^'\d\.]"
bRes = RegExp.test(s)
If bRes Then MsgBox "Неверный формат данных " & s
bRes = RegExp.test(s2)
If bRes Then MsgBox "Неверный формат данных " & s2
End Sub
И ещё можно ли составить такой Pattern что бы учитывалось, что апостроф только в начале (может быть\может отсутствовать совсем), точка только между двумя цифрами (не в начале не в конце)? И последнее если ничего не введено, то это тоже правильный формат данных (не знаю почему, но в приведенном примере в принципе это соблюдается). Спасибо!
Добрый день! Подскажите пожалуйста, отслеживаю добавление\удаление строк таким способом (может есть другой способ?)
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge Mod Columns.Count = 0 Then
If Target.CountLarge Mod Rows.Count <> 0 Then
MsgBox "Пуск"
End If
End If
End Sub
Если взять с Листа 2 скопировать строчки (выбрать полную строку) с пятерками и через контекстное меню "Вставить скопированные ячейки" вставить на Лист1 то почему то отслеживание этого события запускается два раза (два раза вылетает msgbox "Пуск"). Может кто-нибудь знает в чем причина и как этого избежать? Спасибо.
Подскажите пожалуйста как правильно передать книгу и лист в другую процедуру? Представляла что это должно выглядеть как то так, но он ругается "byRef type mismatch".
Код
Sub пример1()
Set wb = ActiveWorkbook
Set sh = wb.ActiveSheet
Call пример2(wb, sh)
End Sub
Sub пример2(wb As Workbooks, sh As Sheets)
MsgBox wb.FullName
MsgBox sh.Name
End Sub
Добрый день! Под себя перерабатывала файл с другого сайта по переносу данных из Excel в Word. И у меня возникает ошибка User-defined type is not defined на строках
Код
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
В исходном файле на основании которого корректировала макрос на этой же строчке ошибки не возникает. Помогите разобраться что не так? Просто не первый раз уже такая ошибка вылезает а я не знаю что с ней делать.
Обрезанный пример исходного макроса где работает тоже прилагаю на всякий. Не работает в Excel2Word.xlsm
Добрый день! Подскажите пожалуйста, как передать массив arr в процедуру ИзъятиеМассива У меня не передается и arr пишет как Empty. Помогите пожалуйста найти ошибку!
Код
Sub ИзъятиеМассива()
iCC = 3
iST = 1
arr = СлияниеМассива(2, 1)
Range(Cells(iST, "J", Cells(UBound(arr) + iST, "J") = WorksheetFunction.Transpose(arr)
End Sub
Function СлияниеМассива(iCC, Optional iST, Optional iET) 'As Variant
If IsMissing(iST) Then iST = 2
If IsMissing(iET) Then iET = Cells(Rows.Count, iCC).End(xlUp).Row
For i = iST To iET
sumWord = sumWord & "||" & Cells(i, iCC)
Next
arr = Split(Right(sumWord, Len(sumWord) - 2), "||"
СлияниеМассив = arr
End Function
Добрый день, Есть график со столбцами, по одной шкале отображается процент по второй икс-шкале отображется абсолют.
Столбец с процентами "спрятался" за столбцом с абсолютными значениями. Помогите пожалуйста, можно ли поменять местами столбец-абсолют - что бы был на заднем плане, а столбец с процентами (который пунктирными линиями) на переднем плане?
Подскажите пожалуйста как циклом перебрать элементы в сводной таблице (она строится на олап) ?
Не знаю поможет это или нет, но вот так выглядит макрос записанный макрорекордером на скрытие одного элемента из этой таблицы. Sub Макрос1() ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields( _ "[Артикулы 5].[Артикулкраткий]").HiddenItemsList = Array( _ "[Артикулы 5].[All Артикулы 5].[Т0104]") End Sub
Имеется файл размером более 28 МБ. Проблема только с одним листом - данных на 500 строк,а Excel загружает себе в кэш все пустые ячейки с листа (как я это понимаю) - ползунки справа и снизу идут по всему диапазону листа до строки 1 048 536 и до столбца 16370. Таким образом, любая работа с данными на этом листе приводит к ошибке: "Не хватает ресурсов системы". Причем с прочими листами можно работать без проблем.
Вопрос: как исправить эту проблему? Как вылечить этот лист, чтобы в кэш грузился только тот диапазон, в котором работаешь?
Следующие операции не помогли: - выделение всех пустых ячеек > очистка данных ячеек. - для удаления самих пустых ячеек сразу не хватает ресурсов. - при очистке всех внешних связей книги ситуация не меняется.
Подскажите пожалуйста, как наилучшим образом реализовать задачу.
Простите, тут начну издалека. (Мало ли у этой проблемы есть решение) Мне установили 2007 excel, оставив 2003. Я не хочу пользовать 2007, сделала обратно что бы xls открывались в 2003 (через “открыть с помощью”-“использовать по умолчанию’). Возникло неудобство, что каждый файл открываемый через проводник, из почты или через ярлык открывается в своей_собственной_сессии excel. Что означает, что я, например, не могу между ними писать формулы и т.к. эти две книги, в разных сессиях и друг друга не видят. Например, файл открытый из почты надо сохранить, сначала, а потом через экселевскую ‘открывашку’ открывать в сессии с активным файлом. Это не удобно.
Хочу создать что то вроде общего списка “недавно открытых”.
СОБСТВЕННО ЗАДАЧА: Есть два файла в разных сессиях excel. Я нажимаю на кнопку в одной сессии и .FullName этого файла сохраняется в некое место. Потом в другом файле во второй сессии нажимаю на кнопку и вторым пунктом в этом некоем месте сохраняется .FullName второго файла. Надо в “некое место” записать, а потом иметь возможность из третьей сессии считать, что-то удалить, что-то перезаписать. Хочу иметь нечто вроде “списка активно используемых файлов”, куда можно добавлять/удалять файлы.
Вопрос в этом самом “некоем месте”. Как его можно реализовать? * Если бы у меня всё открывалось в одной сессии это можно было бы записывать в лист надстройки. Но этот вариант не подходит. * В скрипописательстве используют ini файлы для таких случаев. Может есть в Excel подобное? (Это был бы самый простой структурированный вариант) * Запись в txt… один из вариантов. Но его сложнее будет обрабатывать, так, как текст будет идти единой простыней. (Т.е. получается надо считывать простыню, внутри excel сплитовать и обрабатывать, потом сливать и перезаписывать простыню в txt) * Слышала на форуме про запись в регистр – возможно ли его использовать для этой цели? Мне нужно порядка 15 ячеек, куда я хочу записывать пути к часто используемым файлам. * Ну и последний вариант приходящий в голову, это хранение этих данных в специальном файле excel. Мне почему то кажется, что это более медленный вариант, чем запись txt? Но если по скорости так же, то зато тут ничего сплитовать/сливать не надо.
Я пока имею только концептуальное представление о возможных решениях задачки. Очень нужно мнение практиков – как лучше?
Подскажите пожалуйста, как в переменную передать выделенное значение из ListBox? И как определить если ни одного значения не выбрано?
На примере во вложении: Запускаем форму frmListManager
Eсть список имеющихся ссылок в ListBoxLink. Данные в два столбца (в первом имя файла, во втором столбце полное имя файла с путём)
Выделяем значение Selected.xls
Надо что бы по нажатию кнопки ОК выдалось msgbox: Selected.xls + C:\old_doc\Selected.xls А если ничего не выбрано msgbox “Ошибка! Значение не выбрано!”
Иными словами – как в переменные передать выделенные значения ListBox первого и второго столбца.
И ещё вопросик по ListBox: Можно ли что бы при наведении в ListBoxLink, например на Selected.xls, появлялась всплывающая подсказка полного пути к файлу т.е. C:\old_doc\Selected.xls (значение которое находится во втором столбце ListBoxLink)
Как через макрос можно проставить/убрать галочки с CheckBox1 и CheckBox2. И как в переменную можно считать какое стоит значение у этих CheckBox1 и CheckBox2?
Файл с чекбоксами во вложении (чекбоксы двух разных типов)
Есть формула в ячейке B2 вида R1C1 равная =RC[-1]+R[-1]C+R2C3, можно ли в макросе её как-то сконвертировать в вид =A2+B1+$C$2? Пока вижу только один вариант записать её в ячейку как FormulaR1C1, а считать как FormulaLocal.
Может можно как-то проще?
Конвертирую, потому что когда я разбиваю формулу R1C1 по составляющим. То не могу использовать так: txt = Range(RC[-1]).value
Здравствуйте! Есть прекрасный макрос, который умеет добавлять картинки в примечание:
Sub AddCommentPicture() With ActiveCell.AddComment .Visible = False .Text Text:="test" With .Shape .Fill.ForeColor.RGB = RGB(255, 255, 255) .Fill.Transparency = 0# .Fill.UserPicture "C:\test.jpg" End With End With End Sub
Можно ли сделать так, что бы в примечание добавлялась не картинка с жесткого диска, а картинка находящаяся в буфере обмена (скриншот, например)?
Можно ли как-нибудь сделать АВС анализ формулами, что бы не прибегать к ручной сортировке массива?
Пример и алгоритм во вложении.
Хочется, что бы при необходимости обновить АВС не надо было производить ручные сортировки (самая частая причина закрадывающихся ошибок), а просто можно было бы обновить сводную, и АВС пересчитался бы формулами.
Каждый файл xls открывается в отдельном приложении excel, вместо того что бы открываться в окне excel с другими, уже открытыми файлами xls.
Мне дополнительно к 2003 установили 2007 office. У меня все excel файлы ассоциировались с 2007 excel. Но я хочу пользоваться 2003 excel как основным. Я через “открыть с помощью” привязала excel файлы к старому 2003 excel. Теперь у меня каждый рабочий файл открывается в _своём_собственном_ приложении Excel (раньше открывалась в последнем активированном окне excel).
Минусы: я не могу пользоваться формулами между открытыми в разных приложениях excel книгами и немало других минусов.
Надо что бы при открытии файла (например из почты, или проводника) - файл открывался в том же приложении excel что и уже открытые файлы, а не создавал новое “независимое” окно приложения excel.
Можно ли с помощью формул просортировать диапазон и проставить ранг от максильного к минимальному _формулами_ Т.е. надо проставить номер позиции, как если бы диапазон был отсортирован по убыванию.