Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jun 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro that opens each .xls file data out macro (Excel)

    Hello,

    I have be using a macro someone posted on this site and having difficulty making it do a slightly different procedure

    For Each sfl In fld
    i = i + 1
    wsh.Range("A" & i) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    If strFile = "" Then
    wsh.Range("C" & i) = "No workbook found"
    Else

    this code sniplet seems to break each time becaus sfl is empty.

    attached it the whole code I copied,

    What I want to do is in a specified folder open each .xls file and run my stripping process i dont need to move in out of a folder,
    the folder I specify in the code it the oly folder

    What do I need to change to make the code loop through only the .xls file in my specified folder?

    thank you,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that opens each .xls file data out macro (Excel)

    The line

    For Each sfl In fld

    should probably be

    For Each sfl In fld.SubFolders

    I don't understand the rest of your question.

  3. #3
    New Lounger
    Join Date
    Jun 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that opens each .xls file data out macro (Excel)

    What I am trying to do it
    1. goto a directory path c:My Excel files
    2.have the macro open each excel file (temp excel workbook) and grap certain ranges and past them in my main workbook, close the temp excel workbook and open the next excel workbook in the path and do the same procedure.

    It seems that the code will not reconize For Each sfl In fld.SubFolders
    because
    sfl = ""

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that opens each .xls file data out macro (Excel)

    If you don't want to loop through subfolders, you don't need

    For Each sfl In whatever

    If you want to inspect Excel workbooks in a fixed folder, you don't need to use FileSystemObject, you can simply use Dir to loop through all workbooks in a folder.

  5. #5
    New Lounger
    Join Date
    Jun 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that opens each .xls file data out macro (Excel)

    Thanks for your help

  6. #6
    New Lounger
    Join Date
    Jun 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that opens each .xls file data out macro (Excel)

    Hans,

    One question,

    I have the loop working but I m not sure how a icam move on the next excel file, it currently keeps opening the same file
    my code is below


    Sub extracter()


    Dim strPath As String
    Dim strFile As String
    Dim i As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim oSh As Worksheet
    Dim oSh2 As Worksheet
    Dim Loopcount As Integer
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False


    Set wsh = ThisWorkbook.Worksheets(1)
    wsh.Range("A:AV").Clear
    strPath = wsh.Range("BW1")
    Loopcount = 1
    Do Until Loopcount = 26


    i = i + 1
    strFile = Dir(strPath & "*.xls")
    If strFile = "" Then
    wsh.Range("C" & i) = "No workbook found"
    Else

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro that opens each .xls file data out macro (Excel)

    Your loop should look like this:
    <code>
    ' Set strFile to the first file in the folder
    strFile = Dir(strPath & "*.xls")
    ' Loop while there is a file available
    Do While Not strFile = ""
    ' Do something with the filename here, for example
    i = i + 1
    wsh.Range("C" & i) = strFile
    ' And move on to the next file
    strFile = Dir
    Loop</code>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that opens each .xls file data out macro (Excel)

    Try something like this:

    <code>
    Sub extracter()
    Dim strPath As String
    Dim strFile As String
    Dim i As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim oSh As Worksheet
    Dim oSh2 As Worksheet
    ' On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    Set wsh = ThisWorkbook.Worksheets(1)
    wsh.Range("A:AV").Clear
    strPath = wsh.Range("BW1")
    strPath = strPath & ""
    strFile = Dir(strPath & "*.xls")
    Do While strFile <> ""
    i = i + 1
    Set wbk = Workbooks.Open(strPath & strFile)
    ' process workbook here
    wbk.Close
    strFile = Dir
    Loop
    End Sub
    </code>
    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Jun 2006
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro that opens each .xls file data out macro (Excel)

    Thanks it works great

    Legares work nicely too, Thanks a bunch gents

Posting Permissions

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