Isn't it possible to execute dir function without permission? RRS feed

  • Question

  • Hi world.

    I'm scripting some macros in limited environment and I have to script without permissions of admininstrator.

    Doesn't look like dir function execute well.

    Please give me your idea in case you have an amazing one.

    Thank you.

    Here is the script.


    Sub ProcessFiles()
        Application.DisplayAlerts = False

        Dim Pathname, Filename As String
        Dim wb As Workbook
        Dim LastRow As Integer
        Pathname = "C:\Users\user\Desktop\my_folder\" '
        Filename = Dir(Pathname & "*.xls")
        Do While Filename <> ""
            LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
            Set wb = Workbooks.Open(Pathname & Filename)
            DoWork wb
            wb.Close SaveChanges:=False  
            Filename = Dir()
        Sheets("Sheet1").Range("a" & CStr(LastRow + 1)).PasteSpecial ' 

        Application.DisplayAlerts = True
    End Sub

    Sub DoWork(wb As Workbook)
        Dim raw_sht As Worksheet
        Dim xWs As Worksheet
        Dim LastRow As Integer
        Dim i, j As Integer
        LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
        With wb
            .Sheets("Sheet1").Range("a3:ab" & CStr(LastRow)).Copy
        End With

    End Sub

    Tuesday, March 10, 2020 1:01 AM

All replies

  • Hi Chris,

    Thanks for posting in TechNet forum.

    Since your issue is more related to Excel for developer, we will help you move this case to Excel for developers forum.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Best regards

    Itch Sun

     Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.                

    Tuesday, March 10, 2020 7:37 AM
  • To:  Chris_Lee
    re:  problem

    The copy and paste should be done inside the DoWork sub.
    Make sure you include the workbook name when pasting.


    Some new, some older Excel programs (now free) at MediaFire...
    Download (no ads) from... 

    Tuesday, March 10, 2020 1:25 PM
  • No, I mean dir function doesn't work.

    After work this line 'Filename = Dir(Pathname & "*.xls")', Filename has no result because of administrator's permission. I would like to do but I'm working in secure public institutions.

    Wednesday, March 11, 2020 6:42 AM
  • Alright, I should do that.

    Thank you

    Wednesday, March 11, 2020 6:43 AM