Macro to perform task, delete file until directory is empty 1

JustATheory · Oct 30, 2007

Greetings,

I need to write a macro that copies information from a number of spreadsheets in one directory and appends the information in a spreadsheet in a different directory. The file names are varied and the number of spreadsheets will change each time this is run. Once the information is copied from a spreadsheet I need to cut and paste it to a third directory and continue until all files have been moved to the third directory.

Any help is much appreciated
Thanks,
Andy

macropod · Oct 30, 2007

Hi Andy,

So what code have you got so far, and which bits are you having trouble with? We're not here to write the code for you - especially since we've got no idea of the folder or workbook structures - only to help you sort out problems you might be having getting your code to run properly.

Cheers

[MS MVP - Word]

JustATheory · Oct 30, 2007

Hi macroprod,

Sorry I wrote to much, I'm only having trouble with counting the files, I figure it's a Do Until n=0, function. All other code I'm good with, moving files, appending data etc...

Thanks,
Andy

macropod · Oct 30, 2007

Hi Andy,

Here's some modified code from the MS help file:

Code:

Sub GetFiles(SourceFolder)
' This sub processes all files in the called folder
Dim fs, ff
Dim FileCount As Integer
Dim i As Integer
Set fs = Application.FileSearch
With fs
    ' Where to look
    .LookIn = SourceFolder
    ' What kind of file to look for
    .FileName = "*.xls"
    ' Sort the files and check how many
    If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderDescending) > 0 Then
        ' Store and report the number of files matching the 'kind' criteria
        FileCount = .FoundFiles.Count
        MsgBox FileCount & " file(s) were found."
        ' process all matching files
        For i = FileCount To 1 Step -1 
            ff = fs.FoundFiles(i)
            ' Open the file
            ' Get the data
            ' Close the file
            ' Move the file
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub

As coded, you pass the source folder's name to the sub; otherwise delete the 'SourceFolder' reference at the top and put whatever you need to get the folder in the '.LookIn = SourceFolder' line.

Since we need to loop through the folder backwards (ie counting down) so as ensure all found files are processed, matching that with a descending file order listing results in the files being actually processed in an ascending order.

Cheers

[MS MVP - Word]

JustATheory · Nov 15, 2007

I just now had a chance to try this out and it works great!

Thanks,
Andy

JustATheory · Jan 10, 2008

Hi Macropod,

This piece of code works great and I'm now trying to tighten up the production part of the code. I've been doing a copy append paste for the files I work with, I noticed in a previous thread of yours (thread707-1216205), it looks like your passing ((importing text) beginning at Do While Not EOF(FileNum)) without opening a secondary file, is this possible to move data from one excel file to another (append) without opening the files? Each file has a header and has from 1 to 4k records, but not quite sure how to pass the data and append it.

You always have a great solution,
Thanks,
Andy

macropod · Feb 3, 2008

Hi Andy,

I've been travelling O/S for a while (still am), so I haven't had a chance to reply before this.

The short answer is no. You can't edit a closed file, which is what *moving* data between files entails.

In theory, you could get around this by editing at the disk level, but IMHO the effort and risks wouldnt be worth it.

Cheers

[MS MVP - Word]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Macro to perform task, delete file until directory is empty 1

JustATheory

IS-IT--Management

macropod

Technical User

JustATheory

IS-IT--Management

macropod

Technical User

JustATheory

IS-IT--Management

JustATheory

IS-IT--Management

macropod

Technical User

Similar threads

Part and Inventory Search

Sponsor