 |
|

09-08-2003, 07:00 AM
|
 |
Centurion
|
|
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 173
|
|
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.
|
|

09-08-2003, 07:21 AM
|
 |
Licensed to post
* Expert *
|
|
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
|
|
|

09-08-2003, 07:30 AM
|
 |
Centurion
|
|
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 173
|
|
thanks.. I'll give em a try
|
|

09-08-2003, 08:12 AM
|
 |
Centurion
|
|
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 173
|
|
import_csv_file(filelist1.csv)
would this work the same as import_text_file(filelist1.text) for comma delimited files?
|
|

09-08-2003, 08:32 AM
|
 |
Licensed to post
* Expert *
|
|
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
|
|
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.

|
|

09-08-2003, 12:02 PM
|
 |
Centurion
|
|
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 173
|
|
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 
|
|

09-08-2003, 12:40 PM
|
 |
Licensed to post
* Expert *
|
|
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
|
|
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.
|
|

09-08-2003, 01:22 PM
|
 |
Microsoft Excel MVP
Forum Leader * Guru *
|
|
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
|
|
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
|
|

09-08-2003, 01:37 PM
|
 |
Centurion
|
|
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 173
|
|
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
|
|

09-08-2003, 01:45 PM
|
 |
Centurion
|
|
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 173
|
|
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..
|
|

09-08-2003, 01:48 PM
|
 |
Microsoft Excel MVP
Forum Leader * Guru *
|
|
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
|
|
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
|
|

09-08-2003, 01:52 PM
|
 |
Microsoft Excel MVP
Forum Leader * Guru *
|
|
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
|
|
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
|
|

09-09-2003, 03:51 AM
|
 |
Green-Eyed
Super Moderator * Guru *
|
|
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
|
|
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 
|
|

09-09-2003, 07:49 AM
|
 |
Microsoft Excel MVP
Forum Leader * Guru *
|
|
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
|
|
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
|
|

09-09-2003, 09:29 AM
|
 |
Centurion
|
|
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 173
|
|
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
|
|

09-09-2003, 09:45 AM
|
 |
Microsoft Excel MVP
Forum Leader * Guru *
|
|
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
|
|
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
|
|

09-10-2003, 02:34 AM
|
 |
Licensed to post
* Expert *
|
|
Join Date: Jul 2003
Location: York, Uk
Posts: 1,637
|
|
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.
|
|

09-10-2003, 06:54 AM
|
 |
Green-Eyed
Super Moderator * Guru *
|
|
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
|
|
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 
|
|

09-10-2003, 07:12 AM
|
 |
Microsoft Excel MVP
Forum Leader * Guru *
|
|
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
|
|
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
|
|

09-10-2003, 08:07 AM
|
 |
Centurion
|
|
Join Date: Jul 2003
Location: Ontario, Canada
Posts: 173
|
|
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
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Thread Tools |
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|