Use VBA to paste the values of a protected sheet

[thorwood]

Hi,
the following code copies and pastes any excel attachments from a subfolder (called "Test Folder" in Outlook, into individual new excel workbooks under the path Y:\Scripts3\copy, the first being copy1, the second copy2, etc.

However, I would like them to all go in 1 worksheet, one after another, not separate workbooks. Does anyone know how to do this?

Many thanks,
Tim

Sub SaveAttachments()
Dim myOlapp As Outlook.Application
Dim myNameSpace As Outlook.Namespace
Dim myFolder As Outlook.MAPIFolder
Dim myItem As Outlook.MailItem
Dim myAttachment As Outlook.Attachment
Dim I As Long

Set myOlapp = CreateObject("Outlook.Application")
Set myNameSpace = myOlapp.GetNamespace("MAPI")
Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myFolder = myFolder.Folders("Test Folder")

For Each myItem In myFolder.Items
If myItem.Attachments.Count <> 0 Then
For Each myAttachment In myItem.Attachments
I = 1 + 1
myAttachment.SaveAsFile "Y:\Scripts3\copy" & I & ".xls"
Next
End If

Next
End Sub

 

[thorwood]

Also, as per the title of my question...the sheets I am copying are actually protected, in that the data cannot be overwritten. Ideally I would like the copies of these sheets to be completely unprotected. I'm not sure if this is possible? The above script simply makes a direct copy of the data in the attachment.

 

[SkipVought]

Hi,

Depending on your version of Excel, various this are protected or not on a sheet.

The best approch is to OPEN, UNPROTECT, COPY, CLOSE the workbook without saving, PASTE.

    Dim oFS, oFD, oFL, ws As Worksheet
    
    Set ws = ActiveSheet
    
    Set oFS = CreateObject("Scripting.FileSystemObject")
    For Each oFL In oFS.GetFolder("C:\Documents and Settings\ii36250\Desktop").Files
        With Workbooks.Open(oFL)
            .Unprotect
            .Sheets(1).UsedRange.Copy
            ws.Cells(ws.[A1].CurrentRegion.Rows.Count + 1, 1).PasteSpecial xlPasteAll
            Application.DisplayAlerts = False
            .Close
            Application.DisplayAlerts = True
        End With
    Next
[code]


Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[thorwood]

Hi Skip

Thanks for your replies...I'm a complete VBA novice though!!!

How would I incorporate your code into my code above? I.e I want this macro to run in Excel, but only search Outlook for emails with attachments in "Test Folder", which is a sub-folder of Inbox. I only want it to save to Y:\Scripts\

It is Excel 2003.

Thanks for your advice

Tim

 

[SkipVought]

This just puts all the data in the FIRST SHEET of each workbook in "C:\Documents and Settings\ii36250\Desktop" (change to suite) in ONE WORKBOOK, in answer to, " I would like them to all go in 1 worksheet."

Once in that workbook, save it and attach to your eMail.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[thorwood]

Aha, great. That sounds what I need. Incidentally, what is I decide I do not want ALL of the data from the first page of each workbook? What if I just want the range B10-O29? How would I manipulate the script? Thanks

 

[SkipVought]

'What if I just want the range B10-O29
            .Sheets(1).range("B10:O29").Copy

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[thorwood]

Ok, great.

Just one more thing....the example you have given allows me to copy the first sheet from each workbook in C:\Documents and Settings\ii36250\Desktop. Ideally I'd like to get the information directly from my inbox/Test Folder folder. Do you think this is possible? and how would I do this?

Thanks

 

[SkipVought]

Find the path and substitute.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Do you have WORKBOOKS saved in your inbox/Test folder?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[thorwood]

Hi,

No, basically emails come into my inbox, and a pre-defined rule sends them to inbox/Test Folder. Each has an attachment, which I want to work with.

I would like them opening and copying (unprotected) to my hard drive....which is Y:\Scripts\

So, I didn't think I could just find the path and substitute...I thought I'd have to do more programming than that...

Thanks
Tim

 

[SkipVought]

Well your code is saving the attachments in Y:\Scripts3.

Use THAT path in my code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[thorwood]

Ok, I'm getting a bit confused...I was thinking your path was relating to where the excel data was coming FROM, not TO.

 

[SkipVought]

You have messages in your TEST folder in Outlook.

You have code that loops thru the messages and takes the attachments and saves them in the Y drive.

Now take the Excel files that you have saved on the Y drive and suck them into ONE WORKSHEET and do with it what you want.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}