Excel VBA Button: Import Data from other worksheet incl. selection of source worksheet

[kingofcamden]

Hi all,

I would like to import data from a closed Excel spreadsheet (.xls, .xlsm or .xlsx) on click into a worksheet called "LOCFile".
On click of the button, it should ask for the file to import from and from which tab/worksheet in the source file (the source file might have multiple worksheets that might differ from file to file so ideally it should ask for the correct worksheet). The data should be copied/imported 1 to 1 and ideally in the source formatting (the file will contain translations so there will be a lot of special characters which should stay the same when copied over).

I've tried several scripts I found online now, but every script is only doing a part of the job (e.g. you can't select which file to import from, it defaults to importing the first worksheet it can find in the locfile, it does not keep source formatting etc.) so quite helpless here :-(

This is a script I found which seems to be working to a certain extend, however, it simply copies the whole workbook (not the content) and adds a new worksheet to the file instead of importing to an existing worksheet.

Quite a noob when it comes to VBA and still learning so any help would be appreciated

Many thanks!

Please Login or Register  to view this content.

[AlphaFrog]

Try this. Set the destination worksheet to suit.

Please Login or Register  to view this content.

[kingofcamden]

Hi Alphafrog,

WOW! I'm impressed!! Did you just code that from scratch? This is simply amazing!! Thank you so much.

Is there any chance to:

• jump back to the worksheet the button is on once the import is finished? Ideally I would put the LOCfile worksheet (that now contains the loaded data) on hide.
• can a message appear "Import completed" once the import is finished?

Other than that, this is EXACTLY what I was looking for
Cheers!

[kingofcamden]

I managed to get the 'import completed' message to show up - wohoo!

Please Login or Register  to view this content.

Also added

Please Login or Register  to view this content.

at the end of the sub to go back to the initial worksheet. Seems to do the trick!

Thanks again for your help

[AlphaFrog]

You're welcome. Thanks for the feedback.

Delete the red line and it will stay on the original worksheet.

Add the blue line for the message.

Please Login or Register  to view this content.

[kingofcamden]

Hi Alphafrog,

Ace! Thanks so much!