open all files in a folder with fixed number of characters in filename

**Telemick** · 01-14-2012, 01:31 PM

Hi all, I am fairly new to VBA but keen to learn.

I want to open all the files in a folder whose filename consists of a certain number of characters. The filename will be either 6 or 8 characters long. I have two macros and would like one of them to open all the 6 character filenames and ignore the 8 character ones, while the second macro should only open 8 character filenames. I have found through the forum how to open all files of a given type eg "book1*.xls" but am struggling to be specific on the filename length.

Can anyone advise?

Thanks

Mick

**pike** · 01-14-2012, 03:39 PM

Hi Telemick,
And welcome to excel forum
what is the code you are using?

**Leith Ross** · 01-14-2012, 04:40 PM

Hello telemick,

Does the character count refer to the file name with or without the extension?

**Telemick** · 01-14-2012, 04:41 PM

Hi Pike,

Apologies if this reply has been posted several times but I'm not sure if I'm driving this thing properly! Third try.

Code is as follows

Please Login or Register  to view this content.

However, I want to open any file of the form 20120101,20120102 or alternatively 201201,201202 rather than all files called "Book*.xls" in the example code.

Thanks

Mick

**Telemick** · 01-14-2012, 04:51 PM

Hi Leith,

The 6 or 8 character filename does not include the file extension. In case it might be important, the files are excel comma separated values so the extension is csv.

Apologies for not wrapping my code properly in my reply to Pike.

Thanks for your interest Leith

Mick

**shg** · 01-14-2012, 05:30 PM

Apologies for not wrapping my code properly in my reply to Pike.

Please take a few minutes to read the forum rules and correct that.

Thanks.

**Telemick** · 01-15-2012, 05:49 AM

I think this is the accepted format. Regards

Please Login or Register  to view this content.

**pike** · 01-15-2012, 06:29 AM

Hi Telemick
I dont have FileSearch in xl 2010, but Steve may

**shg** · 01-15-2012, 03:54 PM

FileSearch went away in Excel 2007, so maybe like this (untested):

Please Login or Register  to view this content.

**Telemick** · 01-16-2012, 08:01 AM

Hi SHG,

Thanks for the code. I'll give it a try and let you know how I get on.

Mick

**Telemick** · 01-16-2012, 05:18 PM

Hi SHG,

I've tried the code but come unstuck! It goes into an infinite loop between the Do While and Loop statements.

Using the F8 command I stepped through it and found the first bit worked fine:

Please Login or Register  to view this content.

When the sfile line has run, the correct path and file name for the first file in the directory are shown when I hover the mouse over DIM sfile.

The next bit of code is as follows:

Please Login or Register  to view this content.

Once the cursor has passed the "If spath & sfile..." line it jumps straight to "End if" without ever opening the file and now it is in a loop that it can't escape from.

The other thing I noticed is that in the line

Please Login or Register  to view this content.

the value of ThisWorkbook.FullName is C/........Excel/XLSTART which is where the macro is stored. I have no idea if this what you intended.

Thanks for your efforts so far. Just to confirm, it is Excel 2003 I am using (re the comment about filesearch gone in 2007)

Best Regards

Mick

**pike** · 01-17-2012, 01:52 AM

Hi Telemick
Move the sFile bit out side the if to just before the loop

Please Login or Register  to view this content.

**shg** · 01-17-2012, 12:57 PM

Thanks, pike.

**Telemick** · 01-17-2012, 02:40 PM

shg and pike - you have done it!

Thanks a million.

Mick

**pike** · 01-18-2012, 03:31 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

open all files in a folder with fixed number of characters in filename

LinkBack

Thread Tools

Rate This Thread

Display

open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Re: open all files in a folder with fixed number of characters in filename

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions