Wildcard in File Name

[Guest]

In a macro while using the TransferText action, can I use a wildcard in the
File Name? I want to import a text file, but I hate to keep the text file
name specific. Thanks.

 

[Ken Snell [MVP]]

No. You must provide a specific path and file name.

I posted one method in the macros newsgroup a while ago. It assumes
that all files are in the same folder, and all end with ".txt" extension. It
also uses a "hidden" form that is opened by the macro to store the filename
info as the macros do their things -- the concept is based on using the Dir
function recursively (just as it's done in VBA), and using a textbox on that
hidden form to store data for the macro to read.

Create a form (name it "HiddenForm") that has a single textbox (unbound) on
it (name the textbox "txtFile").

Create the two macros shown below. Change arguments' information to match
your setup.

To run the process, you would run MacroStart.

----
MacroName: MacroStart

Condition: (none)
Action: OpenForm
Form Name: "HiddenForm"
Mode: Hidden

Condition: (none)
Action: SetValue
Expression: Dir("C:\MyFolder\*.txt")
Control Name: Forms!HiddenForm!txtFile

Condition: (none)
Action: RunMacro
Macro Name: MacroGet
Repeat Expression: Len(Forms!HiddenForm!txtFile & "") > 0

Condition: (none)
Action: Close
Object Type: Form
Object Name: HiddenForm


(end of MacroStart)

----

MacroName: MacroGet

Action: TransferText
File Name: ="C:\MyFolder\" & Forms!HiddenForm!txtFile
(other arguments as appropriate)

Action: SetValue
Expression: Dir()
Control Name: Forms!HiddenForm!txtFile


(end of MacroGet)