Please Help for a macro reading files not in sequence

G

Guest

Hello

I am using WindowsXP Prof. and Excel 2003
I the folders "invoice 2006" that are in the Drives A, C end E there are 6
each excel files numbered from _001_2006 to _006_2006.xls

The macro I wrote here down, reads the files in each folder and writes the
result in sheet1 starting in cell A1

It does it but they are not read in sequence appart those in C:\ that are
right, what I get is:

A:\_002_2006.xls
A:\_001_2006.xls
A:\_003_2006.xls
A:\_004_2006.xls
A:\_005_2006.xls
A:\_006_2006.xls

C:\_001_2006.xls
C:\_002_2006.xls
C:\_003_2006.xls
C:\_004_2006.xls
C:\_005_2006.xls
C:\_006_2006.xls

E:\_006_2006.xls
E:\_001_2006.xls
E:\_002_2006.xls
E:\_003_2006.xls
E:\_004_2006.xls
E:\_005_2006.xls

Moreover if it is possible I would like to comapre the files in the
mentioned folder and if in one of them one or more files are missing in one
of the folder the macro should copy them in it.

What is wrong in the nacro?
Thanks for any help I can get on the matter
Francesco


Sub ListFiles()
Dim aryDrives
Dim oFSO
Dim n As Long
Dim i As Long


Dim sPath As String
On Error GoTo cleanUp
On Error Resume Next
aryDrives = Array("A:\", "C:\", "E:\")
Set oFSO = CreateObject("Scripting.FileSystemObject")
i = 0 'Set i to row-1 of the starting row of the data.

For n = LBound(aryDrives) To UBound(aryDrives)
sPath = aryDrives(n)
MyPath = sPath & "invoicingPRG\Invoice2006\*.*"
'MyPath = "C:\programmaFatturazione\Fatture2006\*.*" 'Modify
to suit

myFile = Dir(MyPath, vbNormal)
While myFile <> ""

Cells(i, 1) = sPath & myFile 'Modify 1 here to the column you want
the List
myFile = Dir
i = i + 1
Wend
i = i + 1
Next n
cleanUp:
Application.ScreenUpdating = True
End Sub
 
M

mrice

My understanding is that you have no control over the order in which the
dire command reads files off a disc. I guess that its something due to
the actual locations on the disc.

What you might try is to change your Dir call to a more specific one
with a loop

e.g.

Dir "C:\....\1*.xls the first time around and

Dir "C:\....\2*.xls the first time around etc.

This assumes that the file names are predictable.

Hope this helps.

Martin
 
G

Guest

Hello mrice
thanks for your suggestion but it did not solve my problem.
Hope I get some other solution :)
Francesco
 
J

Jim Cone

Sub ListFilesRevised()
On Error GoTo cleanUp
Dim oFSO As Object
Dim MyFile As Object
Dim MyFolder As Object
Dim aryDrives As Variant
Dim n As Long
Dim i As Long
Dim MyPath As String
Dim sPath As String

aryDrives = Array("A:\", "C:\", "E:\")
Set oFSO = CreateObject("Scripting.FileSystemObject")
i = 1
Application.ScreenUpdating = False

For n = LBound(aryDrives) To UBound(aryDrives)
sPath = aryDrives(n)
MyPath = sPath & "invoicingPRG\Invoice2006\"
Set MyFolder = oFSO.getfolder(MyPath)
For Each MyFile In MyFolder.Files
Cells(i, 1) = sPath & MyFile.Name
i = i + 1
Next
Next n

cleanUp:
Set MyFile = Nothing
Set MyFolder = Nothing
Set oFSO = Nothing
Application.ScreenUpdating = True
End Sub
--

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Francesco" <[email protected]>
wrote in message...
Hello
I am using WindowsXP Prof. and Excel 2003
I the folders "invoice 2006" that are in the Drives A, C end E there are 6
each excel files numbered from _001_2006 to _006_2006.xls
The macro I wrote here down, reads the files in each folder and writes the
result in sheet1 starting in cell A1
It does it but they are not read in sequence appart those in C:\ that are
right, what I get is:

A:\_002_2006.xls
A:\_001_2006.xls
A:\_003_2006.xls
A:\_004_2006.xls
A:\_005_2006.xls
A:\_006_2006.xls

C:\_001_2006.xls
C:\_002_2006.xls
C:\_003_2006.xls
C:\_004_2006.xls
C:\_005_2006.xls
C:\_006_2006.xls

E:\_006_2006.xls
E:\_001_2006.xls
E:\_002_2006.xls
E:\_003_2006.xls
E:\_004_2006.xls
E:\_005_2006.xls

Moreover if it is possible I would like to comapre the files in the
mentioned folder and if in one of them one or more files are missing in one
of the folder the macro should copy them in it.
What is wrong in the nacro?
Thanks for any help I can get on the matter
Francesco


Sub ListFiles()
Dim aryDrives
Dim oFSO
Dim n As Long
Dim i As Long

Dim sPath As String
On Error GoTo cleanUp
On Error Resume Next
aryDrives = Array("A:\", "C:\", "E:\")
Set oFSO = CreateObject("Scripting.FileSystemObject")
i = 0 'Set i to row-1 of the starting row of the data.

For n = LBound(aryDrives) To UBound(aryDrives)
sPath = aryDrives(n)
MyPath = sPath & "invoicingPRG\Invoice2006\*.*"
'MyPath = "C:\programmaFatturazione\Fatture2006\*.*" 'Modify to suit

myFile = Dir(MyPath, vbNormal)
While myFile <> ""

Cells(i, 1) = sPath & myFile 'Modify 1 here to the column you want the List
myFile = Dir
i = i + 1
Wend
i = i + 1
Next n
cleanUp:
Application.ScreenUpdating = True
End Sub
 
G

Guest

Hi Jim,
Thanks for answering me, this what I get:
( Folder in a:\ , there are only 2 files and 6 files in the athers)
Is there any reason why those of folder in C are in sequence while those in
A and E are not
A:\_002_2006.xls
A:\_001_2006.xls

C:\_001_2006.xls
C:\_002_2006.xls
C:\_003_2006xls
C:\_004_2006.xls
C:\_005_2006.xls
C:\_006_2006.xls

E:\_006_2006.xls
E:\_001_2006.xls
E:\_002_2006.xls
E:\_003_2006.xls
E:\_004_2006.xls
E:\_005_2006.xls

Thanks
Francesco
 
J

Jim Cone

Francesco,

I am not sure.
Try sorting the files in each folder (in windows explorer) then
running the code.
Of course, you can just sort each worksheet list in Excel.

Note: made a couple of minor changes in the code designated by <<
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Francesco" <[email protected]>
wrote in message
Hi Jim,
Thanks for answering me, this what I get:
( Folder in a:\ , there are only 2 files and 6 files in the athers)
Is there any reason why those of folder in C are in sequence while those in
A and E are not
A:\_002_2006.xls
A:\_001_2006.xls

C:\_001_2006.xls
C:\_002_2006.xls
C:\_003_2006xls
C:\_004_2006.xls
C:\_005_2006.xls
C:\_006_2006.xls

E:\_006_2006.xls
E:\_001_2006.xls
E:\_002_2006.xls
E:\_003_2006.xls
E:\_004_2006.xls
E:\_005_2006.xls

Thanks
Francesco

Jim Cone said:
Sub ListFilesRevised()
On Error GoTo cleanUp
Dim oFSO As Object
Dim MyFile As Object
Dim MyFolder As Object
Dim aryDrives As Variant
Dim n As Long
Dim i As Long
Dim MyPath As String
Dim sPath As String

aryDrives = Array("A:\", "C:\", "E:\")
Set oFSO = CreateObject("Scripting.FileSystemObject")
i = 1
Application.ScreenUpdating = False

For n = LBound(aryDrives) To UBound(aryDrives)
sPath = aryDrives(n)
MyPath = sPath & "invoicingPRG\Invoice2006\"
Set MyFolder = oFSO.getfolder(MyPath)
For Each MyFile In MyFolder.Files
Cells(i, 1).Value = sPath & MyFile.Name '<<<<
i = i + 1
Next
Click to expand...
i = i + 1 '<<<<
 
J

Jim Cone

This will sort each separate file list.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Sub ListFilesRevised2()
On Error GoTo cleanUp
Dim oFSO As Object
Dim MyFile As Object
Dim MyFolder As Object
Dim aryDrives As Variant
Dim n As Long
Dim lngTop As Long '<<
Dim i As Long
Dim MyPath As String
Dim sPath As String

aryDrives = Array("A:\", "C:\", "E:\")
Set oFSO = CreateObject("Scripting.FileSystemObject")
i = 1
Application.ScreenUpdating = False

For n = LBound(aryDrives) To UBound(aryDrives)
sPath = aryDrives(n)
lngTop = i '<<<<
MyPath = sPath & "invoicingPRG\Invoice2006\"
Set MyFolder = oFSO.getfolder(MyPath)
For Each MyFile In MyFolder.Files
Cells(i, 1).Value = sPath & MyFile.Name
i = i + 1
Next
Range(Cells(lngTop, 1), Cells(i - 1, 1)).Sort key1:=Cells(lngTop, 1) '<<
i = i + 1
Next n

cleanUp:
Set MyFile = Nothing
Set MyFolder = Nothing
Set oFSO = Nothing
Application.ScreenUpdating = True
End Sub
 
G

Guest

Thanks Jim,
I get them now in sequence but I had to open the file n.2 in A and the n. 6
in E and to save them again in theri folder and then now they are read in
sequence.
I do not inderstand the reason but it works now.

by the way , there is a way, if the folders in question do not have the same
number and type of files to to copy those missing from the most updated
folder ( supposing that C has 6 files and the others one 4 and one 5, to copy
those missing from the folder in C)
Thanks a lot for your help
Happy Easter
Francesco
 
J

Jim Cone

Francesco,

It takes just a few seconds in Windows Explorer to manually copy files
from one folder to another.
Using code, you will have to determine the folder with the most files.
(Folder.Files.Count)
But since that doesn't guarantee those are the latest files, you will
have to check the DateLastModified property of each file and compare it to
each file in the other folders, before replacing it.
If you know for sure that the folder with the most files is the latest then,
you could use the CopyFolder method and set the Overwrite argument to true.

Details on all of this is in...
Microsoft Windows Script 5.6 Documentation
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/webdev.asp
The Script Runtime | FileSystemObject is the pertinent subject matter.

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Francesco" <[email protected]>
wrote in message

Thanks Jim,
I get them now in sequence but I had to open the file n.2 in A and the n. 6
in E and to save them again in theri folder and then now they are read in
sequence.
I do not inderstand the reason but it works now.

by the way , there is a way, if the folders in question do not have the same
number and type of files to to copy those missing from the most updated
folder ( supposing that C has 6 files and the others one 4 and one 5, to copy
those missing from the folder in C)
Thanks a lot for your help
Happy Easter
Francesco
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert Multiple CSV Files to XLS Files (Again) 2
Macro help 2
Macro with actions on variable/dynamic pathnames 1
Combining several workbooks into one file 4
Problem with an amended macro. Please help! 4
Making Changes in a Group of Workbooks 2
.xls files not deleted after conversion to .xlsx; help please 13
Excel 2007 Macro Help (Excel 2003 not working in 2007) 4

Top