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

J

Julesdude

Board Regular
Joined
Jan 24, 2010
Messages
197
Hi all,

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

Code: 
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Can you post the part of the code that actually opens the files?
 
Upvote 0
Hi Joe, sorry for delay. The current code I am using is as follows:

Code: 
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
 
Upvote 0
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:
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.
 
Upvote 0
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:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
remove columns from multiple Excel-files in a folder
Replies
5
Views
279
gjvv
G
N
  • Question Question
Always open xlsx files in the already-open instance of Excel
Replies
2
Views
452
nevyoung
N
I
  • Question Question
Unable to Open or Copy Corrupted Excel Files on Network Drive
Replies
1
Views
192
Candyman8019
C
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
151
howard
H
M
  • Solved
Can't open a new xlsx file with Excel 2007
Replies
3
Views
259
RoryA
RoryA

Forum statistics

Threads
1,224,524
Messages
6,179,308
Members
452,904
Latest member
CodeMasterX

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top