Make me a macro...
May 29, 2009 9:14 AM Subscribe
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?
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?
Response by poster: Yes, and that makes much more sense. Thank you.
posted by bluejayway at 10:04 AM on May 29, 2009
posted by bluejayway at 10:04 AM on May 29, 2009
Best answer: 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, 2009 [1 favorite]
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, 2009 [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, 2009
posted by SuperSquirrel at 12:22 PM on May 29, 2009
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, 2009
posted by inigo2 at 12:24 PM on May 29, 2009
Response by poster: 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, 2009
posted by bluejayway at 12:34 PM on May 29, 2009
Best answer: Okay, this may kill the indents, but:
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, 2009
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, 2009
(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, 2009
posted by inigo2 at 1:33 PM on May 29, 2009
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, 2009
posted by inigo2 at 1:34 PM on May 29, 2009
Response by poster: Thanks again, inigo2! Working very nicely!
posted by bluejayway at 2:29 PM on May 29, 2009
posted by bluejayway at 2:29 PM on May 29, 2009
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, 2009
posted by inigo2 at 3:51 PM on May 29, 2009
This thread is closed to new comments.
posted by hellboundforcheddar at 9:28 AM on May 29, 2009