PDA

View Full Version : Search for Excel workbook and open



Kortike
03-24-2016, 12:55 PM
Hi, I am relatively new to VBA and using it for Excel. My company has documents that we recently revised and the revision added 1 row per "section" of information. This makes it a slow and tedious process to use the old school "copy and paste" method of reformatting the pre-revision documents. I have a macro that will automatically transfer the data properly from the old format to the new, but I would like to streamline the process. Currently the user opens the new format template, then opens the excel file they want to update to the new format, and move/copy a single worksheet from the workbook into the new format template workbook. Sorry that's a lot. Then the user can run the macro and all of the data will transfer properly. Ideally I was wondering if someone could assist with some VBA code that would allow the user to input the name of the workbook they want to copy into a form. Then I need the program to search (in sub-directories) for the file name, open the file, move the worksheet to my new workbook AND THEN FINALLY my original macro will run to convert the document to the new format. Most of what I have found mentions Application.FileSearch which no longer works and so I'm kind of unsure where to go from here. Ideally it would be an input box where "example.xls" could be typed and then that file would be searched for in director Z: in subfolders then on and on as I said above... Even just a point in the right direction would be appreciated as far as the file search and open goes.

Thanks,

Eli