Page 84 | Some Notes on the Machines

Import Multiple CSVs to Tabs in One Excel Spreadsheet
article #763, updated 3796 days ago

From the excellent Jerry Beaucaire; VBA, for use inside an Excel module.

https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/merge-functions/csvs-to-sheets

Sub ImportCSVs()
'Author:    Jerry Beaucaire
'Date:      8/16/2010
'Summary:   Import all CSV files from a folder into separate sheets
'           named for the CSV filenames
'
'Update:    2/8/2013   Macro replaces existing sheets if they already exist in master workbook
'Modded:    12/10/14   Minor edits (Jonathan Brickman)

Dim fPath       As String
Dim fCSV        As String
Dim SheetName   As String
Dim wbCSV       As Workbook
Dim wbMST       As Workbook

Set wbMST = ThisWorkbook
fPath = "X:\PATH-TO-CSVs\"       'path to CSV files, include the final \
Application.ScreenUpdating = False  'speed up macro
Application.DisplayAlerts = False   'no error messages, take default answers
fCSV = Dir(fPath & "*.csv")         'start the CSV file listing

    On Error Resume Next
    Do While Len(fCSV) > 0
        SheetName = Left(fCSV, Len(fCSV) - 4)   ' Trim .CSV from filename, make sheet name
        Set wbCSV = Workbooks.Open(fPath & fCSV)                    'open a CSV file
        wbMST.Sheets(ActiveSheet.Name).Delete                       'avoid replication, delete sheet
        ActiveSheet.Move After:=wbMST.Sheets(wbMST.Sheets.Count)    'move new sheet into Mstr
        ActiveSheet.Name = SheetName
        Columns.AutoFit             		'clean up display
        fCSV = Dir                  		'ready next CSV
    Loop

Application.ScreenUpdating = True
Set wbCSV = Nothing
End Sub

Categories: Microsoft Office

Export all Active Directory groups to CSV
article #762, updated 3798 days ago

From the amazing Farhan Kazi:

http://fkazi.blogspot.com/2013/07/export-active-directory-groups-with.html

@ECHO OFF
SETLOCAL EnableDelayedExpansion

SET FileName=Report.csv
SET AG=0
SET EG=0
SET CT=0
SET NE=0
SET GN=

FOR /F %%T IN ('DSQuery * -Filter "(&(objectClass=Group))" -Limit 0') DO SET /a AG+=1 >NUL
FOR /F %%T IN ('DSQuery * -Filter "(&(objectClass=Group)(^!member=*))" -Limit 0') DO SET /a EG+=1 >NUL
SET /a NE=!AG!-!EG!

ECHO Total Groups in Active Directory %AG% out of them %EG% are empty.&&ECHO.
ECHO Group,Members>"!FileName!"
TITLE Exporting !NE! Non-Empty AD Groups.

FOR /F "delims=" %%G IN ('DSQuery * -Filter "(&(objectClass=Group)(member=*))" -Limit 0') DO (
    FOR /F "delims=" %%v IN ('DSQuery * %%G -l -q -Attr Name -Limit 0') DO SET GN=%%v
    SET /a CT+=1 >NUL
    ECHO !CT!. Exporting: !GN!
    FOR /F "delims=" %%M IN ('DSGET Group %%G -Members') DO (
        FOR /F "delims=" %%U IN ('DSQuery * %%M -l -q -Attr displayName') DO (
        ECHO !GN!,%%U>>"!FileName!")))

TITLE Export complete.
ECHO.&&ECHO Export complete, please check '!FileName!' file.
EXIT /B 0

Here’s a mod, which creates one file per group:

REM
REM Export all Active Directory groups to CSV files
REM One CSV file per group
REM
REM Original written by Farhan Kazi
REM http://fkazi.blogspot.com/2013/07/export-active-directory-groups-with.html
REM
REM Modded for one file per group by Jonathan Brickman
REM http://n.ponderworthy.com
REM

@ECHO OFF
SETLOCAL EnableDelayedExpansion

SET AG=0
SET EG=0
SET CT=0
SET NE=0
SET GN=

FOR /F %%T IN ('DSQuery * -Filter "(&(objectClass=Group))" -Limit 0') DO SET /a AG+=1 >NUL
FOR /F %%T IN ('DSQuery * -Filter "(&(objectClass=Group)(^!member=*))" -Limit 0') DO SET /a EG+=1 >NUL
SET /a NE=!AG!-!EG!

ECHO Total Groups in Active Directory %AG% out of them %EG% are empty.&&ECHO.
TITLE Exporting !NE! Non-Empty AD Groups.

FOR /F "delims=" %%G IN ('DSQuery * -Filter "(&(objectClass=Group)(member=*))" -Limit 0') DO (
    FOR /F "delims=" %%v IN ('DSQuery * %%G -l -q -Attr Name -Limit 0') DO SET GN=%%v
    SET /a CT+=1 >NUL
    ECHO !CT!. Exporting: !GN!
    FOR /F "delims=" %%M IN ('DSGET Group %%G -Members') DO (
        FOR /F "delims=" %%U IN ('DSQuery * %%M -l -q -Attr displayName') DO (
        ECHO %%U>>"!GN!".CSV)))

TITLE Export complete.
ECHO.&&ECHO Export complete.
EXIT /B 0

Categories: Active Directory