Code to import worksheet using dialogue box

Hi there,

I'm looking for some code that will import an Excel worksheet that has been exported from a DB. Essentially, the macro should do the following upon execution:

1. Display file-open dialogue box to allow selection of a single file
2. Copy worksheet from within selected workbook ' there will only ever be one worksheet in workbook and will always be called "Sheet 1", as per how the exporting DB operates
3. Close imported file '' it would be beneficial if user doesn't actually see the open/closing to give a smoother UEX
4. Name "Sheet 1" as x


I then have subsequent macros to derive values, format, prepare for publication etc.


Any help would be most appreciated!



Thanks,

Rob.

Sorry, meant to provide that detail. It should be pasted as the last sheet in the workbook that the macro is being executed.

Original sheet should not be removed.

Copied and pasted as a whole sheet, so effectively a new sheet in the existing workbook from where the macro has been executed.



Rob.

.
.
Try this code :

Please Login or Register  to view this content.

Thanks. That was really useful.

Would you be able to show me how to do "inverse", i.e:

1. Open dialogue box
2. Set save path and filename
3. Move selected worksheet to new file
4. Save file as filename and to path defined in step 2
5. Close saved file
6. Confirm export to user


Rob.