Solved

Excel 2007 VBA code to look through a folder

Posted on 2011-03-01
13
304 Views
Last Modified: 2012-05-11
Hi. Its a long time since I've coded, and I'm having trouble with something I'm sure is pretty easy.. I have Excel 2007 running in Win 7x64.

- I need to specify a folder on my hard drive
- This folder contains many subfolders
- Each subfolder has one .csv (comma separated file - these open in Excel on my PC) file in it. I need to open the .csv file
- (once I have control of the csv file I have already written the code for what I want to do, but it involves importing some of the data from a known cell range to one master workbook)
- I then I need to close it and move on to the next subfolder

Web searches and MS VBA Help both talk about the filesearch command, but this appears to have been disabled by MS in 2007.

I have seen what appear to be similar questions and answers looking through old EE posts - some refer to fso (file system objects), which I don't yet understand, and I can't get the code to run.

Can someone give me some example code please

Thanks
0
Comment
Question by:Arfx3
  • 7
  • 6
13 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35011596
I'm not quite sure what you're asking.

The FSO is one way to access file info.  However, I prefer the old way because you don't have to instantiate any objects:

 
Sub ReadCSVFiles()
    Dim iFileNumber As Integer
    Dim strFileName As String
    Dim strFileContents As String
    
    strFileName = Dir("C:\*.csv")
    Do Until strFileName = ""
       iFileNumber = FreeFile()
       Open strFileName For Binary As #iFileNumber
       strFileContents = Input(LOF(iFileNumber), iFileNumber)
       ' do something with file contents
       Close #iFileNumber
       strFileName = Dir
    Loop
End Sub

Open in new window

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35011630
oops...one correction...on the Open line, you'll need the full path:

Open "C:\" & strFileName For Binary As #iFileNumber

You may want to put the path in a separate variable since you're going to use it twice.
0
 

Author Comment

by:Arfx3
ID: 35011864
Hello rspahitz, thanks for looking at my post.

I have a folder on my hard drive. Its address is
C:Users\Arf\Documents\Project\Data

The 'Data' folder contains only subfolders, i.e. .....\Project\Data\sub1   and sub2, sub3 etc.

Sub1, Sub2, Sub3 etc etc all contain a csv file I want to open. I'm going to copy cells A3:G1000, and copy them into the Workbook that I am running the code from.

When I ran your code, above, I changed your line strFileName = Dir("C:\*.csv") to

strFileName = Dir("C:Users\Arf\Documents\Project\Data\*.csv"), but it bypasses the rest of the code because it didn't find anything - in other words the code immediately decides that (Do Until strFileName = "") is true because strFileName is blank.

I can understand that - the csv files are not in the 'Data' folder, they are all still in the subfolders within the Data folder. Does that help you understand what I'm trying to do?

Thanks

Arf
0
 

Author Comment

by:Arfx3
ID: 35011897
oh, and just to be clear the subfolders are not called sub1 etc - they have different names every time. I was just hoping for some code that went something like;

For Each Subfolder in 'Data'Folder
-open csv file
-copy A3:G1000
-paste to my workbook
-close this csv file
Next Subfolder

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35012068
so you want to basically do the same thing but navigate through each subfolder?
that requires a bit more effort because this doesn't handle recursion well.
However, if there are no sub-sub folders, then this should work, by gathering the list of folders first, then navigating through each one.

See how this works....
 
Sub ReadCSVFiles()
    Dim strRootPath As String
    Dim strFilePaths() As String
    Dim iSubFolderCount As Integer
    Dim strSubFolder As String
    Dim iFileNumber As Integer
    Dim strFileName As String
    Dim strFileContents As String
    Dim iSubFolderCntr As Integer
    
    strRootPath = "C:Users\Arf\Documents\Project\Data"
    
    ReDim strFilePaths(0)
    iSubFolderCount = 0
    strSubFolder = Dir(strRootPath & "\*.*", vbDirectory)
    Do Until strSubFolder = ""
        If strSubFolder <> "." And strSubFolder <> ".." And GetAttr(strRootPath & "\" & strSubFolder) = vbDirectory Then
            strFilePaths(iSubFolderCount) = strSubFolder
            iSubFolderCount = iSubFolderCount + 1
        ReDim Preserve strFilePaths(iSubFolderCount)
        End If
        strSubFolder = Dir
    Loop
    
    For iSubFolderCntr = 0 To iSubFolderCount - 1
        strFileName = Dir(strRootPath & "\" & strFilePaths(iSubFolderCntr) & "\*.csv")
        Do Until strFileName = ""
           iFileNumber = FreeFile()
           Open strRootPath & "\" & strFilePaths(iSubFolderCntr) & "\" & strFileName For Binary As #iFileNumber
           strFileContents = Input(LOF(iFileNumber), iFileNumber)
           ' do something with file contents
           Close #iFileNumber
           strFileName = Dir
        Loop
    Next
End Sub

Open in new window

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35012302
At line 31 in the above, I guess you want to call a routine to do these steps:

-copy A3:G1000
-paste to my workbook

Something like this:

CopyCSVDataToSheet(strFileContents)

then

Private Sub CopyCSVDataToSheet(CSVData As String)
   Dim strDataRows() as String
   Dim iLineCntr as Integer
   Dim strDataLine as String

   strDataRows = Split(CSVData, vbNewLine)
   For iLineCntr = 0 to Ubound(strDataRows)
      strDataLine  = strDataRows(iLineCntr )
'...
   Next
End Sub
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Arfx3
ID: 35012888
This is brilliant (and shoots down my original comments that it was probably a simple solution!) - it works out that there are currently 59 subfolders and has opened the first one.....and that is where I fall down.

In the code;

strDataRows = Split(CSVData, vbNewLine)
   For iLineCntr = 0 to Ubound(strDataRows)
      strDataLine  = strDataRows(iLineCntr )

...I'm not getting very far in controlling what happens next so that I can copy the data into my spreadsheet. Can you explain your thinking pls - I read the help on Split function, didn't seem to mean much to me, I'm afraid. What would I need to do to paste the data into my master spreadsheet?

thanks
Arf



0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35013104
Split is used to split a text string into pieces.  In this case, the entire CSV file is read in so this breaks it at vbNewline, which basically means that it splits it into lines.

From there, you'll need to extract the pieces again since they are comma-separated.  I think there's a library to do that but I haven't explored that in a long time.  The simple way is to simply take strDataLine and split it again on "," (rather than vbNewLine) except that if there are any values that contain commas, this will be wrong.  so you really need to split on qualifiers first (usually quotes) then within each piece, split on commas, then put the items into an array for easy access.

Another possibility is to push everything into the clipboard then try to paste it into Excel...not sure if that would work but it seems that it should.

so for that, you could try this:



Sub CopyCSVDataToSheet(CSVData As String)
    Dim objDataObject As DataObject
    
    Set objDataObject = New DataObject
    objDataObject.SetText CSVData
    objDataObject.PutInClipboard
    Range("A1").Paste
End Sub

Open in new window

0
 
LVL 22

Accepted Solution

by:
rspahitz earned 250 total points
ID: 35013183
sorry...that got submitted while testing

first, you may need to add teh FM20.dll to the VB references (menu Tools | References, [Browse] button, found in win32 folder)

then this code:
 
Sub CopyCSVDataToSheet(CSVData As String)
    Dim objDataObject As DataObject
    
    Set objDataObject = New DataObject
    objDataObject.SetText CSVData
    objDataObject.PutInClipboard
    Range("A1").Select
    ActiveSheet.Paste
End Sub

Open in new window


However, it may put everything into cell A or may put into multiple cells.  I supposed we could then get Excel to perform a Text-To-Columns routine to force it to split.
0
 

Author Comment

by:Arfx3
ID: 35013235
It doesn't seem to like the Dim statement. I'll give this some thought, but I have meetings now, so haven't had time to think it through - just thought I'd mention it in case the reason is obvious to you.

thanks for the help so far.

Arf
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35013250
you probably tried it while I was fixing it in the next post (above yours.)
0
 

Author Comment

by:Arfx3
ID: 35018719
Yup, guess I did :-)

Thanks for all your help - I have it working now. I'm now off to learn the joys of working with spreadsheets with 800,000 rows... I see linking to Access in my near future!!!
0
 

Author Closing Comment

by:Arfx3
ID: 35018756
You need to look at the whole thread to see the solution - this reply fixed the last step of the problem but you need code from earlier posts for a complete answer.

Many thanks rspahitz
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now