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
|