Prev: make it text in vlookup
Next: One step further
From: Eric on 22 Mar 2010 20:22
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
From: Per Jessen on 22 Mar 2010 20:49
Maybe this:

Application.DisplayAlerts=False

Just remember to set to True again.

Regards,
Per

On 23 Mar., 01:22, Eric <E...(a)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

From: Eric on 22 Mar 2010 22:09
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...(a)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
>
> .
>
From: Per Jessen on 22 Mar 2010 23:03
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...(a)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=False
>
> > Just remember to set to True again.
>
> > Regards,
> > Per
>
> > On 23 Mar., 01:22, Eric <E...(a)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
>
> > .- Skjul tekst i anførselstegn -
>
> - Vis tekst i anførselstegn -

From: FSt1 on 22 Mar 2010 23:36
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...(a)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
> >
> > .
> >
 | 
Pages: 1
Prev: make it text in vlookup
Next: One step further