V tem članku bomo ustvarili makro za kopiranje podatkov iz več delovnih zvezkov v mapi v nov delovni zvezek.
Ustvarili bomo dva makra; en makro bo samo kopiral zapise iz prvega stolpca v nov delovni zvezek, drugi makro pa vanj vse podatke.
Neobdelani podatki za ta primer so evidenca prisotnosti zaposlenih. V mapi TestFolder imamo več Excelovih datotek. Imena datotek Excel predstavljajo določen datum v obliki »ddmmyyyy«.
Vsaka Excelova datoteka vsebuje datum, ID zaposlenega in ime zaposlenih tistih zaposlenih, ki so bili na ta dan prisotni.
Ustvarili smo dva makra; "CopyingSingleColumnData" in "CopyingMultipleColumnData". Makro »CopyingSingleColumnData« bo v nov delovni zvezek kopiral samo zapise iz prvega stolpca vseh datotek v mapi. Makro »CopyingMultipleColumnData« bo kopiral vse podatke iz vseh datotek v mapi v nov delovni zvezek.
Makro »CopyingSingleColumnData« lahko izvedete s klikom na gumb »Kopiranje enega stolpca«. Makro »CopyingMultipleColumnData« lahko izvedete s klikom na gumb »Kopiranje več stolpcev«.
Preden zaženete makro, morate v besedilno polje, v katerem so datoteke Excel, določiti pot do mape.
Ko kliknete gumb »Kopiranje enega stolpca«, bo v opredeljeni mapi ustvarjen nov delovni zvezek »Konsolidirana datoteka«, ki bo vseboval konsolidirane podatke iz prvega stolpca vseh datotek v mapi.
Novi delovni zvezek bo vseboval samo zapise v prvem stolpcu. Ko imamo konsolidirane podatke, lahko s štetjem datuma ugotovimo število zaposlenih, ki so prisotni na določen dan. Štetje določenega datuma bo enako številu zaposlenih, ki so prisotni na ta dan.
Ko kliknete gumb »Kopiranje več stolpcev«, bo v določeni mapi ustvaril nov delovni zvezek »Konsolidirani vsi stolpci«. Ta delovni zvezek bo vseboval konsolidirane podatke iz vseh zapisov vseh datotek v mapi.
Ustvarjeni novi delovni zvezek bo vseboval vse zapise iz vseh datotek v mapi. Ko imamo konsolidirane podatke, imamo vse podatke o prisotnosti na voljo v eni datoteki. Z lahkoto lahko ugotovimo število zaposlenih, ki so bili prisotni na ta dan, in dobili smo tudi imena zaposlenih, ki so bili prisotni na ta dan.
Razlaga kode
List1.TextBox1.Vrednost
Zgornja koda se uporablja za vnos vrednosti v besedilno polje »TextBox1« z lista »Sheet1«.
Dir (FolderPath & "*.xlsx")
Zgornja koda se uporablja za ime datoteke z razširitvijo datoteke ».xlsx«. Za ime datoteke z več znaki smo uporabili nadomestni znak *.
Medtem ko ime datoteke ""
Štetje1 = Štetje1 + 1
ReDim Preserve FileArray (1 do števila 1)
FileArray (Count1) = Ime datoteke
Ime datoteke = Dir ()
Wend
Zgornja koda se uporablja za pridobivanje imen vseh datotek v mapi.
Za i = 1 Za UBound (FileArray)
Naslednji
Zgornja koda se uporablja za pregled vseh datotek v mapi.
Obseg ("A1", celice (LastRow, 1)). Kopiraj DestWB.ActiveSheet.Cells (LastDesRow, 1)
Zgornja koda se uporablja za kopiranje zapisa iz prvega stolpca v ciljni delovni zvezek.
Obseg ("A1", ActiveCell.SpecialCells (xlCellTypeLastCell)). Kopiraj DestWB.ActiveSheet.Cells (LastDesRow, 1)
Zgornja koda se uporablja za kopiranje vseh zapisov iz aktivnega delovnega zvezka v ciljni delovni zvezek.
Za kodo sledite spodaj
Možnost Eksplicitno podkopiranjeSingleColumnData () 'Deklariranje spremenljivk Dim Ime datoteke, FolderPath, FileArray (), Ime datoteke1 Kot niz nizov LastRow, LastDesRow, Count1, i Kot Integer Dim SourceWB, DestWB Kot aplikacija za delovni zvezek.ScreenUpdating = SheetPesek1.' Vstavljanje povratne poševnice v pot mape, če poševnica (\) manjka Če je desno (FolderPath, 1) "\" Potem FolderPath = FolderPath & "\" End If 'Iskanje datotek Excel Ime datoteke = Dir (FolderPath & "*.xlsx") Count1 = 0 'Ponavljanje po vseh datotekah Excel v mapi Medtem ko FileName "" Count1 = Count1 + 1 ReDim Shrani FileArray (1 do Count1) FileArray (Count1) = FileName FileName = Dir () Wend' Ustvarjanje novega nastavka delovnega zvezka DestWB = Workbooks.Add For i = 1 To UBound (FileArray) 'Iskanje zadnje vrstice v delovnem zvezku LastDesRow = DestWB.ActiveSheet.Range ("A1"). SpecialCells (xlCellTypeLastCell). (FolderPath & FileArray (i)) LastRow = ActiveCell.SpecialCells (xlCellTypeLas tCell. ActiveSheet.Cells (LastDesRow, 1) Else Range ("A1", Cells (LastRow, 1)). Copy DestWB.ActiveSheet.Cells (LastDesRow + 1, 1) End If SourceWB.Close False Next 'Shranjevanje in zapiranje novega Excela delovni zvezek DestWB.SaveAs FileName: = FolderPath & "ConsolidatedFile.xlsx" DestWB.Close Set DestWB = Nothing Set SourceWB = Nothing End Sub Sub CopyingMultipleColumnData () 'Razglasitev spremenljivk Dim FileName, FolderPath, FileArrayRast1,, , Count1, i As Integer Dim SourceWB, DestWB As Applicationbook Application.ScreenUpdating = False FolderPath = Sheet1.TextBox1.Value 'Vstavljanje povratne poševnice v pot do mape, če poševnica (\) manjka, če je desno (FolderPath, 1) "\" Potem FolderPath = FolderPath & "\" End If "Iskanje Excelovih datotek FileName = Dir (FolderPath &"*.xlsx ") Count1 = 0 'Preklapljanje po vseh Excelovih datotekah v mapi Medtem ko ime datoteke "" Count1 = Count1 + 1 ReDim Shrani FileArray (1 do Count1) FileArray (Count1) = FileName FileName = Dir () Wend "Ustvarjanje novega delovnega zvezka Set DestWB = Delovni zvezki. Dodaj Za i = 1 Za UBound (FileArray) 'Iskanje zadnje vrstice v delovnem zvezku LastDesRow = DestWB.ActiveSheet.Range ("A1"). SpecialCells (xlCellTypeLastCell) .Row "Odpiranje Excelovega delovnega zvezka Set SourceWB = delovni zvezki. FileArray (i)) 'Lepljenje kopiranih podatkov v zadnjo vrstico v ciljnem delovnem zvezku If LastDesRow = 1 Potem' Kopiranje vseh podatkov na delovnem listu v zadnjo vrstico v obsegu ciljnega delovnega zvezka ("A1", ActiveCell.SpecialCells (xlCellTypeLastCell)). Kopiraj DestWB.ActiveSheet.Cells (LastDesRow, 1) Else Range ("A1", ActiveCell.SpecialCells (xlCellTypeLastCell)). Kopiraj DestWB.ActiveSheet.Cells (LastDesRow + 1, 1) End If SourceWB.Cseving nov Excelov delovni zvezek DestWB.SaveAs FileName: = FolderPath & "ConsolidatedAllColumns.xlsx" DestWB.Zapri D estWB = Nič ni nastavljeno VirWB = Nič ni podrejeno
Če vam je bil ta blog všeč, ga delite s prijatelji na Facebooku. Prav tako nam lahko sledite na Twitterju in Facebooku.
Radi bi slišali od vas, nam sporočite, kako lahko izboljšamo svoje delo in ga izboljšamo za vas. Pišite nam na spletnem mestu e -pošte