Never thought this would be my biggest headache today.
Short version: I spent all day yesterday doing test runs and logging data (for which the physical setup took the first part of the week). Today I'm trying to sort the massive pile of log files into something that's convenient for analysis.
Problem is, the software that was logging the realtime data is an old, unsupported POS with very strange filing procedures. Essentially, for a test names "Test1", it dumps out a dozen+ files named Test1.xxx, where xxx can be, well, a LOT of different things. And every additional test gets all its files dumped in that same directory.
And, just for grins&giggles, the data I need are always in the file that has no extension.
So, today I've been bashing (ahem) together a VBA script in Excel (sucks, but it's what I've got) that opens a user dialog, sucks up the file, and rearranges the data into nice columnar format for further processing. But I'd like to have the file-select box filter out all the files that have extensions, so that when the user selects the directory where all the files from all of the tests got dumped, the user will only see the files that have no extension.
And this is where I'm stuck. Googling around I've found how to have the box filter for any extension I like, except the no-extension-at-all condition. I just can't seem to find the correct magic combination of search terms to lead me to what I need.
Anybody know how to do this?
Can you filter for no "."?
I don't know. If I was processing a list of filenames in code, sure. But I'm trying to get the standard VBA file-select dialog box to do this, and I haven't a clue about the inner workings of that thing.
I suppose you could be a script to hide every filename with an extension...
Well, I know for certain that you can set up a Java FileFilter to parse out just about anything you want based on the file name (extensions being any filename ending with ".xxx"). However, seeing as you're already working in Excel, I'm guessing you're looking to do a lot more work on the files and likely run several through at once. I can't say for sure with out knowing what you're trying to do with what's in there, but as most logs are simple wall-o-text files, you probably can get analysis done once you've got your file(s) selected.
---
The Master said: "It is all in vain! I have never yet seen a man who can perceive his own faults and bring the charge home against himself."
>Analects: Book V, Chaper XXVI
Doing the text file processing isn't a problem, I've got that sorted. What I'm doing is, when the VBA macro is started, it opens a standard Windows VB file-select window for the user to select the file s/he wants to work on. Now, in my VBA code, I can set the "file types" box to only one extension, or several, and there's lots of information on-line about doing that. I can have the visible files only show .txt, or .txt and .mp3, or whatever... but so far, everything I've tried to make it only show the files with no extension generates a runtime failure.
This isn't really a critical part of the script, but given that I'm building this script for someone else whose VBA skills are even weaker than mine (and mine ain't too hot), and that any given file name can show up a dozen+ times in the file list with different extensions, not to mention that so far, the only way to get the extensionless files to even be visible is to set the box to *.*, and that all the different tests get stored in the same directory (we can't control the logging software at that level), well... it would just be a Really Nice feature if I could filter out all the files that this script isn't supposed to deal with. It's an ergonomic thing, more than anything else. I need the filtering to take place in the file-select box, which, as I understand it, is a completely standard Windows VB control (form?). My problem is that I'm trying to get it to do something that, from my research so far, seems pretty atypical. It's possible that the standard Windows file-select box just won't support what I want to do.
Did you try to go 'behind the scenes' with a DOS window and try searching that way?
_____
DEATH is Certain. The hour, Uncertain...
Looking through the Visual Basic API (damn they make it hard to find) for their entry on the
Application.GetOpenFilename Method, it looks like the FileFilter is only a string argument here, and designed to specify what extensions
to display. No allotment for what extensions to
not display.
I don't think you're gonna get this one working right, using this. The method is just not made to handle no-extension files.
---
The Master said: "It is all in vain! I have never yet seen a man who can perceive his own faults and bring the charge home against himself."
>Analects: Book V, Chaper XXVI
It makes sense for VBA scripts to be unable to do this as mirosoft considers extensions important, for some silly reason.
Well if you want to add more steps you could use an extension fixup script that gives all files without an extension one (I would use perl), then set the filter to only show the files with that new extension. I recommend the *.SkyeFire extension
E: "Did they... did they just endorse the combination of the JSDF and US Army by showing them as two lesbian lolicons moving in together and holding hands and talking about how 'intimate' they were?"
B: "Have you forgotten so soon? They're phasing out Don't Ask, Don't Tell."
You should be able to use a filename filter like so: "*."
Testing on my local machine, that works, but I'm assuming when you say VBA that you're talking about the standard Windows File Open/Save dialog box, accessible via API and by ActiveX objects from VBA. If you're talking about something else, I'm not sure if it'll accept that string.
--sofaspud
--"Listening to your kid is the audio equivalent of a Salvador Dali painting, Spud." --OpMegs
Yeah, VBA is Visual Basic for Applications, which I guess comes with MS Office, but it looks like only the typical Windows "File Open" dialog box is available unless I want to start writing standalone VB and calling it from the VBA macro/script/whatever.
'*.' was in fact the first thing I tried, but the result was that the dialog box came up showing '*.*', which I guess makes sense from a M$ viewpoint. Gah.
I think I may be stuck following Catty's suggestion about using a separate script to add a unique extension to the extensionless filenames. Since I know there are already some pre-existing tools (non-M$ Office) that depend on those files having the original filenames, probably what I'll do is make copies. I'd been hoping to avoid that, though, so I could keep it all as a single self-contained Excel Macro. Still, I'm almost to the point of diminishing returns here -- this little R&D morsel is under-funded, so I need something quick and dirty, even if it's a lousy hack from a programmer standpoint.
Does VBA have support for regular expressions? Hmm, quick web search suggests RE was added around 2002 or so. If yours is new enough, then what we need is an RE that searches for filenames that end with "."
If I am reading things right then the RE filter you want is: .$
The slash changes the period from representing its default 'any single character' to searching for an actual period, and the dollar sign says the match must come at the end of the string being examined.
Doing a quick Google search I found what may be a relevant page on
Stack Overflow. Unfortunately I don't have an MS Office new enough to support RE on any of the computers at home, so I can't test what they're showing (desktop is still loaded with Office 97, of which Excel is acting up). But one of the answers looks at going through a file list and the last one gives a snippet of code for applying a RE filter. Only thing I could test it on right now would be Java (and by coincidence, this week's assigned chapter in my class is on File I/O).
And, as we have now invoked regular expressions we must of course link to the relevant XKCD:
Everybody stand back! I know regular expressions!
-----
Will the transhumanist future have catgirls? Does Japan still exist? Well, there is your answer.
Ah, okay, I see (I think) the problem. First off, let me address one thing: VBA and VB are -identical-. Identical in capabilities, at any rate, with the sole exception that you can't compile code in VBA. That said, they ship with different included bits, and that's what's caught you out here.
You're using the embedded control that came with Office, it sounds like. Which -- by design -- does not support files with no extension. Microsoft has known about that little gotcha since 2003 and as far as I know it's still listed as 'under review', which basically means it'll never be fixed.
That said, there's no reason VBA can't access and use the built-in Windows File Open dialog (part of the Windows common dialogs) via API and provide you complete, full, accessibility. The downside to this is that, like with any Windows API, you're going to be using significantly more code. I can attest that it works -- I've done it -- but I'm not sure if you want to go this route.
If you do, check out Randy Birch's site (
http://vbnet.mvps.org/ind...lg/filedlgsoverview.htm) for the articles about the File Open dialog. The site is written with VB in mind, but all of the examples work in VBA, sometimes with a little bit of tweaking (not usually, though).
That said, I think a faster/easier route would be to use a batch rename. A batch file that does the following, for example:
Quote:REN *. *.FOO
You can call this batch file from VBA quite easily:
Quote:SHELL("foo.bat")
So in your VBA code, call the batch file before you open the file dialog, and then set the file dialog to only show files of type "*.FOO".
I think this would be easier and faster than any of the other proposed solutions. Also, if you're really keen on keeping the files intact, you can reverse the process with another batch file that renames *.FOO to plain *.
Make sense?
--sofaspud
--
"Listening to your kid is the audio equivalent of a Salvador Dali painting, Spud." --OpMegs