Open file, perform operation, close file, LOOP?

[MatthewBell]

Hi there, it's a long time since I last posted on here, I hope the forum's still as good as it used to be.

I have a load of test data files with a standard file name format. "baXXc*.DFT" and I have to copy some data from each to an excel workbook.
XX is a number that increases with each test and there are three files from each XX number/test.
* is a number 1-3 (there are 3 channels of data incase your wondering).

I need to open the files in the order
ba01c1.DFT
ba02c1.DFT
ba03c1.DFT
ba04c1.DFT
ba05c1.DFT etc etc
until the last XX number and copy 2cols of data from each onto a sheet (I have code to get a DFT to open in excel and perform the copy). I need the loop to paste the data in the next two clear cols in the sheet so I'll need code for this.

I then want to do the same for all the baXXc2 and c3 files putting them on 2 other sheets.

All the files are in a single folder.

Can anyone give me some loops, into which I can put my copy code, that will open the files in the correct order (all XX numbers for c1 then all for c2 etc) and also ensure the 2cols I copy go into the next two clear cols in the sheet.

Many thanks for reading, any help would be appreciated.

Matt

 

[Loomah]

Hi
This is a starting point on which some building can be done. One assumption is that your files are numbered in order; ie XX = 01, 02, 03....nn and not XX = 01, 03, 04, 07 etc.

The loop first increments you "c" number then loops from 01 until a file cannot be found.

I've only tested this in theory and not with real files hence testing with the message box.

Note that this will not work in versions of Excel prior to 2000.

Sub lime()
'open files in the format "baXXc*.DFT"
Dim count1 As Integer   ' "XX"
Dim count2 As Integer   ' "*"
Dim sFile As String
Dim ofso As Object

Set ofso = CreateObject("Scripting.FileSystemObject")

For count2 = 1 To 3 'known values for *
count1 = 0
    Do
        On Error Resume Next
        count1 = count1 + 1 'create value for XX
        sFile = "ba" & Format(count1, "0#") & "c" & count2 & ".DFT"
        MsgBox sFile
            'file opening and manipulation code goes here
            'Workbooks.Open sFile
        On Error GoTo 0
    Loop While ofso.fileexists(sFile)
Next
Set ofso = Nothing
End Sub

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[MatthewBell]

Thanks for that Loomah. I'll give that a go and let you know how I get on.

Matt

 

[Loomah]

OK!!

In the meantime here's a "better" version of the Do...Loop that removes the need for error handling - which is how I should have done it in the first place!!

Do
    count1 = count1 + 1 'create value for XX
    sFile = "ba" & Format(count1, "0#") & "c" & count2 & ".DFT"
        If ofso.fileexists(sFile) Then
            'file opening code goes here
            Workbooks.Open sFile
        End If
Loop While ofso.fileexists(sFile)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[MatthewBell]

Thanks Loomah
That worked and I have a lovely collection of data in my worksheet now. I now need to make charts from the data which I'm also stuck on but I'll make a new post for that.

Matt