| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please Help for a macro reading files not in sequence
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help for a macro reading files not in sequence
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 -- mrice ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=533117 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help for a macro reading files not in sequence
Hello mrice
thanks for your suggestion but it did not solve my problem. Hope I get some other solution :-) Francesco "mrice" wrote: 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 -- mrice ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=533117 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help for a macro reading files not in sequence
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" 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help for a macro reading files not in sequence
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" wrote: 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" 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help for a macro reading files not in sequence
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" 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" wrote: 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 i = i + 1 '<<<< 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" 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help for a macro reading files not in sequence
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help for a macro reading files not in sequence
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 "Jim Cone" wrote: 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" 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" wrote: 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 i = i + 1 '<<<< 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" 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Please Help for a macro reading files not in sequence
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/de...ist/webdev.asp The Script Runtime | FileSystemObject is the pertinent subject matter. Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Francesco" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to open files isn't updating correctly | Excel Discussion (Misc queries) | |||
| Macro that stores all sheets as tab-delimited text files | Excel Discussion (Misc queries) | |||
| Macro for multiple open files | Excel Discussion (Misc queries) | |||
| Macro to delete first line of 200+ files | Excel Discussion (Misc queries) | |||
| Macro - - Automation | Excel Discussion (Misc queries) | |||
Linear Mode
