Results 1 to 4 of 4
  1. #1
    Steve Ball

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

    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>

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Gold Lounger
    Join Date
    Feb 2001
    Dublin, Ireland, Republic of
    Thanked 0 Times in 0 Posts

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

    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
    End Sub</pre>

    Does that help ?

    Andrew C

  4. #3
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

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

    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

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

  5. #4
    Steve Ball

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

    Looks like this might work. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts