Ինչպես համախմբել բազմաթիվ Excel աշխատանքային գրքեր VBA-ի միջոցով

Եթե ցանկանում եք միավորել Excel-ի մի քանի աշխատանքային գրքույկներ նվազագույն ջանքերով, ապա ճիշտ տեղում եք եկել:

Տվյալների տարբեր աղբյուրների հետ աշխատելիս, դուք կարող եք հաճախ դժվարանալ մի քանի աշխատանքային գրքույկներ և աշխատաթերթեր կազմելու համար, նախքան մեկ վերջնական տվյալների բաժին հասնելը: Պատկերացրեք մի իրավիճակ, երբ դուք ունեք մի քանի հարյուր աշխատանքային գրքույկ, որոնք պետք է համատեղել նախքան ձեր օրը սկսելը:

Ոչ ոք չի ցանկանում անվերջ ժամեր ծախսել՝ աշխատելով տարբեր աղբյուրների վրա՝ բացելով յուրաքանչյուր աշխատանքային գրքույկ, պատճենելով և կպցնելով տվյալները տարբեր թերթերից՝ մինչև վերջապես մեկ համախմբված աշխատանքային գրքույկ պատրաստելը: Ի՞նչ անել, եթե VBA մակրոն կարող է դա անել ձեզ համար:

Այս ուղեցույցով դուք կարող եք ստեղծել ձեր սեփական Excel VBA մակրո կոդը՝ մի քանի աշխատանքային գրքույկներ համախմբելու համար, այս ամենը հաշված րոպեների ընթացքում (եթե տվյալների ֆայլերը շատ են):

Ձեր սեփական VBA մակրո կոդը ստեղծելու նախադրյալները

Ձեզ անհրաժեշտ է մեկ աշխատանքային գիրք՝ VBA կոդը տեղադրելու համար, մինչդեռ աղբյուրի տվյալների մնացած աշխատանքային գրքերն առանձին են: Բացի այդ, ստեղծեք մեկ աշխատանքային գիրք Համախմբված՝ ձեր բոլոր աշխատանքային գրքերի համախմբված տվյալները պահելու համար:

Ստեղծեք թղթապանակ Համախմբումձեր նախընտրած վայրում՝ ձեր բոլոր սկզբնաղբյուր աշխատանքային գրքերը պահելու համար: Երբ մակրոն գործարկվում է, այն անցնում է այս թղթապանակում պահվող յուրաքանչյուր աշխատանքային գրքում, պատճենում է բովանդակությունը տարբեր թերթերից և տեղադրում Համախմբված աշխատանքային գրքում:

Ստեղծելով ձեր սեփական Excel VBA կոդը

Երբ նախադրյալները դուրս են գալիս ճանապարհից, ժամանակն է խորամուխ լինել ծածկագրի մեջ և սկսել կոտրել հիմունքները՝ այն հարմարեցնելու ձեր պահանջներին:

Սեղմեք Alt+F11 ստեղնը Excel-ում՝ VBA մակրո կոդի խմբագրիչը բացելու համար: Տեղադրեք ստորև գրված կոդը և պահեք ֆայլը որպես մակրո միացված աշխատանքային գրքույկ (.xlsm ընդլայնում):

 Sub openfiles()'declare the variables used within the VBA codeDim MyFolder As String, MyFile As String, wbmain As Workbook, lastrow As Long'disable these functions to enhance code processingWith Application.DisplayAlerts = False.ScreenUpdating = FalseEnd With'change the path of the folder where your files are going to be savedMyFolder = InputBox("Enter path of the Consolidation folder") & "\"'define the reference of the folder in a macro variableMyFile = Dir(MyFolder)'open a loop to cycle through each individual workbook stored in the folderDo While Len(MyFile) > 0'activate the Consolidation workbookWindows("Consolidation").Activate'calculate the last populated rowRange("a1048576").SelectSelection.End(xlUp).SelectActiveCell.Offset(1, 0).Select'open the first workbook within the Consolidation folderWorkbooks.Open Filename:=MyFolder & MyFileWindows(MyFile).Activate'toggle through each sheet within the workbooks to copy the dataDim ws As WorksheetFor Each ws In Sheets ws.Activate ws.AutoFilterMode = False 'ignore the header and copy the data from row 2 If Cells(2, 1) = "" Then GoTo 1 GoTo 101: Next10: Range("a2:az20000").CopyWindows("Consolidation").Activate'paste the copied contentsActiveSheet.PasteWindows(MyFile).Activate'close the open workbook once the data is pastedActiveWorkbook.Close'empty the cache to store the value of the next workbookMyFile = Dir()'open the next file in the folderLoop'enable the disabled functions for future useWith Application.DisplayAlerts = True.ScreenUpdating = TrueEnd WithEnd Sub

VBA ծածկագիրը բացատրված է

Կոդի առաջին մասը սահմանում է ենթածրագր, որը պարունակում է ձեր ամբողջ VBA կոդը: Սահմանեք ենթածրագրը sub-ով, որին հաջորդում է կոդի անվանումը: Ենթա անունը կարող է լինել ցանկացած; Իդեալում, դուք պետք է պահպանեք անունը համապատասխան այն կոդը, որը պատրաստվում եք գրել:

Excel VBA-ն հասկանում է օգտագործողի կողմից ստեղծված փոփոխականները և դրանց համապատասխան տվյալների տեսակները, որոնք հայտարարված են մութ (չափով):

Ձեր կոդի մշակման արագությունը բարձրացնելու համար կարող եք անջատել էկրանի թարմացումը և չեղարկել բոլոր ահազանգերը, քանի որ դա դանդաղեցնում է կոդի կատարումը:

Օգտագործողին կառաջարկվի նշել այն թղթապանակի ուղին, որտեղ պահվում են տվյալների ֆայլերը: Ստեղծվում է հանգույց՝ թղթապանակում պահվող յուրաքանչյուր աշխատանքային գրքույկ բացելու, յուրաքանչյուր թերթի տվյալները պատճենելու և Համախմբում աշխատանքային գրքում ավելացնելու համար:

Համախմբման աշխատանքային գիրքը ակտիվացված է, որպեսզի Excel VBA-ն կարողանա հաշվարկել վերջին բնակեցված տողը: Ընտրված է աշխատանքային թերթի վերջին բջիջը, իսկ վերջին տողը հաշվարկվում է աշխատանքային գրքում՝ օգտագործելով օֆսեթ ֆունկցիան: Սա շատ օգտակար է, երբ մակրոն սկսում է տվյալների կցումը աղբյուրի ֆայլերից:

Երբ հանգույցը բացում է առաջին աղբյուրի ֆայլը, զտիչները հանվում են յուրաքանչյուր թերթիկից (եթե դրանք կան), և A2-ից մինչև AZ20000 տատանվող տվյալները պատճենվելու և տեղադրվելու են Consolidation աշխատանքային գրքում:

Գործընթացը կրկնվում է այնքան ժամանակ, մինչև աշխատանքային գրքույկի բոլոր թերթերը կցվեն հիմնական աշխատանքային գրքում:

Ի վերջո, աղբյուրի ֆայլը փակվում է, երբ բոլոր տվյալները տեղադրվեն: Հաջորդ աշխատանքային գրքույկը բացվում է, որպեսզի VBA մակրոները կարողանա կրկնել նույն քայլերը ֆայլերի հաջորդ հավաքածուի համար:

Օղակը կոդավորված է այնպես, որ գործարկվի այնքան ժամանակ, մինչև բոլոր ֆայլերը ավտոմատ կերպով թարմացվեն հիմնական աշխատանքային գրքում:

Օգտագործողի վրա հիմնված անհատականացումներ

Երբեմն, դուք չեք ցանկանում անհանգստանալ ներկառուցված հուշումներից, հատկապես, եթե դուք վերջնական օգտագործողն եք: Եթե նախընտրում եք կոշտ ծածկագրել կոդի Համախմբման թղթապանակի ուղին, կարող եք փոխել կոդի այս մասը.

 MyFolder = InputBox("Enter path of the Consolidation folder") & "\"

Դեպի՝

 MyFolder = “Folder path” & "\"

Բացի այդ, դուք կարող եք նաև փոխել սյունակների հղումները, քանի որ քայլը ներառված չէ այս ծածկագրում: Պարզապես փոխարինեք վերջի սյունակի հղումը ձեր վերջին բնակեցված սյունակի արժեքով (այս դեպքում՝ AZ): Դուք պետք է հիշեք, որ վերջին բնակեցված տողը հաշվարկվում է մակրո կոդի միջոցով, այնպես որ դուք պետք է փոխեք միայն սյունակի հղումը:

Այս մակրոյից առավելագույն օգուտ քաղելու համար կարող եք այն օգտագործել միայն նույն ձևաչափով աշխատանքային գրքերը համախմբելու համար: Եթե կառուցվածքները տարբեր են, դուք չեք կարող օգտագործել այս VBA մակրոները:

Մի քանի աշխատանքային գրքույկների համախմբում Excel VBA մակրոյով

Excel VBA կոդի ստեղծումը և փոփոխումը համեմատաբար հեշտ է, հատկապես, եթե հասկանում եք կոդի որոշ նրբերանգներ: VBA-ն համակարգված կերպով անցնում է յուրաքանչյուր կոդային տողով և կատարում այն տող առ տող:

Եթե կոդի մեջ որևէ փոփոխություն կատարեք, պետք է համոզվեք, որ չփոխեք կոդերի հերթականությունը, քանի որ դա կխաթարի կոդի կատարումը: