+ Reply to Thread
Results 1 to 3 of 3

Counting # of files using DIR and then opening them in a loop = problems!!

Hybrid View

magnus.blomquist Counting # of files using DIR... 03-27-2014, 12:56 PM
ragulduy Re: Counting # of files using... 03-27-2014, 12:59 PM
magnus.blomquist Re: Counting # of files using... 03-27-2014, 01:11 PM
  1. #1
    Registered User
    Join Date
    07-03-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    19

    Counting # of files using DIR and then opening them in a loop = problems!!

    Hi,
    I've got some code that counts the number of *.csv files in a folder

    FolderPath = "my directory"
    path = FolderPath & "\*.csv"
    filename = Dir(path)
    Do While filename <> ""
       filecount = filecount + 1
       filename = Dir()
    Loop
    
    filename = Dir(path)
    
        For filecount = 1 To filecount
        Debug.Print filename
        filename = Dir()
        Workbooks.Open ("mydirectory"/filename
    This first loop works fine and gives me the number of files perfectly. It's the second part that brings in the problem.
    The next section is a loop that opens each file, extracts the data, pastes it into a new file, saves and closes the newly created one, closes the opened one, and then loops onto the next file in the directory. With me?
    However, when the "for filecount" loop calls the Dir(), it picks up the name of the second file in the directory, and not the first. so the sequence goes file #2,3,4,5.... but misses #1. It seems that I have to have the "filename = Dir(path)" between the Loop and For loop as otherwise there is no filename.
    Can anyone help me to work out the order/naming to both count number of files and open all the files sequentially in a loop?
    Thanks!!

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Counting # of files using DIR and then opening them in a loop = problems!!

    FolderPath = "my directory"
    path = FolderPath & "\*.csv"
    filename = Dir(path)
    Do While filename <> ""
       filecount = filecount + 1
       filename = Dir()
    Loop
    
    filename = Dir(path)
    
        For filecount = 1 To filecount
        Debug.Print filename
            Workbooks.Open ("mydirectory/" & filename)
    'code
    filename = dir()
    next

  3. #3
    Registered User
    Join Date
    07-03-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Counting # of files using DIR and then opening them in a loop = problems!!

    So simple.... THANK YOU!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Problems with opening csv-files
    By Pardo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-29-2014, 04:26 PM
  2. Counting files, opening each one and retrieving a value
    By rcruzeiro in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-20-2009, 05:41 AM
  3. VBA Loop Interupted By Opening Other Excel Files
    By bartinp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-21-2009, 12:50 PM
  4. problems opening files in XLSTART
    By Scott in forum Excel General
    Replies: 1
    Last Post: 04-11-2006, 11:45 AM
  5. problems opening files larger than 1mb in size
    By richtea in forum Excel General
    Replies: 1
    Last Post: 03-21-2006, 02:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1