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
|