VBA File Extension

**chbrandt** · 08-02-2010, 06:13 PM

I've got a macro that I've pieced together from different sources on the web designed to combine a lot of excel workbooks with single worksheets into one workbook with many worksheets. My problem is, the extension used in the VBA is .xls, causing me to have to go in manually each time the extension is something else, say, .xlsx, and change the code before I can put the files together.

Does anyone know how to make this tweak so that I can have multiple file extensions automatically checked? Maybe even a list of them could be added to the first sheet under Accepted File Extensions so that someone else who was using it could add theirs if it wasn't already in the list without having to open up the VB editor?

I've attached the file. Thanks

******EDIT******

It doesn't seem to work at all when I go from .xls to .xlsx, although .dat and .rep both work. Not sure what causes this either.

**stanleydgromjr** · 08-02-2010, 07:17 PM

chbrandt,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

**chbrandt** · 08-02-2010, 07:39 PM

Thanks for your help. Unfortunately, this fix didn't work, I'm getting the same error with that as I was getting when I tried .xlsx. Is it because of the vbNormal? Something with the newer version excel file is causing it not to work. Any ideas?

Thanks in advance.

**stanleydgromjr** · 08-02-2010, 08:19 PM

chbrandt,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

**chbrandt** · 08-02-2010, 08:26 PM

Still getting Error 400 with an OK and HELP option, and haven't been able to figure it out from there. Have you tried experimenting with the file? Thanks

**stanleydgromjr** · 08-02-2010, 10:54 PM

chbrandt,

Please Login or Register  to view this content.

When I changed the file extensions of the workbooks to something else, the macro did not error out, and no files were processed.

**chbrandt** · 08-02-2010, 11:20 PM

Thanks a lot, I figured out that it was something to do with the actual files I was trying to open and merge. Thanks again.

**royUK** · 08-03-2010, 01:25 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save