Доброго времени суток уважаемы форумчане! Прошу помощи в следующем вопросе: Есть файл excel скажем так - эталонный, в который необходимо загружать данные из других файлов имеющих различное расположение столбцов с нужными значениями, а также могут отсутствовать некоторые столбцы. Идея для загрузки такова - что перед как загружать файл оператор открывает загружаемый файл - смотрит глазами где-какой столбец по счету, все ли нужные столбцы присутствуют или какие то отсутствуют и с какой строки начинать чтение файла. После этого в эталонном файле запускает макрос - выбирает нужный для обработки файл, затем появляется форма в которой нужно или отметить или ввести значения столбцов и начальной ячейки. Если нужного столбца в файле нет - то не использовать это значение. После чего запускается обработка файла и значения дописываться к уже загруженным данным. Вот сам макрос [vba]
Код
Sub Макрос1() Dim FilePath$, FileName As String, intPos% Dim WbRead As Workbook Dim sh As Worksheet Dim x, z(), i As Long, k As Long
Set shd = ActiveWorkbook.ActiveSheet 'определяем активный лист 'выбираем книгу для загрузки With Application.FileDialog(msoFileDialogFilePicker) .Title = "Папка для работы с файлами" .ButtonName = "Select": .AllowMultiSelect = False If .Show Then FilePath = .SelectedItems(1) Else Exit Sub End With 'выбираем книгу для загрузки
Set WbRead = Nothing: Set WbRead = Workbooks.Open(FileName:=FilePath, ReadOnly:=True) 'открывем файл для чтения
'сразу определяем имя файла для дальнейшего использования intPos = InStrRev(FilePath, "\") FileName = Right(FilePath, Len(FilePath) - intPos) ' MsgBox FileName 'сразу определяем имя файла для дальнейшего использования
Set sh = WbRead.Worksheets(1) 'берем данные с первого листа x = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 8) 'берем диапазон ячеек до последней заполненой в столбце А WbRead.Close False: DoEvents 'закрываем прочитаный файл без изменений ReDim z(1 To UBound(x), 1 To 8) With CreateObject("Scripting.Dictionary") For i = 1 To UBound(x): .Item(x(i, 1)) = i: Next i 'заполняем For i = 1 To UBound(x) 'идем по строкам If .exists(x(i, 1)) Then k = .Item(x(i, 1)) 'номер строки z(k, 1) = x(i, 1) 'заполняем значения z(k, 2) = x(i, 2) z(k, 3) = x(i, 3) z(k, 4) = x(i, 4) z(k, 5) = x(i, 5) z(k, 6) = x(i, 6) z(k, 7) = FileName z(k, 8) = Date End If Next i End With
'в этом месте не получается вернуть значения на лист с учетом того, что на листе есть данные (((: 'и нужно дописывать в конец
Application.DisplayAlerts = False Application.DisplayAlerts = True End Sub
[/vba]
Прошу помощи в следующем: 1. Подскажите пожалуйста вернуть данные на лист с учетом ранее введенных данных - т.е. продолжить а не перезаписывать. 2. Как создать форму для ввода или выбора значений и передачи выбора в макрос
Заранее спасибо за ответ. Файл с образцом прилагаю.
Доброго времени суток уважаемы форумчане! Прошу помощи в следующем вопросе: Есть файл excel скажем так - эталонный, в который необходимо загружать данные из других файлов имеющих различное расположение столбцов с нужными значениями, а также могут отсутствовать некоторые столбцы. Идея для загрузки такова - что перед как загружать файл оператор открывает загружаемый файл - смотрит глазами где-какой столбец по счету, все ли нужные столбцы присутствуют или какие то отсутствуют и с какой строки начинать чтение файла. После этого в эталонном файле запускает макрос - выбирает нужный для обработки файл, затем появляется форма в которой нужно или отметить или ввести значения столбцов и начальной ячейки. Если нужного столбца в файле нет - то не использовать это значение. После чего запускается обработка файла и значения дописываться к уже загруженным данным. Вот сам макрос [vba]
Код
Sub Макрос1() Dim FilePath$, FileName As String, intPos% Dim WbRead As Workbook Dim sh As Worksheet Dim x, z(), i As Long, k As Long
Set shd = ActiveWorkbook.ActiveSheet 'определяем активный лист 'выбираем книгу для загрузки With Application.FileDialog(msoFileDialogFilePicker) .Title = "Папка для работы с файлами" .ButtonName = "Select": .AllowMultiSelect = False If .Show Then FilePath = .SelectedItems(1) Else Exit Sub End With 'выбираем книгу для загрузки
Set WbRead = Nothing: Set WbRead = Workbooks.Open(FileName:=FilePath, ReadOnly:=True) 'открывем файл для чтения
'сразу определяем имя файла для дальнейшего использования intPos = InStrRev(FilePath, "\") FileName = Right(FilePath, Len(FilePath) - intPos) ' MsgBox FileName 'сразу определяем имя файла для дальнейшего использования
Set sh = WbRead.Worksheets(1) 'берем данные с первого листа x = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 8) 'берем диапазон ячеек до последней заполненой в столбце А WbRead.Close False: DoEvents 'закрываем прочитаный файл без изменений ReDim z(1 To UBound(x), 1 To 8) With CreateObject("Scripting.Dictionary") For i = 1 To UBound(x): .Item(x(i, 1)) = i: Next i 'заполняем For i = 1 To UBound(x) 'идем по строкам If .exists(x(i, 1)) Then k = .Item(x(i, 1)) 'номер строки z(k, 1) = x(i, 1) 'заполняем значения z(k, 2) = x(i, 2) z(k, 3) = x(i, 3) z(k, 4) = x(i, 4) z(k, 5) = x(i, 5) z(k, 6) = x(i, 6) z(k, 7) = FileName z(k, 8) = Date End If Next i End With
'в этом месте не получается вернуть значения на лист с учетом того, что на листе есть данные (((: 'и нужно дописывать в конец
Application.DisplayAlerts = False Application.DisplayAlerts = True End Sub
[/vba]
Прошу помощи в следующем: 1. Подскажите пожалуйста вернуть данные на лист с учетом ранее введенных данных - т.е. продолжить а не перезаписывать. 2. Как создать форму для ввода или выбора значений и передачи выбора в макрос
Заранее спасибо за ответ. Файл с образцом прилагаю.Ed_Vard
По коду - похоже пытались переделывать что-то моё, я тоже люблю в одной строке словарь заполнять Но словарь тут вообще и совершенно лишний, в этом конкретном коде. По задаче - если уж оператор сперва открывает файл и на него смотрит - то можно тут же по Alt+F8 запустить код, а он уже посредством инпутбоксов типа8 попросит оператора ткнуть мышью в нужные заголовки столбцов - так сразу и файл укажете, и начало таблицы, и расположение столбцов, и каких столбцов нет можно определить. Это как идея, писать не хочется...
По коду - похоже пытались переделывать что-то моё, я тоже люблю в одной строке словарь заполнять Но словарь тут вообще и совершенно лишний, в этом конкретном коде. По задаче - если уж оператор сперва открывает файл и на него смотрит - то можно тут же по Alt+F8 запустить код, а он уже посредством инпутбоксов типа8 попросит оператора ткнуть мышью в нужные заголовки столбцов - так сразу и файл укажете, и начало таблицы, и расположение столбцов, и каких столбцов нет можно определить. Это как идея, писать не хочется...Hugo
Но словарь тут вообще и совершенно лишний, в этом конкретном коде.
Дело в том - что возможно в дальнейшем нужно будет производить какие то вычисления - поэтому использовал словарь. Простой перенос из файла проблем не вызывал.
то можно тут же по Alt+F8 запустить код, а он уже посредством инпутбоксов типа8 попросит оператора ткнуть мышью в нужные заголовки столбцов - так сразу и файл укажете, и начало таблицы, и расположение столбцов, и каких столбцов нет можно определить.
Но словарь тут вообще и совершенно лишний, в этом конкретном коде.
Дело в том - что возможно в дальнейшем нужно будет производить какие то вычисления - поэтому использовал словарь. Простой перенос из файла проблем не вызывал.
то можно тут же по Alt+F8 запустить код, а он уже посредством инпутбоксов типа8 попросит оператора ткнуть мышью в нужные заголовки столбцов - так сразу и файл укажете, и начало таблицы, и расположение столбцов, и каких столбцов нет можно определить.
Set r1 = Application.InputBox(Prompt:="Выделите заголовок столбца Товар", Title:="Диапазон", Type:=8)
[/vba] вернёт ссылку на указанную юзером мышью ячейку (заголовок столбца с например товарами). Т.е получаете сразу все нужные данные - файл, лист, ячейку с координатами. Ну или пусть сразу выбирает весь диапазон с данными - так сразу и размер таблицы получите, у остальных столбцов достаточно указать только заголовок.
Разжую - [vba]
Код
Set r1 = Application.InputBox(Prompt:="Выделите заголовок столбца Товар", Title:="Диапазон", Type:=8)
[/vba] вернёт ссылку на указанную юзером мышью ячейку (заголовок столбца с например товарами). Т.е получаете сразу все нужные данные - файл, лист, ячейку с координатами. Ну или пусть сразу выбирает весь диапазон с данными - так сразу и размер таблицы получите, у остальных столбцов достаточно указать только заголовок.Hugo
Set r1 = Application.InputBox(Prompt:="Выделите заголовок столбца Товар", Title:="Диапазон", Type:=8)
Спасибо - документацию почитал. Диапазон может быть и 10000-20000 строк - и указывать его мышкой... Диапазон я и так скопирую - без словаря и этого диалога - а вот указать какому столбцу соответствует каждый столбец - это подойдет. А вот по этому вопросу не подскажите? 1. Подскажите пожалуйста вернуть данные на лист с учетом ранее введенных данных - т.е. продолжить а не перезаписывать.
Set r1 = Application.InputBox(Prompt:="Выделите заголовок столбца Товар", Title:="Диапазон", Type:=8)
Спасибо - документацию почитал. Диапазон может быть и 10000-20000 строк - и указывать его мышкой... Диапазон я и так скопирую - без словаря и этого диалога - а вот указать какому столбцу соответствует каждый столбец - это подойдет. А вот по этому вопросу не подскажите? 1. Подскажите пожалуйста вернуть данные на лист с учетом ранее введенных данных - т.е. продолжить а не перезаписывать.Ed_Vard
Сообщение отредактировал Ed_Vard - Вторник, 26.06.2018, 11:44
Ну раз 10000-20000 тогда конечно не нужно заставлять юзера всё выделять, пусть укажет только заголовок, а далее кодом определите где данные заканчиваются. И по п.1 - точно так же определите где данные заканчиваются, вот на строку ниже и выводите. Вот же в коде выше применяете: [vba]
Код
sh.Range("a" & sh.Rows.Count).End(xlUp)
[/vba] - вернёт ячейку по столбцу A
Ну раз 10000-20000 тогда конечно не нужно заставлять юзера всё выделять, пусть укажет только заголовок, а далее кодом определите где данные заканчиваются. И по п.1 - точно так же определите где данные заканчиваются, вот на строку ниже и выводите. Вот же в коде выше применяете: [vba]
Ну раз 10000-20000 тогда конечно не нужно заставлять юзера всё выделять, пусть укажет только заголовок, а далее кодом определите где данные заканчиваются.
Повторюсь - данные для загрузки имеют разную структуру - т.е. полностью не совпадают с эталонным - могут быть столбцы, а могут и не быть - могут идти в другой последовательности - они загружаются в один общий файл с однородной структурой. Весь диапазон выделить можно - но сопоставить каждый столбец загружаемого файла с эталонным - это нужно на каждый столбец вызывать этот метод?
Ну раз 10000-20000 тогда конечно не нужно заставлять юзера всё выделять, пусть укажет только заголовок, а далее кодом определите где данные заканчиваются.
Повторюсь - данные для загрузки имеют разную структуру - т.е. полностью не совпадают с эталонным - могут быть столбцы, а могут и не быть - могут идти в другой последовательности - они загружаются в один общий файл с однородной структурой. Весь диапазон выделить можно - но сопоставить каждый столбец загружаемого файла с эталонным - это нужно на каждый столбец вызывать этот метод?
если вы сможете идентифицировать каждый прайс(например по названию или по месту хранения или задав вопрос пользователю типа: "Чей прайс?"), то можно на отдельном листе сделать шаблон последовательности столбцов и брать потом номера столбцов для обработки из шаблона
если вы сможете идентифицировать каждый прайс(например по названию или по месту хранения или задав вопрос пользователю типа: "Чей прайс?"), то можно на отдельном листе сделать шаблон последовательности столбцов и брать потом номера столбцов для обработки из шаблонаboa
boa, Большое спасибо - да - это именно так! А Вы не подскажите такой вопрос - читаю читаю про метод - и не могу понять как использовать его! Я так думаю - что вызывается [vba]
Код
Set r1 = Application.InputBox(Prompt:="Выделите заголовок столбца Товар", Title:="Диапазон", Type:=8)
[/vba]
оператор щелкает по заголовкам столбцов А или В и т.д. - выделяет столбец целиком, а как передать порядковый номер столбца например сюда z(k, 1) = x(i, 1) в поле x(i, 1) вместо 1? Переменной r1 присваивается при выделении всего столбца значение $А:$А, а вот как получить что номер столбца 1 со второй строки недопонимаю?
boa, Большое спасибо - да - это именно так! А Вы не подскажите такой вопрос - читаю читаю про метод - и не могу понять как использовать его! Я так думаю - что вызывается [vba]
Код
Set r1 = Application.InputBox(Prompt:="Выделите заголовок столбца Товар", Title:="Диапазон", Type:=8)
[/vba]
оператор щелкает по заголовкам столбцов А или В и т.д. - выделяет столбец целиком, а как передать порядковый номер столбца например сюда z(k, 1) = x(i, 1) в поле x(i, 1) вместо 1? Переменной r1 присваивается при выделении всего столбца значение $А:$А, а вот как получить что номер столбца 1 со второй строки недопонимаю?Ed_Vard
если вы сможете идентифицировать каждый прайс(например по названию или по месту хранения или задав вопрос пользователю типа: "Чей прайс?"), то можно на отдельном листе сделать шаблон последовательности столбцов и брать потом номера столбцов для обработки из шаблона
Большое спасибо за помощь! Их в принципе можно идентифицировать - но даже от одного и того же поставщика может прийти другой прайс - я думаю - это не благодарное занятие! Это нужно будет за этим постоянно следить! А так пользователь сопоставил столбцы - загрузил себе в файл и уже дальше с работает с этим файлом!
если вы сможете идентифицировать каждый прайс(например по названию или по месту хранения или задав вопрос пользователю типа: "Чей прайс?"), то можно на отдельном листе сделать шаблон последовательности столбцов и брать потом номера столбцов для обработки из шаблона
Большое спасибо за помощь! Их в принципе можно идентифицировать - но даже от одного и того же поставщика может прийти другой прайс - я думаю - это не благодарное занятие! Это нужно будет за этим постоянно следить! А так пользователь сопоставил столбцы - загрузил себе в файл и уже дальше с работает с этим файлом!Ed_Vard
Я тут случайно при правке потёр пост - а было там примерно так: Просите щёлкнуть по заголовку диапазона таблицы, где например написано "Товар" или даже лучше по первой ячейке с данными товаров (например это ячейка B3)- получаете точный адрес начала диапазона, высчитываете кодом конец товаров, далее просите щёлкнуть по ценам, количеству и т.д. - и тут уже не важно, хоть стобец F:F целиком выбирают, тут уже главное номер столбца получить. И не нужно юзеру сперва смотреть на таблицу и на бумажке записывать где какие столбцы и как расположены, чтоб позже уже без этого файла (хотя не факт..) заводить в форму или куда там номера/адреса данных руками, что чревато разными ошибками.
Я тут случайно при правке потёр пост - а было там примерно так: Просите щёлкнуть по заголовку диапазона таблицы, где например написано "Товар" или даже лучше по первой ячейке с данными товаров (например это ячейка B3)- получаете точный адрес начала диапазона, высчитываете кодом конец товаров, далее просите щёлкнуть по ценам, количеству и т.д. - и тут уже не важно, хоть стобец F:F целиком выбирают, тут уже главное номер столбца получить. И не нужно юзеру сперва смотреть на таблицу и на бумажке записывать где какие столбцы и как расположены, чтоб позже уже без этого файла (хотя не факт..) заводить в форму или куда там номера/адреса данных руками, что чревато разными ошибками.Hugo
Hugo, Да - спасибо! Правильно ли я думаю алгоритм действий? При запуске макроса открывается выбор файла, а затем метод [vba]
Код
Set r1 = Application.InputBox(Prompt:="Выделите заголовок столбца Товар", Title:="Диапазон", Type:=8)
[/vba] для определения начальной ячейки для переменной вместо sh.Range("a2") - а как получить переменную для sh.Range("a") и как ее тогда записать в код? А если столбец с данными не первый а третий например - это весь диапазон съедет на 8 и не захватит первые два столбца в которых тоже будут какие то данные?
[vba]
Код
x = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 8)
[/vba]
А уже затем последовательно для каждого столбца для определения его номера для x(i, 1-6). А вот если искомого столбца нет - в ответ методу можно передать 0 или он свалиться в ошибку?
Да Вы правы - гораздо удобней визуально выбирать, чем через бумажку.
Hugo, Да - спасибо! Правильно ли я думаю алгоритм действий? При запуске макроса открывается выбор файла, а затем метод [vba]
Код
Set r1 = Application.InputBox(Prompt:="Выделите заголовок столбца Товар", Title:="Диапазон", Type:=8)
[/vba] для определения начальной ячейки для переменной вместо sh.Range("a2") - а как получить переменную для sh.Range("a") и как ее тогда записать в код? А если столбец с данными не первый а третий например - это весь диапазон съедет на 8 и не захватит первые два столбца в которых тоже будут какие то данные?
[vba]
Код
x = sh.Range(sh.Range("a2"), sh.Range("a" & sh.Rows.Count).End(xlUp)).Resize(, 8)
[/vba]
А уже затем последовательно для каждого столбца для определения его номера для x(i, 1-6). А вот если искомого столбца нет - в ответ методу можно передать 0 или он свалиться в ошибку?
Да Вы правы - гораздо удобней визуально выбирать, чем через бумажку.
Когда юзер укажет на товар (он ведь основной столбец) - Вы получите строку начала таблицы, и строку конца (вычислите по наличию товара), ну и позицию этого столбца. Далее юзер покажет позиции других столбцов, а больше ничего и не нужно. Если чего-то нет - пусть жмёт отмену, получите в переменной nothing. Нужно конечно всё обработать кодом, для каждого значимого столбца свою переменную можно завести, а можно просто собирать например коллекцию номеров столбцов, если количество не определено. Например знаете что должны быть выделены товары, количество, цена, адрес доставки, получаете коллекцию 3, 2, 0, 1 - значит в таблице порядок такой: адрес, количество, товар, а цены нет Ну или если коллекция - то вообще можно к столбцам обращаться по индексу коллекции, а не по номерам что дал юзер - ибо всегда в коллекции будет правильный порядок, ну разве что что-то может быть в столбце 0 Т.е. когда нужен адрес - берёте из коллекции четвёртый элемент, и не важно что там прописано - туда и идёте в таблице. Сейчас на примере в первый столбец.
Когда юзер укажет на товар (он ведь основной столбец) - Вы получите строку начала таблицы, и строку конца (вычислите по наличию товара), ну и позицию этого столбца. Далее юзер покажет позиции других столбцов, а больше ничего и не нужно. Если чего-то нет - пусть жмёт отмену, получите в переменной nothing. Нужно конечно всё обработать кодом, для каждого значимого столбца свою переменную можно завести, а можно просто собирать например коллекцию номеров столбцов, если количество не определено. Например знаете что должны быть выделены товары, количество, цена, адрес доставки, получаете коллекцию 3, 2, 0, 1 - значит в таблице порядок такой: адрес, количество, товар, а цены нет Ну или если коллекция - то вообще можно к столбцам обращаться по индексу коллекции, а не по номерам что дал юзер - ибо всегда в коллекции будет правильный порядок, ну разве что что-то может быть в столбце 0 Т.е. когда нужен адрес - берёте из коллекции четвёртый элемент, и не важно что там прописано - туда и идёте в таблице. Сейчас на примере в первый столбец.Hugo
Ну вот примерно так, можете тасовать столбцы как угодно. Первый msgbox просто информация, второй показывает данные. Но нет обработки отказа от выбора чего-либо, кроме товара! Это некогда делать, убегаю.
Ну вот примерно так, можете тасовать столбцы как угодно. Первый msgbox просто информация, второй показывает данные. Но нет обработки отказа от выбора чего-либо, кроме товара! Это некогда делать, убегаю.Hugo