Automatic Web Queries

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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,

 

[Guest]

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