powershell excel macro needs or way to use macro for 100s of files

I had a list of servers, I needed to find all the openfiles \ shares being used and export to a .csv or .txt file.

The list of servers is several hundreds.
Code for getting the data, is as follows:

@echo off
setlocal
for /f %%a in ('type servers.txt') do (
	openfiles.exe /query /s %%a /fo csv /NH /v >>"F:\OpenSessionsReport\openfilesonserver_%%a.csv"
)

Open in new window



Now that I have all the information,
I need to filter, and etc via Excel Macro or via powershell or vbscript...


I can create an Excel Macro but doing one per server_openfile.csv / txt would be tedious since there are hundreds.

Example of Macro.
First it imports the openfile.txt
      Delimited
      Start import at row 1   File orgin 437: OEM United States

      Delimiters: TAB, Semicolon, comma, Space

      Then I remove a couple columns
      
      I expand all rows\colums
      I add a column
      A = Server
      B = User
      C=OS
      D=Access
      F=Path
      
      I then highlight row 1 and make it BOLD larger font, colored background. and click all borders
      
      I then freeze first row
      
      Next I Sort with "My data has headers"
      I sort by User and then path
      Next I need to filter for unique records only
      Then remove or filter out the "NO access" under Access Column
      
      Then save as %servername%.xlsx
      
      Below is sample macro. But again, I have too many to create a macro for every server.
      And having all openfiles go to one file, is way to big for excel, and not all data is imported.
      
      The other thing is some files are much larger, and contains more rows then others.

      
      
Sub First Server()
'
' First Server Macro
'
' Keyboard Shortcut: Ctrl+u
'
    Workbooks.OpenText Filename:= _
        "F:\OpenSessionsReport\firstserver_openfiles.txt", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 9), Array( _
        3, 1), Array(4, 1), Array(5, 9), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
    Cells.Select
    Cells.EntireColumn.AutoFit
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "SERVER:"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "USER:"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "OS:"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "ACCESS:"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "OPEN"
    Rows("1:1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.Font.Bold = True
    Cells.Select
    ActiveWorkbook.Worksheets("firstserver_openfiles").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("firstserver_openfiles").Sort.SortFields.Add Key:= _
        Range("E2:E16154"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("XRI00101_openfiles").Sort.SortFields.Add Key:= _
        Range("B2:B16154"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("server_openfiles").Sort
        .SetRange Range("A1:I16154")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1:I16154").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    ActiveWindow.SmallScroll Down:=90
    ActiveWindow.LargeScroll Down:=19
    ActiveWindow.ScrollRow = 7484
    ActiveWindow.ScrollRow = 7375
    ActiveWindow.ScrollRow = 7339
    ActiveWindow.ScrollRow = 7266
    ActiveWindow.ScrollRow = 7230
    ActiveWindow.ScrollRow = 7121
    ActiveWindow.ScrollRow = 7048
    ActiveWindow.ScrollRow = 7012
    ActiveWindow.ScrollRow = 6939
    ActiveWindow.ScrollRow = 6867
    ActiveWindow.ScrollRow = 6758
    ActiveWindow.ScrollRow = 6649
    ActiveWindow.ScrollRow = 6503
    ActiveWindow.ScrollRow = 6358
    ActiveWindow.ScrollRow = 6140
    ActiveWindow.ScrollRow = 5886
    ActiveWindow.ScrollRow = 5595
    ActiveWindow.ScrollRow = 5341
    ActiveWindow.ScrollRow = 5050
    ActiveWindow.ScrollRow = 4796
    ActiveWindow.ScrollRow = 4505
    ActiveWindow.ScrollRow = 4251
    ActiveWindow.ScrollRow = 4033
    ActiveWindow.ScrollRow = 3779
    ActiveWindow.ScrollRow = 3488
    ActiveWindow.ScrollRow = 3234
    ActiveWindow.ScrollRow = 2980
    ActiveWindow.ScrollRow = 2689
    ActiveWindow.ScrollRow = 2326
    ActiveWindow.ScrollRow = 2035
    ActiveWindow.ScrollRow = 1745
    ActiveWindow.ScrollRow = 1527
    ActiveWindow.ScrollRow = 1381
    ActiveWindow.ScrollRow = 1200
    ActiveWindow.ScrollRow = 1127
    ActiveWindow.ScrollRow = 1054
    ActiveWindow.ScrollRow = 1018
    ActiveWindow.ScrollRow = 982
    ActiveWindow.ScrollRow = 945
    ActiveWindow.ScrollRow = 909
    ActiveWindow.ScrollRow = 836
    ActiveWindow.ScrollRow = 800
    ActiveWindow.ScrollRow = 764
    ActiveWindow.ScrollRow = 728
    ActiveWindow.ScrollRow = 655
    ActiveWindow.ScrollRow = 619
    ActiveWindow.ScrollRow = 582
    ActiveWindow.ScrollRow = 510
    ActiveWindow.ScrollRow = 473
    ActiveWindow.ScrollRow = 437
    ActiveWindow.ScrollRow = 401
    ActiveWindow.ScrollRow = 364
    ActiveWindow.ScrollRow = 292
    ActiveWindow.ScrollRow = 255
    ActiveWindow.ScrollRow = 183
    ActiveWindow.ScrollRow = 146
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 1
    Cells.EntireColumn.AutoFit
    ActiveWorkbook.SaveAs Filename:="F:\OpenSessionsReport\firstserver.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
End Sub

Open in new window

LVL 5
IndyrbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Is having
(a) an Excel file
(b) one file per server
a requirement?
Robberbaron (robr)Commented:
dim filen as string

filen = dir$(F:\OpenSessionsReport\openfilesonserver_*.txt)

do while filen<>""
   'process the text file filen as per your code.
   filen = dir$
loop

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IndyrbAuthor Commented:
where do I put in the macro? under do while filen<>""

and will this open all .txt files and do the macro?
so if one is server1_openfiles.txt and next is server2 and so on and so forth.

And is this a vbscript or batch file?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
It's neither. It is VBA code, and needs to run inside of Excel.

Your own code (opening the file, doing all stuff, ...) would replace line 6.

But I cannot recommend to use the recorded macro as-is. It needs severe cleanup (but that is common if you record a macro), to remove the dangerous or superfluous stuff. E.g. remove all ActiveWindow.*Scroll* calls. And shorten code like
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "SERVER:"

Open in new window

to
    Range("A1").FormulaR1C1 = "SERVER:"

Open in new window

at least where there is only a single reference to a selection.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.