How not to display any pop up message for macro?

When macro imports an external links into sheets, it pops up a message is 
about the pasting area is larger then worksheet for confirmation, does anyone 
have any suggestions on how not to display any pop up message for running 
macro?

Thanks in advance for any suggestions

qryConnect = "URL;http://www.mmacycles.com"
Set anySheet = Sheets("MMA")
With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _
    :=anySheet.Range("$A$1"))
    
        .Name = "o070104"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = True
        .BackgroundQuery = True
        .RefreshStyle = xlInsertEntireRows
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
0
Utf
3/23/2010 12:22:11 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
768 Views

Similar Articles

[PageSpeed] 43

Maybe this:

Application.DisplayAlerts=3DFalse

Just remember to set to True again.

Regards,
Per

On 23 Mar., 01:22, Eric <E...@discussions.microsoft.com> wrote:
> When macro imports an external links into sheets, it pops up a message is
> about the pasting area is larger then worksheet for confirmation, does an=
yone
> have any suggestions on how not to display any pop up message for running
> macro?
>
> Thanks in advance for any suggestions
>
> qryConnect =3D "URL;http://www.mmacycles.com"
> Set anySheet =3D Sheets("MMA")
> With anySheet.QueryTables.Add(Connection:=3DqryConnect, Destination _
> =A0 =A0 :=3DanySheet.Range("$A$1"))
>
> =A0 =A0 =A0 =A0 .Name =3D "o070104"
> =A0 =A0 =A0 =A0 .FieldNames =3D True
> =A0 =A0 =A0 =A0 .RowNumbers =3D False
> =A0 =A0 =A0 =A0 .FillAdjacentFormulas =3D False
> =A0 =A0 =A0 =A0 .PreserveFormatting =3D True
> =A0 =A0 =A0 =A0 .RefreshOnFileOpen =3D True
> =A0 =A0 =A0 =A0 .BackgroundQuery =3D True
> =A0 =A0 =A0 =A0 .RefreshStyle =3D xlInsertEntireRows
> =A0 =A0 =A0 =A0 .SavePassword =3D False
> =A0 =A0 =A0 =A0 .SaveData =3D True
> =A0 =A0 =A0 =A0 .AdjustColumnWidth =3D True
> =A0 =A0 =A0 =A0 .RefreshPeriod =3D 0
> =A0 =A0 =A0 =A0 .WebSelectionType =3D xlEntirePage
> =A0 =A0 =A0 =A0 .WebFormatting =3D xlWebFormattingNone
> =A0 =A0 =A0 =A0 .WebPreFormattedTextToColumns =3D True
> =A0 =A0 =A0 =A0 .WebConsecutiveDelimitersAsOne =3D True
> =A0 =A0 =A0 =A0 .WebSingleBlockTextImport =3D False
> =A0 =A0 =A0 =A0 .WebDisableDateRecognition =3D False
> =A0 =A0 =A0 =A0 .WebDisableRedirections =3D False
> =A0 =A0 =A0 =A0 .Refresh BackgroundQuery:=3DFalse
> =A0 =A0 End With

0
Per
3/23/2010 12:49:38 AM
Thank you very much for suggestions
Do I need to set it to True at the end of macro?
What if I don't set it to True, if I run another macro, will the default 
setting be false?
Thank you very much for any suggestions
Eric

"Per Jessen" wrote:

> Maybe this:
> 
> Application.DisplayAlerts=False
> 
> Just remember to set to True again.
> 
> Regards,
> Per
> 
> On 23 Mar., 01:22, Eric <E...@discussions.microsoft.com> wrote:
> > When macro imports an external links into sheets, it pops up a message is
> > about the pasting area is larger then worksheet for confirmation, does anyone
> > have any suggestions on how not to display any pop up message for running
> > macro?
> >
> > Thanks in advance for any suggestions
> >
> > qryConnect = "URL;http://www.mmacycles.com"
> > Set anySheet = Sheets("MMA")
> > With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _
> >     :=anySheet.Range("$A$1"))
> >
> >         .Name = "o070104"
> >         .FieldNames = True
> >         .RowNumbers = False
> >         .FillAdjacentFormulas = False
> >         .PreserveFormatting = True
> >         .RefreshOnFileOpen = True
> >         .BackgroundQuery = True
> >         .RefreshStyle = xlInsertEntireRows
> >         .SavePassword = False
> >         .SaveData = True
> >         .AdjustColumnWidth = True
> >         .RefreshPeriod = 0
> >         .WebSelectionType = xlEntirePage
> >         .WebFormatting = xlWebFormattingNone
> >         .WebPreFormattedTextToColumns = True
> >         .WebConsecutiveDelimitersAsOne = True
> >         .WebSingleBlockTextImport = False
> >         .WebDisableDateRecognition = False
> >         .WebDisableRedirections = False
> >         .Refresh BackgroundQuery:=False
> >     End With
> 
> .
> 
0
Utf
3/23/2010 2:09:01 AM
When the macro stop DisplayAlerts is reset to true, but I would always
reset it to true as soon as possible in my macro, as other unexpected
alerts will be supressed until it is set to True.

--
Per

On 23 Mar., 03:09, Eric <E...@discussions.microsoft.com> wrote:
> Thank you very much for suggestions
> Do I need to set it to True at the end of macro?
> What if I don't set it to True, if I run another macro, will the default
> setting be false?
> Thank you very much for any suggestions
> Eric
>
>
>
> "Per Jessen" wrote:
> > Maybe this:
>
> > Application.DisplayAlerts=3DFalse
>
> > Just remember to set to True again.
>
> > Regards,
> > Per
>
> > On 23 Mar., 01:22, Eric <E...@discussions.microsoft.com> wrote:
> > > When macro imports an external links into sheets, it pops up a messag=
e is
> > > about the pasting area is larger then worksheet for confirmation, doe=
s anyone
> > > have any suggestions on how not to display any pop up message for run=
ning
> > > macro?
>
> > > Thanks in advance for any suggestions
>
> > > qryConnect =3D "URL;http://www.mmacycles.com"
> > > Set anySheet =3D Sheets("MMA")
> > > With anySheet.QueryTables.Add(Connection:=3DqryConnect, Destination _
> > > =A0 =A0 :=3DanySheet.Range("$A$1"))
>
> > > =A0 =A0 =A0 =A0 .Name =3D "o070104"
> > > =A0 =A0 =A0 =A0 .FieldNames =3D True
> > > =A0 =A0 =A0 =A0 .RowNumbers =3D False
> > > =A0 =A0 =A0 =A0 .FillAdjacentFormulas =3D False
> > > =A0 =A0 =A0 =A0 .PreserveFormatting =3D True
> > > =A0 =A0 =A0 =A0 .RefreshOnFileOpen =3D True
> > > =A0 =A0 =A0 =A0 .BackgroundQuery =3D True
> > > =A0 =A0 =A0 =A0 .RefreshStyle =3D xlInsertEntireRows
> > > =A0 =A0 =A0 =A0 .SavePassword =3D False
> > > =A0 =A0 =A0 =A0 .SaveData =3D True
> > > =A0 =A0 =A0 =A0 .AdjustColumnWidth =3D True
> > > =A0 =A0 =A0 =A0 .RefreshPeriod =3D 0
> > > =A0 =A0 =A0 =A0 .WebSelectionType =3D xlEntirePage
> > > =A0 =A0 =A0 =A0 .WebFormatting =3D xlWebFormattingNone
> > > =A0 =A0 =A0 =A0 .WebPreFormattedTextToColumns =3D True
> > > =A0 =A0 =A0 =A0 .WebConsecutiveDelimitersAsOne =3D True
> > > =A0 =A0 =A0 =A0 .WebSingleBlockTextImport =3D False
> > > =A0 =A0 =A0 =A0 .WebDisableDateRecognition =3D False
> > > =A0 =A0 =A0 =A0 .WebDisableRedirections =3D False
> > > =A0 =A0 =A0 =A0 .Refresh BackgroundQuery:=3DFalse
> > > =A0 =A0 End With
>
> > .- Skjul tekst i anf=F8rselstegn -
>
> - Vis tekst i anf=F8rselstegn -

0
Per
3/23/2010 3:03:34 AM
hi
if you don't set it to true again then all display messages, alerts, 
warnings ect will not be display whether you are running a macro or not. it 
is not wise to turn off alerts and NOT turn them back on.  for the duration 
of a singel macro, it may be permissible but never let a macro stop without 
turning alerts back on. otherwise, excel will be severly crippled.

regards
FSt1



"Eric" wrote:

> Thank you very much for suggestions
> Do I need to set it to True at the end of macro?
> What if I don't set it to True, if I run another macro, will the default 
> setting be false?
> Thank you very much for any suggestions
> Eric
> 
> "Per Jessen" wrote:
> 
> > Maybe this:
> > 
> > Application.DisplayAlerts=False
> > 
> > Just remember to set to True again.
> > 
> > Regards,
> > Per
> > 
> > On 23 Mar., 01:22, Eric <E...@discussions.microsoft.com> wrote:
> > > When macro imports an external links into sheets, it pops up a message is
> > > about the pasting area is larger then worksheet for confirmation, does anyone
> > > have any suggestions on how not to display any pop up message for running
> > > macro?
> > >
> > > Thanks in advance for any suggestions
> > >
> > > qryConnect = "URL;http://www.mmacycles.com"
> > > Set anySheet = Sheets("MMA")
> > > With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _
> > >     :=anySheet.Range("$A$1"))
> > >
> > >         .Name = "o070104"
> > >         .FieldNames = True
> > >         .RowNumbers = False
> > >         .FillAdjacentFormulas = False
> > >         .PreserveFormatting = True
> > >         .RefreshOnFileOpen = True
> > >         .BackgroundQuery = True
> > >         .RefreshStyle = xlInsertEntireRows
> > >         .SavePassword = False
> > >         .SaveData = True
> > >         .AdjustColumnWidth = True
> > >         .RefreshPeriod = 0
> > >         .WebSelectionType = xlEntirePage
> > >         .WebFormatting = xlWebFormattingNone
> > >         .WebPreFormattedTextToColumns = True
> > >         .WebConsecutiveDelimitersAsOne = True
> > >         .WebSingleBlockTextImport = False
> > >         .WebDisableDateRecognition = False
> > >         .WebDisableRedirections = False
> > >         .Refresh BackgroundQuery:=False
> > >     End With
> > 
> > .
> > 
0
Utf
3/23/2010 3:36:03 AM
Reply:

Similar Artilces:

save sent messages without attachment
Hi. Does anybody know how to save sent messages without the attachment? Thanks, Candee ...

Window Mail losing saved message content
In some folders whatever message I click on the same message opens regardless. If I try to move a message back to the Inbox then all messages in the folder show as "cannot find content" I have not deleted these messages but saved them in folders. Where did they go? How do I get them back? This has happened since my last backup so I cannot get them from the external backup file. Plus, this sho7uld not happen. I checked another folder, first it worked, then later it went bad as well. How do I get my emails restored, and how do I prevent this crap from happening ag...

Post Exchange SP2 install outgoing messages not being delivered
I have just finished installing SP1 for Windows 2003 Server and SP2 for Exchange 2003, since the install mail being sent to specific servers i.e aol.com, yahoo.com, and a few others is not flowing, it is being queued and not being delivered as before. I need to find a fix for this issue fast, Any and all help with this would be greatly appreciated. OS: Windows 2003 Server Standard w/ SP1 and all updates Exchange Server 2003 with SP2 Thanks in advance. That usually indicates that the server cannot resolve the target domains in DNS. I have seen similar problems caused by the virus scann...

Button/Macro Not Working, Help!
I have a macro that pulls up Report Manager and prints out pre-define views. The macro works fine only after I use Report Manager the firs time. If I exit my workbook and Excel, then return to it, th button/macro does not function. Nothing happens until I manully us Report Manager again. Is there a bug with this or am I missin something -- Message posted from http://www.ExcelForum.com Post your code. Gord Dibben Excel MVP On Thu, 20 May 2004 08:38:55 -0500, baston <<baston.16k4at@excelforum-nospam.com>> wrote: >I have a macro that pulls up Report Manager and prints out pre...

Why does workbook think deleted macro still exits?
I have deleted the macros in a workbook but each time I open it there is still a messge stating there are macros. How do I stop the message from appearing? Thanks Karen ...

Outlook 200 messages all contain attachments
Just in the last day or so every message I have received has had attached a text file with the text of the message. This is in addition to the message itself. I don't know what I might have done to change an option and can't find anything in the options menus that might affect this. Any ideas? Bob Weinheimer Charleston, WV Have you tried rebooting? I had that happen once a long time ago and a reboot cured it. "Bob Weinheimer" <rmweinheimer@ntelos.net> wrote in message news:2DjXe.20694$tc7.18573@fe03.lga... > Just in the last day or so every message I have r...

"You forwarded this message" icon
Is there anyway of removing the icon that shows that a message has been forwarded? Thanks for any help on this! No. Busted. "Tractorhead" wrote: > Is there anyway of removing the icon that shows that a message has been > forwarded? > > Thanks for any help on this! Sure, reply to it. Jacob "Tractorhead" wrote: > Is there anyway of removing the icon that shows that a message has been > forwarded? > > Thanks for any help on this! good one "Jake" wrote: > Sure, reply to it. > > Jacob > ...

Failure to Display anything but Methods in Microsoft.CRM.Proxy.dll
I'm using both VS.NET and VS.NET 2003 to attempt to run the "Find all the Incidents for an Account or Contact" code on MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/crmsdk/htm /findallofthecasesassociatedwithanaccountorcontact.asp). I've added the reference to Microsoft.CRM.Proxy.dll and also have attempted to use the Microsoft.CRM.ComProxy.dll when the first failed. I am unable to compile as the C# compiler states that both ".Credentials" and ".Url" are not valid properties of the Microsoft.CRM.Proxy.BizUser class. I used the o...

How to set Recipient Limits option under Message Delivery for certain users
Dear All, Quick Question: under Global Settings --> Message Delivery , under the properites of Message Delivery there is an option called "Recipient Limits" which will limit the number of recipients per single email, the defaul number is 5000 recipient per single email. now, if i applied this settings and i mentioned 30 recipients, these settings will be applied to all the users under my exchange server, becasue it is under the Global Settings of Exchange. Now, how can i exclude some VIP users from such settings ? i don't want this Global Settings for recipient limits...

Personal Macro Workbook 12-15-09
Hi, The personal macro workbook doesnt start up automatically in excel 2007. How should i get it to start automatically when excel starts up? Its driving me mad to find out where the personal macro workbook is stored in all honesty. Hi, If you did a 'standard' installation you should find personal.xlsb by looking in C:\program files\microsoft office\office 12\xlstart If it's not there then you don't have one. To create it record a simple macro saved to the personal macro workbook and then it should load every time you start the application. Mike "...

automatic warning message
Hi, Is it possible to add an automatic enterprise warning message in bottom of each email sent by Exchange 2003 Thanks Marc There are a number of 3rd party tools that do this. The easiest native way to do this is with an event sink. The following KB will step you through setting this up: http://support.microsoft.com/default.aspx?scid=kb;en-us;317327&Product=exch2003 "Marc D" <asdasdasd@asdasd.com> wrote in message news:%23nJ75lxpFHA.1260@TK2MSFTNGP10.phx.gbl... > Hi, Is it possible to add an automatic enterprise warning message in > bottom of each email...

Disabling Unicode Messaging
Apparently Exchange 2003 uses Unicode Messaging which previous versions of Exch did not. Now that we have migrated all of our users from Exch 2000 to 2003 their OST files will not work. We have no need for Unicode messaging so rather than rebuilding all of our User's Outlook Profile we would rather simply "disable" Unicode Messaging on the Exch Server. Does anyone have any idea how to do that? Can it be done once the server is in use and has 150+ mailboxes on it Thanks for any input Michael ...

Exchange 5.5 message.txt
Hi, I have problem. I got email with error message: This message uses a character set that is not supported by the Internet Service. To view the original message content, open the attached message. If the text doesn't display correctly, save the attachment to disk, and then open it using a viewer that can display the original character set. <<message.txt>> Anyone knows the solution? ...

Transfer Macro in Excel
I am trying to transfer informtion in one sheet to another sheet in the same workbook. I have a macro to process this information from the first sheet to the second. I keep getting an error in this Process macro at the line. Application.Goto REFERENCE:=Range("END") The first sheet is called ProblemSheet and the second sheet is called Processed. The line above is taken from the process macro on the ProblemSheet page. Do I have to name a certain cell as "END" on either sheet? Any help with this would be really appreciated. --- Message posted from http://www.ExcelForum.com...

Headers for internal messages
I've been asked to find a way to display message headers in Outlook for our local Exchange communications, IE employee to employee message. I'm aware that you can go into Outlook and view a message's full header under Message Options, but this only works for internet based messages. We'd really like to be able to see the originating client IP address for our Exchange messages in that Options box. Is something like that possible for Exchange? I've seen it with other systems. Thanks! TxKevo wrote: > I've been asked to find a way to display message headers i...

Pictures/Graphics won't display on website
Help, I'm new at this and have problem getting my website that I created in Publisher posted to web. The site will appear when I copy the index.htm file using my ISP file manager but not sure where the index_file folder and related graphics/pics need to be copied to. I tried creating a folder for index_file and copied all the files there that appear on my c drive when I published to the web. But all that displays are the alternate text for each pic/graphic. Any suggestions? A small child turns to Ed, and exclaims: "Look! Look! A post from anonymous@discussions.microsoft...

outlook 2000
running on home computer, no exhange or server. when I open an inbox message and close it, the message disappears, it isnt in other folders or in delteld files folder. If I move the message to another folder before opening it, then open the message in that folder, I am fine I can open and close it without losing it. I have been running everything fine for a couple of years now, only in the last moth did this start happening. I have reformatted and reloaded my computer, it doesnt help. I have taken mirosoft outlook express off the computer, that doesnt help either. I only have o...

Changing mind about sending message
How does one cancel a sent message? I changed my mind about one that I sent and now I want to cancel it. I can do this with Internet Explorer, but can't seem to find the option in Outlook Express. Thanks. Shari Next time, post in an Outlook Express group - this is not one of them. This group supports Outlook from the MIcrosoft Office suite - Outlook Express is a part of Internet Explorer. AFAIK, Outlook Express does not have support for message recall and only Outlook when using an Exchange server has a somewhat flaky message recall function. --� Milly Staples [MVP - Outlo...

macro #2
I'm new at this so this might seem elementary but if you don't know you ask those that do. I've created a macro that is suppose to clear the input data on a worksheet when a command button is clicked. However when the msgbox appears asking them if they wish to continue and they click "yes" nothing happens.I've included the code that I've written. Any help would be appreciated. Private Sub cmdclear_Click() MsgBox "Caution: You are about to delete the information on this worksheet" & vbNewLine & vbNewLine & "Do you wish to continue?"...

Test Message
2010-04-12 18:15:18 ...

Lost Properties pop up on right mouse click?
Hi, I can no longer right click on a control and bring up its properties. I have to be in design mode and select the field then click the properties button in the tool bar? Any idea how to get the right mouse click to bring the properties up? Thanks Chuck "Chuck" <Chuck@discussions.microsoft.com> wrote in message news:00FD3588-E880-498C-9F24-8E4D6A8F464B@microsoft.com... > Hi, I can no longer right click on a control and bring up its properties. > I > have to be in design mode and select the field then click the properties > button in the tool bar? > Any ide...

POP 3 connectivity
I installed SBS with sr 1 server service pack and SBS sr1 service pack and Exchange Sr2. On the same server I can browse the mailbox with internet explorer on Outlook web access when i created the same account in Outlook express on the same server when i do F5 it dose send and receive mail but it dosenot download messages in Outlook Express . Please advice? Sarosh Tafti wrote: > I installed SBS with sr 1 server service pack and SBS sr1 service pack and > Exchange Sr2. > On the same server I can browse the mailbox with internet explorer on > Outlook web access when i created ...

how to handle reflected message in parent?
i have a CComboBox and want to handle CBN_SELCHANGE in control and give the parent the chance to handle it also. problem is that the ClassWiz generated handler CN_CONTROL(CBN_SELCHANGE, OnSelchange) in my control looks like this: afx_msg void OnSelchange(); , so i cant return a BOOL to say: "forward to parent". how do i do this? in msdn, i didnt find any usefull info.... Yes you can. Change the handler to handle a reflected message, change the return type to BOOL. It's called "editing". See my essay on handling reflected messages in my MSDN Errors and Omissions po...

Error Message
I've started getting the following message each time I sign on to money. Due to Changes in your Money file, the saved version of the "Portfolio Value by Investment Account" report is no longer valid. Money has attempted to reset the report, but you should reset it manually to avoid unreliable information. I've tried to reset the report manually but to no avail. Anyone have any ideas / solutions ? In microsoft.public.money, greggoz wrote: >I've started getting the following message each time I >sign on to money. > > Due to Changes in your Money fil...

Is it possible to measure/display the column width with a cell function?
Wednesday afternoon Is it possible to measure/display the column width with a cell function? I need to adjust some columns for a report. But I cannot find a way to display the current width in units that I can then place in a VBA macro. Would be very good to have the macro read the values entered in a set of columns and change the width accordingly. (This is very easy in Quattro pro.) J Have a look at ShowWidth at: http://www.sulprobil.com/html/get_cell.html HTH, Bernd -- Use non-volatile INDEX(P11:IV65536,1+w,1+y):INDEX(P11:IV65536,w+y,x+z) instead of volatile OFFSET(P11,w,x,y,z)...