VBA needed to copy data from an open unsaved attachment to existing workbook

[rob73]

Hi all,
I am a bit of a novice so please make any responses easy to follow if possible.
I receive a report each day as an attachment. I want to be able to open this attachment without saving it and then have a macro in my existing workbook to identify this open sheet and copy a range of cells and then paste this into a range of cells in my existing workbook.
It will always be the same range copied and the same range pasted into.
I just cannot seem to find a code that will do this. The attachment also has a different name each time so the only way to tell Excel which one it is would be to say it is the only other open spreadsheet other than the existing workbook.
I am new to this forum so be gentle with me. LOL.
Any help would be very much appreciated as I am pulling my hair out and I don't have much to spare.

Thanks in advance

[JRidge]

Bit more Information Required...

Names of Workbook 1 and Workbook 2 (Example).
Worksheet to copy from.
Range to Copy.
Worksheet to Paste to.
Range to Paste to.

Up load an example from both Workbooks.

Regards

JRidge

[rob73]

Hi JRidge

0the main sheet that is saved and to be copied to is called "Universal BF Tool" and the attachment to be copied from is called "Report...(The end of this name varies each time by date,etc)

I have the tool set up to take all the entries pasted into it and filter out only those that need to be looked at. This all works fine but as many users will be using this I want to automate the pasting in too to avoid people copying too many columns, not enough columns, etc, overwriting formulae or whatever.
So I want people to open the attachment and then open the tool, hit a button and have the entries copied and pasted into the sheet and then close the attachment.
The range on the attachment to be copied will be Sheet 1(A15:K10000) and the range to be pasted into will be Sheet 1(named Input Sheet)(K2:U10000
I cannot upload examples. Sorry, but hope this info is enough

I appreciate you taking the time to help me

[JRidge]

Where would these files be..

Same Folder?

[JRidge]

As a Start:

Paste this in a Standard Modul in the Destination Workbook.

Please Login or Register  to view this content.

Close VBA.

Alt+F8 and then click Run.


Just need to work out how to set the Source Workbook title as Report*wildcard.

Regards

JRidge

[JRidge]

Would the Report be the newest file in the folder?

[rob73]

No the copy from workbook would be an unsaved open attachment and users would have their own versions of the tool saved in their own folders.
This is why I was hoping there was a way to just have a code that referenced the only other open workbook on the desktop which would be the open attachment and then copy the relevant cells from it to paste in the workbook containing the macro.
Is it not possible to reference workbooks unless they are saved somewhere? As this would mean users would need to save the attachments with a specific filename in the same folder as the tool and then create a version for each person referencing the folder they are using. But that would not be very practical and I have seen codes for using unsaved files but could not amend ant of them to make them work for me.

[JRidge]

But isn't the Report saved somewhere on the System. They must have opened it from somewhere?

[JRidge]

Ok easy....

Paste this in Module in Destination Workbook:

Please Login or Register  to view this content.

The Source Workbook needs to be opened second. First open the Destination Workbook and then the Source Workbook.

[rob73]

Yes , sorry it would be in their outlook inbox attached to an email called Webmaster....(again tis would change each day. Could we tell Excel to look for an email with the subject Report* received today in the inbox, open the attachment and extract the data?

[JRidge]

See Post #9

[rob73]

OK Will try this and get back to you. Thanks so much

[rob73]

JRidge
You are an absolute star.
That worked beautifully.
Thank you so much for taking the time to help me with this.
I really appreciate it.
Enjoy the rest of your weekend
Take care

[JRidge]

Perfect.

If the Solution is what you wanted the please mark this thread as solved. (Top of Thread, under "Thread Tools").

Also if you think i helped Please don't forget to add reputation using the * in one of my posts..

Regards

JRidge

[Pan314]

Paste this in Module of copiedToWorkbook:


Public Sub CopyDataFromAnOpenUnsavedAttachmentToExistingWorkbook()
'******Supposition: In Excel two workbook is opened(copiedToWorkbook, copiedFromWorkbook) and one workbook has one worksheet.******
Const copiedFromWorkbookName_FirstCharacters As String = "Report"
Const copiedToRangeName As String = "K2:U10000"
Const copiedFromRangeName As String = "A15:K10000"
Dim copiedTo, copiedFrom As Range
Dim copiedToWorkbook, copiedFromWorkbook As Object
Set copiedToWorkbook = ActiveWorkbook

For Each openedWorkbook In Workbooks
'MsgBox openedWorkbook.Name
Select Case openedWorkbook.Name
Case copiedToWorkbook.Name
Case Else
If Mid(openedWorkbook.Name, 1, Len(copiedFromWorkbookName_FirstCharacters)) = copiedFromWorkbookName_FirstCharacters Then
Set copiedFromWorkbook = openedWorkbook
Else
End If
End Select
i = i + 1
Next openedWorkbook
'
Select Case i
Case Is > 2
MsgBox "Too many open files!", vbInformation
Case 1
MsgBox "No attachment opened!", vbInformation
Case 2
answer = MsgBox("Attachment file: " + copiedFromWorkbook.Name, vbYesNo)
If answer = vbYes Then
Windows(copiedFromWorkbook.Name).Activate
Range(copiedFromRangeName).Select
Selection.Copy
Windows(copiedToWorkbook.Name).Activate
Range(copiedToRangeName).Select
ActiveSheet.Paste
Else
Exit Sub
End If
End Select

End Sub