Is is possible to IMPORT a sheet from a Desktop File? -- Struggling w/ 2 Obstacles

I found something that looked similar to what I need to achieve but 2 obstacles I'm not sure how to overcome.. (1) The DESTINATION file will always have various file names so I can't hard-code in a specific file name to copy/import into... however, the DESTINATION file will ALWAYS have a tab called: "Repair Data".

Can the code be written to look for any file open that contains a sheet called "Repair Data"?
The analyst should only have their main working excel file open at the time, so it should be safe.
If so, the DESTINATION issue can be resolved! - but how?

My 2nd Obstacle/Challenge is:
Each analyst that would use this icon-driven macro will have a SOURCE path that will look slightly different depending upon the employee number they've been assigned.
For example: My path looks like this: C:\Users\1359661235E\Desktop\Datafile.xlsx
The analyst next door looks like this: C:\Users\1777359284R\Desktop\Datafile.xlsx
Can this underlined portion of the path be replaced by some sort of wildcard code like "*.*" to get it to accept the user's path no matter who they are? Something like this? C:\Users\*.*\Desktop\DATAfile.xlsx
If so, the SOURCE PATH issue can be resolved! - but how?


Here's what I was trying to start from - but any better ideas are much appreciated:

*There's only one sheet within the Desktop SOURCE file and it will always be called: "Financial"
*The SOURCE file will always be located on the Desktop and will always be called: "Datafile.xlsx"

Please Login or Register  to view this content.

Hello

Because your example was not very clear, I wrote two macros. The first will check all open workbooks for a worksheet named "Repair Data". The second will add the user's desktop path to the file names.

Locate the Repair Data Worksheet

Please Login or Register  to view this content.

Add the User's Desktop Path to the File Names

Please Login or Register  to view this content.

Since it's Valentine's week, I'll use an example that might better explain my need:

The Financial Analyst manages several Gift Shop Client files.
This analyst has a file already open called: "ABC GIFT SHOP INVENTORY"
He needs to extract a single sheet from a closed file that is stored on his Desktop on a regular basis that holds ever-changing pricing.

The analyst will push an icon on his toolbar that will run code that will:
1. Navigate to the user's Desktop
2. Locate a file on the Desktop that will always be called: "MARKET_PRICING.xlsx"
3. Open that Desktop file
4. Copy a sheet from that Desktop file called: "Fresh-Cut-Flowers"
5. Into the already open file called "ABC GIFT SHOP INVENTORY"
6. Close the file that was opened from the Desktop.

The obstacle is... NORMALLY, in the code you would define your SOURCE file ("MARKET_PRICING.xlsx") (closed sitting on Desktop)
and define your SOURCE sheet ("Fresh-Cut-Flowers)
and you'd define your DESTINATION file ("ABC GIFT SHOP INVENTORY") -- this is the one that's already open


The problem is, that the code needs to be generic enough to NOT DEFINE a specific DESTINATION file name (the one that's already open in the very beginning) because it will always be different as he works with different clients with different file names.

Examples of Various Client File/Destination File Names that Will Vary:
"ABC GIFT SHOP INVENTORY"
"XYZ I-FILE"
"123 GIFT AND CANDY SHOP INV"

The "known" that will always be the same is --- that all DESTINATION files will already have a file sitting it in called "Repair Data"
So, when the code needs to know where to paste the copied sheet, it can do the following:

The analyst will push an icon on his toolbar that will run code that will:
1.Verify that a file is already open that holds a sheet called: "REPAIR DATA"
...IF NO, produce message "DESTINATION" file is not open (and stop the code).
...If YES, proceed to Navigate to the user's Desktop
2. Locate a closed Desktop file that will always be called: MARKET_PRICING.xlsx
3. Open that file
4. Copy a sheet from that file called: "Fresh-Cut-Flowers"
5. Into the already open DESTINATION file called "ABC GIFT SHOP INVENTORY"
6. Close the file that opened from the Desktop.

Please Login or Register  to view this content.

Hope that helps!
Thanks so much for helping figure this out!