Набавите листу имена датотека из фасцикле у Екцелу (са и без ВБА)

Првог дана на послу у малој консултантској фирми три дана сам радио на кратком пројекту.

Рад је био једноставан.

На мрежном диску било је много фасцикли и свака фасцикла је имала стотине датотека у себи.

Морао сам да следим ова три корака:

1. Изаберите датотеку и копирајте њено име.
2. Залепите то име у ћелију у Екцелу и притисните Ентер.
3. Пређите на следећу датотеку и поновите кораке 1 и 2.

Звучи једноставно зар не?

Било је то - једноставно и уз огромно губљење времена.

Оно што ми је требало три дана могло се урадити за неколико минута да сам знао праве технике.

У овом водичу ћу вам показати различите начине да овај процес учините супер брзим и супер лаким (са и без ВБА).

Ограничења метода приказаних у овом водичу: Помоћу доле приказаних техника моћи ћете да добијете само називе датотека унутар главне фасцикле. Нећете добити имена датотека у потфасциклама унутар главне фасцикле. Ево начина да добијете имена датотека из фасцикли и потфасцикли помоћу програма Повер Куери

Коришћење функције ФИЛЕС за добијање листе имена датотека из фасцикле

Чуо за Функција ФИЛЕС пре него што?

Не брините ако нисте.

То је из детињства Екцел таблица (формула верзије 4).

Иако ова формула не ради у ћелијама радног листа, још увек ради у именованим опсезима. Ову чињеницу ћемо користити за добијање листе имена датотека из одређене фасцикле.

Претпоставимо да сада имате фасциклу са именом - „Тестна фасцикла„На радној површини и желите да добијете листу имена датотека за све датотеке у овој фасцикли.

Ево корака који ће вам дати имена датотека из ове фасцикле:

1. У ћелију А1 унесите потпуну адресу фасцикле иза које следи знак звездице (*)
   • На пример, ако је ваша фасцикла на Ц диску, онда би адреса изгледала овако
     Ц: \ Усерс \ Сумит \ Десктоп \ Тест Фолдер \*
   • Ако нисте сигурни како да добијете адресу фасцикле, користите следећи метод:
     • • У фасцикли из које желите да добијете називе датотека, или креирајте нову Екцел радну свеску или отворите постојећу радну свеску у фасцикли и користите формулу испод у било којој ћелији. Ова формула ће вам дати адресу директоријума и на крају додати знак звездице (*). Сада можете копирати-залепити (залепити као вредност) ову адресу у било коју ћелију (А1 у овом примеру) у радној свесци у којој желите имена датотека.

         = ЗАМЕНИ (ЦЕЛЛ ("име датотеке"), ФИНД ("[", ЦЕЛЛ ("име датотеке")), ЛЕН (ЦЕЛЛ ("име датотеке")), "*")

         [Ако сте креирали нову радну свеску у фасцикли да бисте користили горњу формулу и добили адресу фасцикле, можда ћете желети да је избришете тако да се не налази на листи датотека у тој фасцикли]
2. Идите на картицу „Формуле“ и кликните на опцију „Дефиниши име“.
3. У дијалогу Ново име користите следеће детаље
   • Име: ФилеНамеЛист (слободно изаберите било које име које желите)
   • Опсег: Радна свеска
   • Односи се на: = ФИЛЕС (Лист1! $ А $ 1)
4. Сада да бисмо добили списак датотека, користићемо именовани опсег унутар функције ИНДЕКС. Идите у ћелију А3 (или било коју ћелију у којој желите да почне листа имена) и унесите следећу формулу:

   = ИФЕРРОР (ИНДЕКС (ФилеНамеЛист, РОВ ()-2), ""))

5. Повуците ово надоле и добићете листу свих назива датотека у фасцикли

Желите да издвојите датотеке са одређеним наставком ??

Ако желите да добијете све датотеке са одређеним наставком, само промените звездицу са тим наставком датотеке. На пример, ако желите само Екцел датотеке, можете користити * клс * уместо *

Дакле, адреса фасцикле коју требате користити би била Ц: \ Усерс \ Сумит \ Десктоп \ Тест Фолдер \*клс*

Слично, за датотеке Ворд докумената користите *доц *

Како ово ради?

Формула ФИЛЕС преузима имена свих датотека наведене екстензије у наведеној фасцикли.

У формули ИНДЕКС смо дали називе датотека као низ и враћамо 1., 2., 3. назив датотеке и тако даље помоћу функције РОВ.

Имајте на уму да сам користио РОВ ()-2, како смо кренули од трећег реда па надаље. Дакле, РОВ ()-2 би било 1 за прву инстанцу, 2 за другу инстанцу када је број реда 4, и тако даље и тако даље.

Гледајте видео - Преузмите листу имена датотека из фасцикле у програму Екцел

Коришћење ВБА Набавите списак свих имена датотека из фасцикле

Сада морам рећи да је горња метода помало сложена (са неколико корака).

Међутим, много је боље него то радити ручно.

Али ако вам је угодно користити ВБА (или ако добро следите тачне кораке које ћу навести у наставку), можете да креирате прилагођену функцију (УДФ) која вам лако може дати имена свих датотека.

Предност коришћења а Усер Д.ефинед Ф.Унцтион (УДФ) је то што функцију можете сачувати у личној радној свесци макроа и лако је поново користити без понављања корака изнова и изнова. Такође можете креирати додатак и делити ову функцију са другима.

Дозволите ми прво да вам дам ВБА код који ће креирати функцију за добијање листе свих имена датотека из фасцикле у Екцелу.

Функција ГетФилеНамес (БиВал ФолдерПатх Као стринг) Ас Вариант Дим Резултат Ас Вариант Дим И Ас Интегер Дим мифиле Као објекта Дим МиФСО Као објекта Дим МојФолдер Ас Објецт Дим МиФилес Као Објецт сет МиФСО = ЦреатеОбјецт ( "Сцриптинг.ФилеСистемОбјецт") сет МојФолдер = МиФСО. ГетФолдер (ФолдерПатх) Постави МиФилес = МиФолдер.Филес Резултат РеДим -а (1 до МиФилес.Цоунт) и = 1 За сваки МиФиле у МиФилес Резултат (и) = МиФиле.Наме и = и + 1 Следећа МиФиле ГетФилеНамес = Крајња функција резултата

Горњи код ће створити функцију ГетФилеНамес која се може користити у радним листовима (баш као и регуларне функције).

Где ставити овај код?

Следите доле наведене кораке да бисте копирали овај код у ВБ Едитор.

• Идите на картицу Девелопер.
• Кликните на дугме Висуал Басиц. Ово ће отворити ВБ Едитор.
• У ВБ Едитор-у кликните десним тастером миша на било који објект радне свеске у којој радите, идите на Уметни и кликните на Модул. Ако не видите Пројецт Екплорер, користите тастерску пречицу Цонтрол + Р (држите контролни тастер и притисните тастер „Р“).
• Двапут кликните на објект Модуле и копирајте и залепите горњи код у прозор кода модула.

Како користити ову функцију?

Испод су кораци за коришћење ове функције на радном листу:

• У било коју ћелију унесите адресу фасцикле из које желите да наведете имена датотека.
• У ћелију у којој желите листу унесите следећу формулу (уносим је у ћелију А3):

  = ИФЕРРОР (ИНДЕКС (ГетФилеНамес ($ А $ 1), РОВ ()-2), ""))

• Копирајте и залепите формулу у ћелије испод да бисте добили списак свих датотека.

Имајте на уму да сам унео локацију фасцикле у ћелију, а затим је користио у ћелији ГетФилеНамес формула. Такође можете тешко кодирати адресу фолдера у формули као што је приказано испод:

= ИФЕРРОР (ИНДЕКС (ГетФилеНамес ("Ц: \ Усерс \ Сумит \ Десктоп \ Тест Фолдер"), РОВ ()-2), ""))

У горњој формули смо користили РОВ ()-2 и кренули смо од трећег реда па надаље. Ово је осигурало да док копирам формулу у доње ћелије, она ће се повећати за 1. У случају да формулу уносите у први ред колоне, можете једноставно користити РОВ ().

Како функционише ова формула?

Формула ГетФилеНамес враћа низ који садржи имена свих датотека у фасцикли.

Функција ИНДЕКС се користи за исписивање једног назива датотеке по ћелији, почевши од прве.

ИФЕРРОР функција се користи за враћање празног уместо #РЕФ! грешка која се приказује када се формула копира у ћелију, али нема више имена датотека за попис.

Коришћење ВБА Набавите списак свих имена датотека са одређеним наставком

Горња формула одлично функционише када желите да добијете листу свих имена датотека из фасцикле у програму Екцел.

Али шта ако желите да добијете имена само видео датотека, или само Екцел датотека, или само имена датотека које садрже одређену кључну реч.

У том случају можете користити мало другачију функцију.

Испод је код који ће вам омогућити да добијете сва имена датотека са одређеном кључном речи (или са одређеном екстензијом).

Функција ГетФилеНамесбиЕкт (БиВал ФолдерПатх Ас Стринг, ФилеЕкт Ас Стринг) Као Вариант Дим Резултат Као Вариант Дим и Ас Интегер Дим МиФиле Као Објекат Дим МиФСО Као Објекат Дим МиФолдер Као Објекат Дим МиФилес Ас Објецт Сет МиФСО = ЦреатеОбјецт ("Сцриптинг.ФилеСистемОбјецт" МиФолдер = МиФСО.ГетФолдер (ФолдерПатх) Постави МиФилес = МиФолдер.Филес Резултат поновног димирања (1 до МиФилес.Цоунт) и = 1 За сваки МиФиле у МиФилес Иф ИнСтр (1, МиФиле.Наме, ФилеЕкт) 0 Затим Ресулт (и) = МиФиле .Назив и = и + 1 Крај ако је следећи МиФиле РеДим очување резултата (1 За и - 1) ГетФилеНамесбиЕкт = Функција завршетка резултата

Горњи код ће створити функцију 'ГетФилеНамесбиЕкт‘Које се могу користити у радним листовима (баш као и редовне функције).

Ова функција има два аргумента - локацију фасцикле и кључну реч проширења. Враћа низ назива датотека који одговарају датој екстензији. Ако није наведена екстензија или кључна реч, вратиће се сви називи датотека у наведеној фасцикли.

Синтакса: = ГетФилеНамесбиЕкт („Локација фасцикле“, „Екстензија“)

Где ставити овај код?

Следите доле наведене кораке да бисте копирали овај код у ВБ Едитор.

• Идите на картицу Девелопер.
• Кликните на дугме Висуал Басиц. Ово ће отворити ВБ Едитор.
• У ВБ Едитор-у кликните десним тастером миша на било који објекат радне свеске у којој радите, идите на Уметни и кликните на Модул. Ако не видите Пројецт Екплорер, користите тастерску пречицу Цонтрол + Р (држите контролни тастер и притисните тастер „Р“).
• Двапут кликните на објект Модуле и копирајте и залепите горњи код у прозор кода модула.

Како користити ову функцију?

Испод су кораци за коришћење ове функције на радном листу:

• У било коју ћелију унесите адресу фасцикле из које желите да наведете имена датотека. Унео сам ово у ћелију А1.
• У ћелију унесите екстензију (или кључну реч) за коју желите све називе датотека. Ово сам унео у ћелију Б1.
• У ћелију у којој желите листу унесите следећу формулу (уносим је у ћелију А3):

  = ИФЕРРОР (ИНДЕКС (ГетФилеНамесбиЕкт ($ А $ 1, $ Б $ 1), РОВ ()-2), ""))

• Копирајте и залепите формулу у ћелије испод да бисте добили списак свих датотека.

А што се тебе тиче? Било који Екцел трикови које користите за олакшавање живота. Волео бих да учим од вас. Поделите то у одељку за коментаре!