Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Toronto, Ontario, Canada
    Thanked 0 Times in 0 Posts

    Automating Formulas (2002/XP)

    Any ideas for this one? I'm trying to automate a very clunky process - and was thinking that Macros might be the way to go.

    Right now I recieve expense reports from multiple people. I extract specific cell items, such as totals, depts, etc. and put them into one big database. I can do a 3D formula that might read something like [CER.xls]Expenses!$B$3. This would still be a very manual process as I have to create that formula for several different spreadsheets.

    I've considered incorporating some sort of process whereby spreadsheets are named in some sort of numeric fashion - but this would involve relying on several different people - and the chance of error is too high.

    What I would like to do is have something in place (sort of like a query in Access) where I am prompted to give the file name (that which appears in the square brackets). In doing so, I would like to be provided with a file menu window, where all I need to do is click and point.

    Is anything like this possible? Given the situation ... is there anything else that could be done?

    Any help would be greatly appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Automating Formulas (2002/XP)

    You could use Indirect formulas if the workbooks are open:
    =indirect("['" & filename &"]" & sheetname &"'!" & cellAddress)

    where the info is read from some location. After extracting it you could copy and paste values to have them stored int he database


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

    Re: Automating Formulas (2002/XP)

    You can use the GetOpenFilename method to display the standard Open dialog box and have it return the filename that the user selects or types. The file will not be opened, the name will just be returned. It works something like this:

    Dim vFileName As Variant
    vFileName = Application.GetOpenFilename("XL Files (*.xls),*.xls,All Files (*.*),*.*")
    If vFileName = False Then
    MsgBox "You did not select a file."
    MsgBox "You selected " & vFileName
    End If
    Legare Coleman

Posting Permissions

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