Make me a macro...

May 29, 2009 9:14 AM

ExcelMacroFilter: Am I screwed? I need a macro in a pre-formatted Excel template that will open the Open File dialog box and then, once a file is opened will copy/Paste Special...Values into the template.

This is basically the last piece of a project where we're pulling data from a the back-end of an enterprise tool and dumping it to Excel. Originally we wanted the same JAVA application that pulls the data and dumps to Excel to do the formatting as well, but we ran into limitations in Java itself. (Don't want to go into that here).

So, we now have a huge, unformatted Excel workbook that we need to place into the formatted template. So, the workflow would be something like: Open the template | Run the Macro which prompts the user to choose the source file | Once the source file is open, the macro will Select All and Copy from the source file | Paste Special into the template.

Is this possible? The Select All, Copy, Paste Special part is obviously very easy to record, but how can I prompt the user to specify which file to copy from?

posted by bluejayway to computers & internet (12 comments total)

Whenever I've done what I think you are describing, I've opened the source file and then run the stored macro on the source. Can users select the raw source file first?
posted by hellboundforcheddar at 9:28 AM on May 29

Yes, and that makes much more sense. Thank you.
posted by bluejayway at 10:04 AM on May 29

It should be possible (and relatively straightforward, unless I'm confused), if you save the macro to the "Personal Macro Workbook" (or so it's called in 2007; something similar in other Word versions). So then open the template, and run the macro. The macro would:
1) do a file open call
2) confirm that it's looking at the file that was just opened (something like workbook.active, or something along those lines)
3) select all, copy
4) switch the active workbook to the template
5) paste special

Just did a quick test; looks like you can actually record the whole thing (including opening the file). Then you just have to edit the macro to prompt the user for the file to be opened.

When you open the file, it automatically gives focus to it, so step 2 is taken care of. What you can do is then copy everything, then close the active doc. This will revert focus back to the previous window (which is the template window). So you don't have to manage switching the active document at all.

Sorry..rambled a bit here, just thinking it through as I typed.
posted by inigo2 at 10:06 AM on May 29 [1 favorite]

Is the file to be opened always the same file? If so, you could just hard code the file name into the macro and then you don't have to prompt the user at all.
posted by SuperSquirrel at 12:22 PM on May 29

That is very true. And in that instance, you are able to just record the entire thing, no coding necessary.
posted by inigo2 at 12:24 PM on May 29

The source file will have a data-based name, so hard-coding it would be out. It will have to be something like inigo2 described.
posted by bluejayway at 12:34 PM on May 29

Okay, this may kill the indents, but:

Sub Fancify()

'Get the file path and file name that you're trying to open
FilenameToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    If FilenameToOpen <> False Then
        MsgBox "Open " & FilenameToOpen

        Workbooks.Open FilenameToOpen 'open that file
        Cells.Select 'select all cells
        Selection.Copy 'copy the data
        ActiveWindow.Close 'close that file
        Range("A1").Select 'go to spot in template you want the top left of the data to be
        ActiveSheet.Paste 'paste the data

        newFilename = Left(FilenameToOpen, Len(FilenameToOpen) - 4) & "-fancy.xls"
        MsgBox (newFilename)
        ActiveWorkbook.SaveAs (newFilename) 'prompt them to give a name to this file
        ActiveWorkbook.Close 'close
    Else
        MsgBox "No file selected."
    End If
End Sub

This will autosave the fancy file as "OldFilename-fancy.xls" in the same directory as the original file. Alternatively, if you want to be able to specify the save name and location each time, change the line "ActiveWorkbook.SaveAs (newFilename)" to "ActiveWorkbook.SaveAs". Or you can mess around with the original filename to choose a different default name / location to save the new file.

The one issue this has is when it's closing the original file, if you have a lot of stuff on the clipboard it prompts the user. There's probably an easy to make that popup not happen, but I don't know what it is. An alternative is to copy, then change to the other doc, paste, then change back, then close, but that's annoying.

Damnit I love to procrastinate...
posted by inigo2 at 1:32 PM on May 29

(And yeah, this works in 2007. Hopefully it works in whatever version you're running...)
posted by inigo2 at 1:33 PM on May 29

One more thing -- just realized I left debugging stuff in there; you can remove the two "MsgBox" lines.
posted by inigo2 at 1:34 PM on May 29

The top two; not the bottom one.
posted by inigo2 at 1:34 PM on May 29

Thanks again, inigo2! Working very nicely!
posted by bluejayway at 2:29 PM on May 29

Sweet. Just paying it forward from SQL help I've gotten here. Any issues, feel free to memail or comment here (so it'll show up in recent activity).
posted by inigo2 at 3:51 PM on May 29