+ Reply to Thread
Results 1 to 6 of 6

Unwanted prompt when running auto_open macro

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2010
    Posts
    4

    Unwanted prompt when running auto_open macro

    I have a work that contains one data query. The work book contains an auto_open macro. When i open the workbook I get the following message generated by the macro. My intention with this workbook is that the workbook is run by the scheduler. It emails a copy of the workbook that is data only and then closes. It does work if the second line commented out.

    "This action will cancel a pending Refresh Data command. Continue?

    The message is generated by the second line of code which saves the workbook. It appears to be a timing issue. Listed below is the auto_open macro:


    Sub auto_open()

    ActiveWorkbook.Connections("OI_Stock_Status_Report").Refresh
    ActiveWorkbook.Save

    Sheets("Sheet2").Select
    Cells.Select
    Selection.ClearContents

    Application.Goto Reference:="Copy_Source"
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Sheets("Sheet2").Select
    Range("A7").Select
    Sheets("Sheet1").Select
    Range("A7").Select

    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim wb As Workbook
    Dim strdate As String
    strdate = Format(Now, "mmddyy")
    Application.ScreenUpdating = False
    Sheets("Sheet2").Copy
    Set wb = ActiveWorkbook
    With wb
    .SaveAs "Stock_Status_010_" & strdate & ".xlsx"
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = ""
    .Subject = "Stock Status 010 New Format " & Format(Date, "MM/dd/YY")
    .Body = "Attached is the New Stock status report for BI."
    .Attachments.Add wb.FullName
    'You can add other files also like this
    '.Attachments.Add ("C:\test.txt")
    .Send 'or use .Display
    End With
    .ChangeFileAccess xlReadOnly
    Kill .FullName
    .Close False
    End With
    Application.ScreenUpdating = True
    Set OutMail = Nothing
    Set OutApp = Nothing

    ActiveWorkbook.Save
    ActiveWorkbook.Application.Quit

    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394

    Re: Unwanted prompt when running auto_open macro

    Why don't you just delete the line? Do you need to save there?

  3. #3
    Registered User
    Join Date
    04-11-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Unwanted prompt when running auto_open macro

    Yes, that is what I did but I was curious as what is causing the message.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,618

    Re: Unwanted prompt when running auto_open macro

    Is the BackGroundQuery property for the query set to False ?

  5. #5
    Registered User
    Join Date
    04-11-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Unwanted prompt when running auto_open macro

    The enable background refresh checkbox is checked. Is that the property you were referring too?

  6. #6
    Registered User
    Join Date
    04-11-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Unwanted prompt when running auto_open macro

    I unchecked the enable background refresh as it solved the problem. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1