Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have several files in a directory named C:\Workfile\Z-Accnts

    What I need is a macro to extract the worksheet called summary from the workbooks named M_*.*.xls or M__*.*.xlsx and to save these extracted worksheets , to contain the name of the workbook from which they were extracted from followed by the for eg M_BRAN1.summary.xls" I then need all these files to be inserted into an email with a subject heading "Summary Profit Figures" and the main detail to contain

    Attached please find summary profit figures

    Regards

    Howard

    Your assistance will be most appreciated

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Howard, if you search this Forum you will find several threads which cover this for you.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi John

    Thanks for the reply. Have tried to find code to suit my needs, but could not

    It would be appreciated if you could point me in the right direction

    Regards

    Howard

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    MS MVP Ron DeBruin has some code examples at http://www.rondebruin.nl/sendmail.htm

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by sdckapr View Post
    MS MVP Ron DeBruin has some code examples at http://www.rondebruin.nl/sendmail.htm
    Steve, as always, beat me to it, but the specific code at Ron's site appears to be on http://www.rondebruin.nl/mail/folder1/mail3.htm using the

    Sub Mail_Sheets_Array()

    code and editing

    .Sheets(Array("Sheet1", "Sheet3")).Copy

    to

    .Sheets("Summary").Copy

    But you also need code to loop through all workbooks in the folder C:\Workfile\Z-Accnts. You can also find code to do that here in this Forum.

    We prefer not to write it all for you, because you don't learn anything, and we have to emulate your situation, which takes time. Put it together as best you can, work on debugging it, and then post back if you are stuck.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try if this fits your needs...
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by pieterse View Post
    Try if this fits your needs...
    Thanks for the help. When I activate the macro a compile error appears-see attached screen print

    I have also attached a sample file, containing the summary worksheet. There is a lot more data, but this is just a sample file. The principle is he same

    It would be appreciated if you would test and advise
    Attached Files Attached Files

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    At a glance, change:

    Sub Mailfiles(sMailAddress As String, sMsg As String, vFiles As Variant)

    to

    Sub Mailfiles(sMailAddress As String, vFiles As Variant)
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi John

    the macro now executes.

    It would be appreciated if you would amend the macro as follows:

    1) Extract the sammary sheet and save this in the temp directory begiining with the name of the original workbook for eg M_Br1 followed by .summary eg M_BR1.summary
    2) Create an email with the subject heading "Summary Profit Figures" and main body "Attached please find summary profit figures"


    Your assistance regarding the above will be most appreciated

    Regards

    Howard
    Attached Files Attached Files

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Right now, the code does almost everything you are asking for, the only difference being that it saves the files in the same folder as where you open them from.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Jan

    When I activate the macro, I opens up a temp directory. I am not sure what file needs to be opened. It would be appreciated if you could advise what I must do when it ants to open up a file

    Regards

    Howard

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Jan

    I managed to work out what the problem was. I had the code on the original workbook. I have now created a blank workbook and the code works perfectly

    Thanks for all the help and patience

    Regards

    Howard

  13. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Jan

    You recently assisted me in writing code to extract a worksheet & to email this.

    I need you to assist in writing code that will range value all the formula on thesis summary worksheet, before saving and emailing this

    Your assistance will be most appreciated

    Regards

    Howard

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Howard,

    These lines of code replace formulas with values:

    With Worksheets("summary")
    .UsedRange.Formula = .UsedRange.Value
    End With
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Jan

    Thanks for the help, much appreciated

    Regards

    Howard

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •