Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Directory file listing - HELP


Reply
 
Thread Tools Display Modes
  #1  
Old 09-08-2003, 07:00 AM
Jubei's Avatar
Jubei Jubei is offline
Centurion
 
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 172
Unhappy Directory file listing - HELP


How do I get a listing of 'files*.csv' from a directory?

I want to get this list from the directory, then stick it on a seperate page to call from.

1- import the first file 'file1.csv' where I extract the info to another worksheet 'worksheet2' to print
2- clear what i just inserted into worksheet2 for the next file to be inserted so that they do not over run each other if one data file is too large, yet not deleting charts and graphs.
3- clear the imported file 'file1.csv' from worksheet1
4- import the next file from the directory listing 'file2.csv'


does this sound undoable?
I am not having much luck finding examples of code on this sort of tasking.
Reply With Quote
  #2  
Old 09-08-2003, 07:21 AM
mark007's Avatar
mark007 mark007 is offline
Licensed to post

* Expert *
 
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
Default

Reply With Quote
  #3  
Old 09-08-2003, 07:30 AM
Jubei's Avatar
Jubei Jubei is offline
Centurion
 
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 172
Default

thanks.. I'll give em a try
Reply With Quote
  #4  
Old 09-08-2003, 08:12 AM
Jubei's Avatar
Jubei Jubei is offline
Centurion
 
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 172
Default

import_csv_file(filelist1.csv)

would this work the same as import_text_file(filelist1.text) for comma delimited files?
Reply With Quote
  #5  
Old 09-08-2003, 08:32 AM
mark007's Avatar
mark007 mark007 is offline
Licensed to post

* Expert *
 
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
Default

Well if you wrote it that way!

import_text_file was a method written in VB rather than one that is automatically available.

Ok let's take what you are wanting to do one step at a time:

1. want to get this list from the directory, then stick it on a seperate page to call from

For this you want to use something like the following making sure you have added a reference to the microsoft scripting run time.

Code:
Dim obFS as scripting.filesystemobject dim myfold as scripting.folder dim myfile As scripting.file Dim x As String dim fileCount as integer fileCount=0 Set obFS=new scripting.filesystemobject x="Enter the path to the directory here" Set myfold=fsob.getfolder(x) For Each myfile In myfold.files 'test to see if it is a .csv file if lcase(right(myfile.name,3))="csv" then 'add it to your list of files which I'll assume should be on a sheet named FileList fileCount=fileCount+1 sheets("FileList").range("a1").offset(fileCount,0)=myfile.name end if Next


2. import the first file 'file1.csv' where I extract the info to another worksheet 'worksheet2' to print


Record a macro to show you how to do this then clean it up a bit i.e. replace .selects and .activates where you can. Also add in a variable name of the file.

3.clear what i just inserted into worksheet2 for the next file to be inserted so that they do not over run each other if one data file is too large, yet not deleting charts and graphs.

I think:

sheets("Sheet2").usedrange.clearcontents

should handle this bit.

4. clear the imported file 'file1.csv' from worksheet1

as above but different sheet name

5. import the next file from the directory listing 'file2.csv'

for this you need to start a loop above point 2 that loops thropugh each file name in the sheet called FileList that we created in step 1. Something like:

Code:
for looper=2 to sheets("FileList").range("a1").end(xldown).row filename=sheets("FileList").range("a1").offset(looper-1,0).value '...... 'now do steps 2 to 4 '...... 'loop round next looper

Have a go at it then if you get stuck post what you have so far here and I'll have a look at it.

Reply With Quote
  #6  
Old 09-08-2003, 12:02 PM
Jubei's Avatar
Jubei Jubei is offline
Centurion
 
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 172
Default

I got the file list to work from many different methods shown from this forum.
I'm still stuck on how to import from the directory listing i just made.

In the Help section it tells me not to use import... then what the heck am I supposed to use?

I am assuming that I have absolutely no idea what's going on here.. I think I'll go for a walk before I kill my computer
Reply With Quote
  #7  
Old 09-08-2003, 12:40 PM
mark007's Avatar
mark007 mark007 is offline
Licensed to post

* Expert *
 
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
Default

The easiest way is probably to open the file using:

Code:
Workbooks.OpenText "C:\test.csv"

and then copy the entire range to your workbook sheet. You can then close the csv file.

I'd write more but I have to get going, I'm sure someone else can fill in if you have any problems.
Reply With Quote
  #8  
Old 09-08-2003, 01:22 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ok, I'll tag in...

Mark, good luck on that exam... I'm sure you'll rock it
(That is, if you don't spend too much time around here... )


Jubie,

Try Mark's code, above, it should work fine. If not you could try:
Code:
Workbooks.Open Filename:="C:\test.csv", Format:=2
-- Mike
Reply With Quote
  #9  
Old 09-08-2003, 01:37 PM
Jubei's Avatar
Jubei Jubei is offline
Centurion
 
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 172
Default

The code does work for the most part. I have my listing of Files in Sheets="File Names"
I have my Button on the same sheet as the list of files within a directory.

file1.csv
file2.csv
file3.csv
file4.csv
file5.csv

How do I get the code to reference to the desired file on the left?

Code:
Workbooks.Open Filename:="C:\test.csv", Format:=2

after that I have a nice problem which everybody has an answer for.. but doesn't work. :P
Reply With Quote
  #10  
Old 09-08-2003, 01:45 PM
Jubei's Avatar
Jubei Jubei is offline
Centurion
 
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 172
Default

This is an example of some files listed

Metric 5197440106 Aug-03.csv
Metric 5197441831 Aug-03.csv
Metric 5197441936 Aug-03.csv
Metric 5197442045 Aug-03.csv
Metric 5197442337 Aug-03.csv
Metric 5197443147 Aug-03.csv
Metric 5197443197 Aug-03.csv
Metric 5197443372 Aug-03.csv
Metric 5197444462 Aug-03.csv
Metric 5197444905 Aug-03.csv
Metric 5197448403 Aug-03.csv
Metric 5197448581 Aug-03.csv
Metric 5197449273 Aug-03.csv
Metric 5198210219 Aug-03.csv
Metric 5198210374 Aug-03.csv
Metric 5198210620 Aug-03.csv
Metric 5198210685 Aug-03.csv
Metric 5198210841 Aug-03.csv
Metric 5198211446 Aug-03.csv

200 of these files and I'm ready to shoot myself in the head.. know what I mean?

so I open one file at a time, "Select all" ???
Paste it to my "Data" sheet, perform functions and calculations.
Print to file remember this one Mike_R? hehe
then start on next file within the list. - this is where the loop starts...

I get the idea.. Now I just need to put something together.
I'll start with what I have and work from there.. if anyone has an idea that I can add to my 'neverending torture' I'll take it into consideration..
Reply With Quote
  #11  
Old 09-08-2003, 01:48 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

I'm not 100% certain where you want to go from here. Do you want the User to select the Cell that they want, and then hit the Button to open that file?

In that case I think you are looking for something like this?
Code:
Sub OpenSelected() Const TheDirectory = "C:\The full path\would go\here\" Dim FileName As String Dim FullPathName As String FileName = ActiveCell.Value FullPathName = TheDirectory & FileName Workbooks.Open FileName:=FullPathName, Format:=2 End Sub
Is this close?

-- Mike
Reply With Quote
  #12  
Old 09-08-2003, 01:52 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Heh, ok we we're posting at the same time...
Quote:
Originally Posted by Jubei
This is an example of some files listed

Metric 5197440106 Aug-03.csv
Metric 5197441831 Aug-03.csv
Metric 5197441936 Aug-03.csv
...
...
Metric 5198211446 Aug-03.csv

200 of these files and I'm ready to shoot myself in the head.. know what I mean?

so I open one file at a time, "Select all" ???
Paste it to my "Data" sheet, perform functions and calculations.
Print to file remember this one Mike_R? hehe
then start on next file within the list. - this is where the loop starts...I get the idea.. Now I just need to put something together.
..

Sounds like you got it PERFECT.

> "Print to file remember this one Mike_R? hehe"

Hah! If we need SendKeys to get this one I'll eat my hat. I'll give this a little thought too, and then we can compare "loops"...

-- Mike
Reply With Quote
  #13  
Old 09-09-2003, 03:51 AM
Timbo's Avatar
Timbo Timbo is offline
Green-Eyed

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
Default

I'm not able to test this myself, but I wonder if it wouldn't be quickest to open the files as text, trasfer the contents to an array, then do the old "Sheet1.Range("X:Y").Value = aryTxt"
...just an idea
__________________
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional
Reply With Quote
  #14  
Old 09-09-2003, 07:49 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Timbo,

> Timbo: "I'm not able to test this myself, but I wonder if it wouldn't be quickest to open the files as text, trasfer the contents to an array, then do the old Sheet1.Range("X:Y").Value = aryTxt..."

Timbo, I'm not familiar with the .OpenText() method, would this allow direct access to putting values into an Array? Or I guess you're suggesting Range1.Value --> Array() --> Range2.Value instead of Range1.Copy & Range2.Paste. Huh. I'd be surprised if that's faster, but who knows? How about Range2.Value = Range1.Value directly, leaving out the Array() middle-man... Hmm... If I find the time, I'll time test this and post the results as a new thread.


Jubei,

I put the following together. You should run it, just to make sure it works -- it should run fine -- but you will still need to make adjustments at the .PrintOut line for two reasons:

(1) If you are printing as a PDF file, you will need to use the SendKeys tricks, or whatever you finally settled on to get this working the last time.

(2) I'm guessing that the .CSV file, once pasted, causes other Charts & Data to update on other Worksheets. I'm guessing that it is these other Worksheets that need to be printed. The way I have it, it's printing the raw data itself, which I doubt is what you need.

Anyway, here's the code:
Code:
Option Explicit Sub Main() Dim oFilesCollection As Collection Dim strFileName As Variant ' Must be Variant for For Each..Next. Dim strDirPath As String Dim wbCSV As Workbook strDirPath = "C:\My Documents\Test Directory\" Set oFilesCollection = ListFiles(strDirPath) For Each strFileName In oFilesCollection Set wbCSV = Application.Workbooks.Open(Filename:=strDirPath & strFileName, _ Format:=2) Call wbCSV.Sheets(1).Cells.Copy Call ThisWorkbook.Worksheets(1).Cells(1, 1).PasteSpecial(xlPasteAll) Call ThisWorkbook.Worksheets(1).PrintOut Call ThisWorkbook.Worksheets(1).Cells.ClearContents Next strFileName End Sub Function ListFiles(strDirectoryPath As String) As Collection ' Credit for this Function owed to Mark007, ' I used his code, above, and modified it here: Dim oFileSystem As Scripting.FileSystemObject Dim oFolder As Scripting.Folder Dim oFile As Scripting.file Dim ResultCollection As New Collection Set oFileSystem = New Scripting.FileSystemObject Set oFolder = oFileSystem.GetFolder(strDirectoryPath) For Each oFile In oFolder.Files If LCase(Right(oFile.Name, 4)) = ".csv" Then Call ResultCollection.Add(oFile.Name) End If Next oFile Set ListFiles = ResultCollection End Function
If the above does not run, you may need to add the Scripting Runtime Library to your project. You would go to Alt|Tools|References... and then look for the "Microsoft Scripting Runtime Library", check it off and then hit ok. Then save your project so that it "sticks". (This library is also known as the Scrrun.dll, but it shouldn't be listed that way in References.)

Hope this works...!

,
Mike
Reply With Quote
  #15  
Old 09-09-2003, 09:29 AM
Jubei's Avatar
Jubei Jubei is offline
Centurion
 
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 172
Default

well that looks a heck of a lot different than my code... But I am also asuming that yours works as mine has a lot of bugs...

what is the Option Explicit you have over your Sub Main()?

I have never seen that before nor do I see it in my books... mostly cause I just started VB 3 months ago...


I'll give your code a go and then post my findings. Thanks all
Reply With Quote
  #16  
Old 09-09-2003, 09:45 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Only 3 mos? Wow, you've come a long way FAST...! Doing great!!

"Option Explicit" is the most important line of code in any module! You should put it at the top of EVERY module, it will help you pick up errors at Compile Time, before you even run it.

It requires that you Dim ALL your variables ahead of time. It's a spell-checker and type-checker really. I would just use it!

To have that line included automatically in all of your Modules, I would goto Alt|Tools|VBE Options... then on the Editor Tab check off the box that reads: "Require Variable Declaration" and then hit <OK>.

I think this will help you with buggy code A LOT.

-- Mike
Reply With Quote
  #17  
Old 09-10-2003, 02:34 AM
mark007's Avatar
mark007 mark007 is offline
Licensed to post

* Expert *
 
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
Default

Well things seem to have moved along a but here since I took an exam break!

Went alright, another one under the belt I reckon

So Jubei, let us know how it works out.
Reply With Quote
  #18  
Old 09-10-2003, 06:54 AM
Timbo's Avatar
Timbo Timbo is offline
Green-Eyed

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
Default

Mike, here's roughly what I was thinking of:
Code:
Sub Text() Dim fs As Object, oFile As Object Dim strText$ Dim aryData Set fs = CreateObject("Scripting.FileSystemObject") Set oFile = fs.OpenTextFile("c:\book1.csv", 1) strText$ = oFile.ReadAll aryData = Split(strText$, Chr(13)) oFile.Close 'Workbooks("Output").Sheets1.Range("X:Y").Value = aryData Set oFile = Nothing Set fs = Nothing End Sub
..slight flaw in the thinking - 'Split' only returns a 1 dimentional array... oh well
__________________
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional
Reply With Quote
  #19  
Old 09-10-2003, 07:12 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ahhh! That would have been kinda cool! Now we just need someone to make a Split3D() command .

I didn't know what you meant because, apparently, the Workbooks collection has a .OpenText method also. I'd never seen it before, but testing it now it seems to be similar to Workbooks.Open(Format:=2).

Nice try though, that would have been very slick.

-- Mike
Reply With Quote
  #20  
Old 09-10-2003, 08:07 AM
Jubei's Avatar
Jubei Jubei is offline
Centurion
 
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 172
Post

ok, I almost got it. I just had to change the code to do a few more things and a few less things. anyways...

a couple easy questions and I should be done.

How do I call Sub Adjust() ??? 'this is another subroutine i need to add
and.. if I detect an error in the database as it is processing and I do End Subroutine, will it also stop my Main sub or will it only stop the sub running and return the the main to continue on?

another addon here... * * * * this thing is starting to work so sweet!!! 8)

I need to copy a range of cells, but they are not always the same length, get it?

select range C4:C( untill(x="")) ?
I am thinking of selecting the whole column untill the cell value equals NULL. I could select the whole column, but I don't want everything. Only till the Null value.(or empty cell)

Do I have the right idea here? hope so...

Once this is done then I will post my resulting code so you guys can see what you've created.
thanks again
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Installation Problem - PLs help urgenlty dpdsouza Installation / Documentation 4 12-02-2004 07:09 PM
Doesn't want to register! MikeyM Installation / Documentation 5 03-02-2003 08:22 PM
File Searching / Recursion question Mowzee General 4 10-30-2002 09:26 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->