Getting information INTO a macro (Excel 97-SR2 on Windows 98)

[Steve Ball]

I know how to use a macro to enter a value or text into a spreadsheet cell, but I'm more than a little hazy on how to do the opposite: use, say, a column of values (store numbers, employee names, etc.) as input TO the macro, to guide the macro into - for example - branching to various actions. (I'm remembering how I use to do this in 123v2.3; it was a snap. But I can't seem to figure out an equivalent capability in Excel).

The specific situation I have is that I am building a macro that will load downloaded text files from our G/L package into Excel, and convert them into Excel files (or tabs). I need a way to specify to the macro (without changing the macro each time) what file names I want it to load. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

[Andrew Cronnolly]

If you have a list of filenames in cells A1 to A10, the following code will loop throughh the range and get the value from each cell.<pre>Sub LoadFiles()
Dim strFileName As String
For Each cell In Range("A1:A10")
strFileName = cell.Value
'Process Filename Data
Next
End Sub</pre>

Does that help ?

Andrew C

[LegareColeman]

There are many ways to do this, and which one you use depends on your application.

In addition to what Andrew posted, here is a way to display the standard File Open dialog box to get a file name from the user:

<pre>Dim strFileName As String
strFileName = Application.GetOpenFileName
</pre>


You might also want to look at This Thread for some ideas on processing all of the files located in a particular directory.

[Steve Ball]

Looks like this might work. Thanks.