DIR Function Work in VBA 2007 But Not in 2003 1

[mmignot]

Hello all,

I have some code that uses the DIR function to loop through excel workbooks in a directory. This code works in VBA 2007, but in VBA 2003 it is not picking up the excel filenames in the directory. Here is a sample of the code:

lCount = 0
sFil = Dir("*.xls")
Do While sFil <> ""
   lCount = lCount + 1
   Set inputWB = Workbooks.Open(InputDIR _
                                & "\" _
                                & sFil)
   inputWB = ActiveWorkbook

   ' *----------------------------------*
   ' * Retrieve Data                    *
   ' *----------------------------------*
   ' *
      Range("E8").Select
      COUNTY_ID = ActiveCell.Value 
      .
      .
      .                    
      .

Any help in determining the problem would be appreciated!!

Thanks!

 

[mmignot]

Hello all,

Here is a better example of the code I'm using:

   lCount = 0
   sFil = Dir("*.xls")
   Do While sFil <> ""
      lCount = lCount + 1
      Set inputWB = Workbooks.Open(InputDIR & "\" & sFil)                   
      inputWB = ActiveWorkbook

   ' *---------------------------------------------*
   ' *  Retrieve input fields from each form.      *
   ' *---------------------------------------------*
   ' *
           ' *----------------------------------*
           ' * Retrieve CNTY ID                 *
           ' *----------------------------------*
           ' *
              Range("E8").Select
              CNTY_ID = ActiveCell.Value
              
           ' *----------------------------------*
           ' * Retrieve State ID                *
           ' *----------------------------------*
           ' *
              Range("E7").Select
              State_ID = ActiveCell.Value
              
           ' *----------------------------------*
           ' * Retrieve City ID                 *
           ' *----------------------------------*
           ' *
              Range("E9").Select
              City_ID = ActiveCell.Value
              
           ' *----------------------------------*
           ' * Retrieve Zip Code                *
           ' *----------------------------------*
           ' *
              Range("I12").Select
              Zip_Code = ActiveCell.Value
              
                 
           ' *----------------------------------*
           ' * Close Supplemental form.         *
           ' *----------------------------------*
           ' *
              inputWB.Close SaveChanges:=False
                 
           ' *----------------------------------*
           ' * Get next record.                 *
           ' *----------------------------------*
           ' *
              sFil = Dir
        Loop

End Sub

 

[mmignot]

Okay, I was able to resolve this issue. I just changed the following code:

sFil = Dir("*.xls")

to:

sFil = Dir(InputDIR & "\" & "*.xls")

This solved my problem and I was able to run the code in both Excel 2003 and 2007.

Thanks!

 

[fumei]

Do you know why?

sFil = Dir("*.xls")

passes to VBA what the application (Excel in this case) assumes as the default folder. So, it becomes:

sFil = Dir(assumed_PATH & "*.xls")

2003 and 2007 make differnt assumptions as to default path.

That is why is always best to be explicit, and fully qualify things.

sFil = Dir(InputDIR & "\" & "*.xls")

passes a full (explicit) path statement.


unknown

 

[mmignot]

Hi Fumei,

Many thanks for your explanation and added input. I knew that 2007 was interpreting the function differently than 2003, I just didn't know exactly why. Your explanation adds clarity. I must remember to be explicit.

Thanks again!

 

[fumei]

I must remember to be explicit."

Generally speaking, a good idea!


unknown