none
Reading files in a folder RRS feed

  • Question

  • Hi

    I have a folder that will contain a variable number of files 9all in the same format).

    I am trying to write some VBA code to access each file, open it, process it , close it ; then for on to next file and do the same. 

    I am guessing there must be some code like:

    For each workbook in folder

      open...

      process..

      close...

    next workbook  

    but I  am struggling trying to fined the correct syntax.

    Can anyone help please?

    many thanks

    Peter

    Wednesday, September 28, 2016 2:09 PM

Answers

  • when you say process it- what do you mean?

    also what format are the files in?

    D

    I use the below to import all txt files from a particular folder that have a common start of name


     Dim nxt_row As Long
    Const strPath As String = "C:\" 'where you files are
        Dim strExtension As String
       ChDir strPath

        strExtension = Dir(strPath & "first part of name*.txt")

        Do While strExtension <> ""

             With Sheets("sheet1") ' destination sheet
             nxt_row = .Cells(.Rows.Count, "A").End(xlUp).row + 1
             End With


            Sheets("sheet1").Visible = True

    Sheets("sheet1").Select
            With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;" & strPath & strExtension, Destination:=Range("$A$" & nxt_row))
                .Name = strExtension
                .FieldNames = False
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = False
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 850
                .TextFileStartRow = 2
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = True
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "="
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With

            strExtension = Dir
        Loop

    • Proposed as answer by Chenchen LiModerator Thursday, September 29, 2016 4:27 AM
    • Marked as answer by py1 Thursday, September 29, 2016 7:42 AM
    Wednesday, September 28, 2016 3:44 PM
  • Peter,
    Re:  open files in folder and process

    My version...
    'Opens each .xls file in the specified folder and provides opportunity to do stuff.
    '---
    Sub FilesToWorksheets_R4()
    'Jim Cone - Portland Oregon - May 2008.
     On Error GoTo ThatHurt
     Dim objFSO    As Object
     Dim objFolder As Object
     Dim objFile   As Object
     Dim strPath   As String
     Dim strName   As String
    'Dim blnTask   As Boolean 'optional

     If Val(Application.Version) >= 10 Then
    'blnTask = Application.ShowWindowsInTaskbar
    'Application.ShowWindowsInTaskbar = False
     End If
     Application.ScreenUpdating = False
    'Specify the folder...
     strPath = "C:\Program Files\Army\Provisions"        '<<<<<< MODIFY

    'Use Microsoft Scripting runtime.
     Set objFSO = CreateObject("Scripting.FileSystemObject")
     If objFSO.FolderExists(strPath) Then
     Set objFolder = objFSO.GetFolder(strPath)
    'Check type of file in the folder and open file.
     For Each objFile In objFolder.Files
       If objFile.Name Like "*.xls" Then
         strName = objFile.Name
         Application.StatusBar = strName
         Workbooks.Open objFile
         With Workbooks(strName).Worksheets(1)

         'process stuff

         End With
         Workbooks(strName).Close savechanges:=True
       End If
     Next 'objFile
     Else
     MsgBox "Cannot find folder.  "
     End If
    CloseOut:
     On Error Resume Next
    'Application.ShowWindowsInTaskbar = blnTask
     Application.StatusBar = False
     Application.ScreenUpdating = True
     Set objFSO = Nothing
     Set objFolder = Nothing
     Set objFile = Nothing
     Exit Sub

    ThatHurt:
     Beep
     MsgBox "Error " & Err.Number & "  " & Err.Description, , "Files To Worksheet"
     Resume CloseOut
    End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0
    (free & commercial excel programs)


    • Edited by James Cone Thursday, September 29, 2016 3:08 AM
    • Proposed as answer by Chenchen LiModerator Thursday, September 29, 2016 4:26 AM
    • Marked as answer by py1 Thursday, September 29, 2016 7:42 AM
    Thursday, September 29, 2016 3:07 AM

All replies

  • when you say process it- what do you mean?

    also what format are the files in?

    D

    I use the below to import all txt files from a particular folder that have a common start of name


     Dim nxt_row As Long
    Const strPath As String = "C:\" 'where you files are
        Dim strExtension As String
       ChDir strPath

        strExtension = Dir(strPath & "first part of name*.txt")

        Do While strExtension <> ""

             With Sheets("sheet1") ' destination sheet
             nxt_row = .Cells(.Rows.Count, "A").End(xlUp).row + 1
             End With


            Sheets("sheet1").Visible = True

    Sheets("sheet1").Select
            With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;" & strPath & strExtension, Destination:=Range("$A$" & nxt_row))
                .Name = strExtension
                .FieldNames = False
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = False
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 850
                .TextFileStartRow = 2
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = True
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "="
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
            End With

            strExtension = Dir
        Loop

    • Proposed as answer by Chenchen LiModerator Thursday, September 29, 2016 4:27 AM
    • Marked as answer by py1 Thursday, September 29, 2016 7:42 AM
    Wednesday, September 28, 2016 3:44 PM
  • Peter,
    Re:  open files in folder and process

    My version...
    'Opens each .xls file in the specified folder and provides opportunity to do stuff.
    '---
    Sub FilesToWorksheets_R4()
    'Jim Cone - Portland Oregon - May 2008.
     On Error GoTo ThatHurt
     Dim objFSO    As Object
     Dim objFolder As Object
     Dim objFile   As Object
     Dim strPath   As String
     Dim strName   As String
    'Dim blnTask   As Boolean 'optional

     If Val(Application.Version) >= 10 Then
    'blnTask = Application.ShowWindowsInTaskbar
    'Application.ShowWindowsInTaskbar = False
     End If
     Application.ScreenUpdating = False
    'Specify the folder...
     strPath = "C:\Program Files\Army\Provisions"        '<<<<<< MODIFY

    'Use Microsoft Scripting runtime.
     Set objFSO = CreateObject("Scripting.FileSystemObject")
     If objFSO.FolderExists(strPath) Then
     Set objFolder = objFSO.GetFolder(strPath)
    'Check type of file in the folder and open file.
     For Each objFile In objFolder.Files
       If objFile.Name Like "*.xls" Then
         strName = objFile.Name
         Application.StatusBar = strName
         Workbooks.Open objFile
         With Workbooks(strName).Worksheets(1)

         'process stuff

         End With
         Workbooks(strName).Close savechanges:=True
       End If
     Next 'objFile
     Else
     MsgBox "Cannot find folder.  "
     End If
    CloseOut:
     On Error Resume Next
    'Application.ShowWindowsInTaskbar = blnTask
     Application.StatusBar = False
     Application.ScreenUpdating = True
     Set objFSO = Nothing
     Set objFolder = Nothing
     Set objFile = Nothing
     Exit Sub

    ThatHurt:
     Beep
     MsgBox "Error " & Err.Number & "  " & Err.Description, , "Files To Worksheet"
     Resume CloseOut
    End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0
    (free & commercial excel programs)


    • Edited by James Cone Thursday, September 29, 2016 3:08 AM
    • Proposed as answer by Chenchen LiModerator Thursday, September 29, 2016 4:26 AM
    • Marked as answer by py1 Thursday, September 29, 2016 7:42 AM
    Thursday, September 29, 2016 3:07 AM
  • many thanks Jim - I appreciate the response.
    Thursday, September 29, 2016 7:42 AM