How do i open all Excel file types, not just .xls but .xlsx?

[Julesdude]

Hi all,

I'm using the following string before opening a folder of Excel files one by one and extracting data from them:

strExtension = Dir("*.xls*")

The trouble is by adding the * at the end it is not opening all Excel files - i.e. it doesn't open xlsx files. But it seems to open .xls no problem.

Is there a way i can use this string variable to open all Excel file types? I'm using Excel 2007.

 

[Joe4]

Can you post the part of the code that actually opens the files?

 

[Julesdude]

Hi Joe, sorry for delay. The current code I am using is as follows:

Dim wbOpen As Workbook
Dim wbNew As Workbook
Dim strPath As String
Dim addstrpath As String
Dim strExtension As String

strPath = "C:\Folder1\"
addstrpath = "C:\Folder1\"
ChDir strPath
 
strExtension = Dir("*.xls*")
 

Set wbNew = Workbooks("Workbook1.xls")

Do While strExtension <> ""
    
    Set wbOpen = Workbooks.Open(strPath & strExtension)
         
    wbOpen.Activate
    wbOpen.Sheets(1).Select


'-----HERE GOES THE CODE TO EXTRACT CELL DATA FROM SOURCE

    With wbOpen
        .Close SaveChanges:=False
    End With
    strExtension = Dir
Loop

 

[Joe4]

I set up a directory with an "xlsx" and and "xls" file, and it opened it each one just fine. Note that it opens and closes them one at a time. I also had to comment out one line of your code:

Set wbNew = Workbooks("Workbook1.xls")

Not really sure what that is doing, but imagine it may be related to other things you are doing with your code.

I would recommend resizing your VBA code to about one-quarter the screen size so you can see your VB Editor Window and Excel Spreadsheet at the same time. Then press F8 to step through your VBA code one line at a time. Keep doing this so you can see your code run one step at a time. When I did this, I saw both files being opened and closed.

 

[Joe4]

One thing I did notice. If this macro workbook is in a different drive than the files it is opening, you may need to add the following line of code:

strPath = "C:\Folder1\"
addstrpath = "C:\Folder1\"
[B][COLOR=red]ChDrive Left(strPath,3)[/COLOR][/B]
ChDir strPath

Similarly, this would work:
ChDrive "C:\"

Either way, it won't hurt to put it in there.

 

[Julesdude]

Thanks for taking a look Joe. Stumped as to why it was giving me a problem. I'll test a little more and see what happens. And you're method of F8-ing through line by line is very useful. I'll give that a try too.

 

[Julesdude]

Hi Joe, they're not on different drives, but they are in different folders and I have had other issues with the code 'finding' the specified path even though it's clearly there. This hasn't happened in the last day or so but has happened previously and I still don't know why. I have put the C drive in this example. However the actual code is accessing from a network drive and path. The network is stable and everything - it works on my computer but not on my colleagues. Again I find that odd and can't explain it. For him, it didn't like the file directory or the .xlsx files. So I got rid of the '*' and had just '*.xlsx' instead of '*.xls*' and that worked ok.

 

[Joe4]

One thing to consider is that different computers may have different network folders mapped to different drives. For example, what my "F" drive is mapped to may be different that what my co-workers "F" drive is mapped to. It is a common problem we have here. Many times, we resort to using UNC naming instead of using drive letters.