Results 1 to 6 of 6

Thread: Listing excel files in a folder

  1. #1
    Board Regular
    Join Date
    Feb 2005
    Location
    Great Yarmouth. England
    Posts
    321
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Listing excel files in a folder

    Can anybody help a bit I please, I am putting together some code to list the excel files in a folder in column B, open each one in turn copy a value from cell D142 and paste in column C next to the file name.
    What this should achieve is the a filename in column B and the description next to it in column C
    Iím sure there are lots of issues with this code but at the moment I cannot get it to open the file so I can copy the data
    All help is much appreciated.

    Code:
    Dim i As Long
    Dim WB As Workbook
    Dim CurrentSheet As Worksheet
    
    Sub GetDescription()
    
    Set ActWork = ActiveWorkbook
    
    Worksheets("Sheet1").Select
    
    myDir = "W:\Sub-Contract\Test\Cost Sheets"
    myFile = Dir(myDir & Application.PathSeparator, vbDirectory)
    
    Range("B3:B500").ClearContents
        Do While myFile <> ""
                i = i + 1
                    Cells(i, 2).Offset(2, 0) = myFile
                        myFile = Dir
                    Workbooks.Open Filename:=myDir
                Sheets("Cost Sheet").Select
            Range("D142").Copy
        wkbk.Activate 'Select Orginal Workbook
    myFile.Offset(0, 1).Paste 'Paste description onto cost sheets list next to the file name
    Loop
    
    For i = 1 To Range("B65536").End(xlUp).Row
    
    Next
    
    End Sub
    I have managed some code to work just listing the file names but cannot move to the next step and open each one as it is listed. I did originally think of listing the files first then going back and opening each one in turn from the list.

    Code:
    Dim i As Long
    Sub List_CostSheets()
    
    ' this is listing the cost sheets only
    Set ActWork = ActiveWorkbook
    
    Worksheets("Sheet1").Select
    
    'myDir = "W:\1works managers files\Cost sheets"
    myDir = "W:\Sub-Contract\Test\Cost Sheets"
    
    myFile = Dir(myDir & Application.PathSeparator & "*.xlsx", vbDirectory)
    'Clear Data in Column B
        Range("B3:B500").ClearContents
                Do While myFile <> ""
                i = i + 1
            Cells(i, 2).Offset(2, 0) = myFile
        myFile = Dir
    Loop
    
    For i = 1 To Range("B65536").End(xlUp).Row
    
    Next
    
    End Sub
    praemonitus, praemunitus

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,248
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Listing excel files in a folder

    Try
    Code:
    Sub List_CostSheets()
       Dim i As Long
       Dim Ws As Worksheet
       Dim Wbk As Workbook
       
       Set Ws = Worksheets("Sheet1")
       
       'myDir = "W:\1works managers files\Cost sheets"
       mydir = "W:\Sub-Contract\Test\Cost Sheets"
       
       myfile = Dir(mydir & Application.PathSeparator & "*.xlsx", vbDirectory)
       'Clear Data in Column B
       Ws.Range("B3:B500").ClearContents
       i = 3
       Do While myfile <> ""
          Ws.Cells(i, 2) = myfile
          Set Wbk = Workbooks.Open(mydir & "\" & myfile)
          Ws.Cells(i, 3).Value = Wbk.Sheets("Cost Sheet").Range("D142").Value
          Wbk.Close False
          myfile = Dir
          i = i + 1
       Loop
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Feb 2005
    Location
    Great Yarmouth. England
    Posts
    321
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Listing excel files in a folder

    Thanks Fluff that works great
    Some of the excel files have links and I get a message "do I wish to update links"
    How can I open the files as read only and not update links
    praemonitus, praemunitus

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,248
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Listing excel files in a folder

    Try
    Code:
    Set Wbk = Workbooks.Open(mydir & "\" & myfile,False)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Feb 2005
    Location
    Great Yarmouth. England
    Posts
    321
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Listing excel files in a folder

    Brilliant thanks for your help Fluff
    praemonitus, praemunitus

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,248
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Listing excel files in a folder

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •