Automatic Web Queries
 

Hi,

Have come across a new problem,

Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic
web updates based on cell data see below

Dim URL2Use As String
URL2Use = "URL;" & Sheet1.Range("D1").Value

With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _
Destination:=Range("A10"))
.Name = "AutoLink"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

This works in a new workbook, but doesn't work when I add a identical page
and macro to an existing workbook,

Have had Runtime 1004 errors, invalid web address errors and background
refresh errors, yet it works fine in a dedicated workbook??

Any thoughts

Automatic Web Queries
 

Sometimes Sheet1 in the workbook is not actually named "Sheet1".
Try this:

Replace the Sheet1 ref in the below code to the actual tab name:
Change this:
URL2Use = "URL;" & Sheet1.Range("D1").Value

to something like this:
URL2Use = "URL;" & Sheet("mySheet").Range("D1").Value

Does that help?

***********
Regards,
Ron


"Alarmbloke" wrote:

Hi,

Have come across a new problem,

Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic
web updates based on cell data see below

Dim URL2Use As String
URL2Use = "URL;" & Sheet1.Range("D1").Value

With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _
Destination:=Range("A10"))
.Name = "AutoLink"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

This works in a new workbook, but doesn't work when I add a identical page
and macro to an existing workbook,

Have had Runtime 1004 errors, invalid web address errors and background
refresh errors, yet it works fine in a dedicated workbook??

Any thoughts

Corrected code typo
 

So as not to have sent you a sabotaged suggestion....

The replacement example code should have been:
URL2Use = "URL;" & Sheets("mySheet").Range("D1").Value

(Changed Sheet to Sheets)

***********
Regards,
Ron


"Ron Coderre" wrote:

Sometimes Sheet1 in the workbook is not actually named "Sheet1".
Try this:

Replace the Sheet1 ref in the below code to the actual tab name:
Change this:
URL2Use = "URL;" & Sheet1.Range("D1").Value

to something like this:
URL2Use = "URL;" & Sheet("mySheet").Range("D1").Value

Does that help?

***********
Regards,
Ron


"Alarmbloke" wrote:

Hi,

Have come across a new problem,

Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic
web updates based on cell data see below

Dim URL2Use As String
URL2Use = "URL;" & Sheet1.Range("D1").Value

With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _
Destination:=Range("A10"))
.Name = "AutoLink"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

This works in a new workbook, but doesn't work when I add a identical page
and macro to an existing workbook,

Have had Runtime 1004 errors, invalid web address errors and background
refresh errors, yet it works fine in a dedicated workbook??

Any thoughts

Corrected code typo
 

Yes, the second one worked a treat thanks, the sheet was labelled as sheet 1
but was added half way through the project with the original sheet1 renamed a
long time ago. Is there any way of telling the true sheet names??

Once again, hat off to you Sir,

"Ron Coderre" wrote:

So as not to have sent you a sabotaged suggestion....

The replacement example code should have been:
URL2Use = "URL;" & Sheets("mySheet").Range("D1").Value

(Changed Sheet to Sheets)

***********
Regards,
Ron


"Ron Coderre" wrote:

Sometimes Sheet1 in the workbook is not actually named "Sheet1".
Try this:

Replace the Sheet1 ref in the below code to the actual tab name:
Change this:
URL2Use = "URL;" & Sheet1.Range("D1").Value

to something like this:
URL2Use = "URL;" & Sheet("mySheet").Range("D1").Value

Does that help?

***********
Regards,
Ron


"Alarmbloke" wrote:

Hi,

Have come across a new problem,

Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic
web updates based on cell data see below

Dim URL2Use As String
URL2Use = "URL;" & Sheet1.Range("D1").Value

With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _
Destination:=Range("A10"))
.Name = "AutoLink"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

This works in a new workbook, but doesn't work when I add a identical page
and macro to an existing workbook,

Have had Runtime 1004 errors, invalid web address errors and background
refresh errors, yet it works fine in a dedicated workbook??

Any thoughts

Corrected code typo
 

Strike that last request, just right click the sheet and 'View Code' Sheet
numbers then listed in VBA list !! Should have guessed that really !!

Thanks again

"Ron Coderre" wrote:

So as not to have sent you a sabotaged suggestion....

The replacement example code should have been:
URL2Use = "URL;" & Sheets("mySheet").Range("D1").Value

(Changed Sheet to Sheets)

***********
Regards,
Ron


"Ron Coderre" wrote:

Sometimes Sheet1 in the workbook is not actually named "Sheet1".
Try this:

Replace the Sheet1 ref in the below code to the actual tab name:
Change this:
URL2Use = "URL;" & Sheet1.Range("D1").Value

to something like this:
URL2Use = "URL;" & Sheet("mySheet").Range("D1").Value

Does that help?

***********
Regards,
Ron


"Alarmbloke" wrote:

Hi,

Have come across a new problem,

Have set up a macro thanks to Pinmaster and Ron Coderre which does automatic
web updates based on cell data see below

Dim URL2Use As String
URL2Use = "URL;" & Sheet1.Range("D1").Value

With ActiveSheet.QueryTables.Add(Connection:=URL2Use, _
Destination:=Range("A10"))
.Name = "AutoLink"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

This works in a new workbook, but doesn't work when I add a identical page
and macro to an existing workbook,

Have had Runtime 1004 errors, invalid web address errors and background
refresh errors, yet it works fine in a dedicated workbook??

Any thoughts