Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatating sending spreadsheet (2003)

    Edited by HansV to reduce screenshot in size

    I need to know if it is possible to send a spreadsheet automatically to a colleague via email on a weekly basis.
    The problem is that the spreadsheet name will vary from month to month.
    The only part of the spreadsheet that will change will be the month name.

    RFS May'08, RFS June'08 etc.

    Attached is screenshot of the document l want to send, l also need to apply a filter to the spreadsheet. Any help would be appreciated.
    Attached Images Attached Images
    • File Type: jpg x.jpg (19.2 KB, 0 views)

  2. #2
    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

    Re: Automatating sending spreadsheet (2003)

    I owuld think you could do it with a macro in a workbook set to run on worbook Open event.

    You could use something like the Windows "Add Scheduled Tasks" to open the workbook with the code

    The code (upon opening) would run the code to open the particular workbook you want to send, filter (or do whatever you need to set it up), then send the file.

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatating sending spreadsheet (2003)

    Can you tell me how l go about doing this as l require some assistance with the coding

  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

    Re: Automatating sending spreadsheet (2003)

    I presume by "code" you mean to open and manipulate the file and email.

    What part do you need help with? You need to provide us more information on what manipulation you want to do with the file before sending it.

    To send it you can examine the code by MS MVP Ron deBruin at Example Code for sending mail from Excel

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatating sending spreadsheet (2003)

    This does not tell me how to call up some VBA coding from scheduled tasks?

    As l would like to automate the process of sending a spreadsheet.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatating sending spreadsheet (2003)

    As Steve says, you attache the code to the workbook Open Event, when the code has run close the workbook.

    To run the Excel workbook use a Scheduled Task to run a batch file with this code

    echo off
    start excel.exe jj1972.xls
    Jerry

  7. #7
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatating sending spreadsheet (2003)

    Where and how do l access workbook 'Open event'?

    As l would like to put this coding in it?


    Sub bsky()
    '
    ' bsky Macro
    ' Macro recorded 02/05/2008
    '

    '
    Selection.AutoFilter Field:=6, Criteria1:="Blue Sky"
    End Sub

  8. #8
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatating sending spreadsheet (2003)

    Have you tried searching the Excel forum for the information you need? - e.g. <post:=708,909>post 708,909</post:>?

  9. #9
    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

    Re: Automatating sending spreadsheet (2003)

    You don't have to use the bat file. You can just have the task scheduler open the XLS file. In the "Add scheduled tasks", if you select the XLS file from "browse", XL will load, open the file and trigger the workbook open event when the task is run.

    Steve

  10. #10
    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

    Re: Automatating sending spreadsheet (2003)

    I would recommend that the add the workbook open procedure to a separate file other than the one you want to email. Otherwise everytime you opened to workbook it would email it out.

    The workbook open procedure would open the file of interest, manipulate it as desired, email it out, then close that file (without saving) and then shut down excel.

    Steve

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automatating sending spreadsheet (2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Good point, silly me <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  12. #12
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatating sending spreadsheet (2003)

    There is a problem in doing that in the workbook l require is updated daily and therefore the version needs to be the latest , is there anyway in the batch file to rename the original file to a file which can be used for the macro.
    I am unsure of the syntax to use, any help would be appreciated.

  13. #13
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatating sending spreadsheet (2003)

    The following macro does not appear to run when worksheet is open. I cannot work out why as it is in Workbook Open?



    Sub bsky()
    '
    ' bsky Macro
    ' Macro recorded 02/05/2008 by xxx

    'Private Sub Workbook_Open()
    Application.WindowState = xlMaximized
    Selection.AutoFilter Field:=6, Criteria1:="Blue Sky"
    End Sub

  14. #14
    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

    Re: Automatating sending spreadsheet (2003)

    Without the comments your code is:

    Sub bsky()
    Application.WindowState = xlMaximized
    Selection.AutoFilter Field:=6, Criteria1:="Blue Sky"
    End Sub

    This is not a "workbook Open" macro. The workbook open macro would be
    Private Sub Workbook_Open()
    Application.WindowState = xlMaximized
    Selection.AutoFilter Field:=6, Criteria1:="Blue Sky"
    End Sub

    and should be in the "ThisWorkbook" object not a "module"

    Steve

  15. #15
    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

    Re: Automatating sending spreadsheet (2003)

    I don't understand the problem. If the "Workbook with the code" opens the particular file, it will open the file that is last saved. If the filename of the workbook to send is changing, then presumably there is some pattern to the changing name and it can be added to the code to look for the file with the correct name on the correct day.

    If there is no pattern to what the name of the file to manipulate is, I don't see how it could be scheduled...

    I am not sure of the details so providing syntax is difficult. Could you detail exactly what you want and need the code to do, perhaps even providing a sample file?

    Steve

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
  •