Pull data from many, poorly named files!

[Stu Dapples]

I am honestly starting to think the people I work with are doing things like this to prove I can't do what they are asking! Fortunately for me, you guys have so far helped me to prove that these things are possible even for mere mortals such as myself! Anyway, enough "smoke blowing" !

I have been asked to get the data out of 2 specific columns from a data sheet which is filled in on each shift and saved. The nomenclature for saving is poor to say the least....
Basically, I have about 350 workbooks with names such as 150kg_NIGHT242, 150kg_MORNING173
where 150kg is the machine, NIGHT/ MORNING is the shift and 242 is 24th of Feb, 173 is 17th March etc.
Is it possible to write some script and point it at all files in directory, regardless of filename? Maybe some sort of wildcard? eg, if filename = 150kg* then run the script?
I toyed with the idea of a user form to get someone to select which file to run the code on, it is do-able but still going to be a long task to get all the data out of 350 plus files....
Any advice, even a direction to start going, would be very much appreciated (and prove to the proliteriate that Excel is unstaoppable!!)
Thanks in advance,
Stu

 

[VoG]

This might be a start http://msdn.microsoft.com/en-us/library/cc837974.aspx

 

[Richard Schollar]

Hi Stu

Dir is what you need:

Const PTH As String = "C:\SomeFolder\SomeOtherFolder\"
 
Dim wb As Workbook
 
Dim strFile As String
 
strFile = Dir(PTH & "150kg*")
 
If Len(strFile)>0 Then
   Do
       Set wb = Workbooks.Open(PTH & strFile)
       ' whatever processing code you have
       wb.Close
       strFile = Dir
    Loop Until Len(strFile)=0
End If

 

[Stu Dapples]

Hi Stu

Dir is what you need:

Const PTH As String = "C:\SomeFolder\SomeOtherFolder\"
 
Dim wb As Workbook
 
Dim strFile As String
 
strFile = Dir(PTH & "150kg*")
 
If Len(strFile)>0 Then
   Do
       Set wb = Workbooks.Open(PTH & strFile)
       ' whatever processing code you have
       wb.Close
       strFile = Dir
    Loop Until Len(strFile)=0
End If

Hi there, looks great and I can understand what it is supposed to do, I ran some basic code (select a couple of cells, copy them and bring them back to new book) but when I run the code, it errors saying Comile error, constant expression required....
I put this into a module and named it as a public sub, not sure if that will make any difference?
Thanks for you help,
Stu

 

[Richard Schollar]

I am afraid I have no idea without seeing what code you are using

 

[Stu Dapples]

Hi there, just a quick note to say THANKS!! I had a tinker with the code and (as usual) it was my error, helps if you dont point the paste to the same cell each time....

Hey ho, every day is a school day!
Many thanks,

Stu