E
Eric
Does anyone have any suggestions on how to fix the following codes?
Under the spreadsheet Summary, Cell A1 is equal to cell K14 in ADR
spreadsheet. When I run the following code, the formula in cell A1 [=ADR!K14]
for Summary spreadsheet change to the formula [=ADR!BJ14], I get no idea why
the referring location is changed.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
Sub ADR()
Dim qtEntry As QueryTable
Dim qryConnect As String
Dim anyRange As Range
Dim anySheet As Worksheet
Set anyRange = Sheets("ADR").Range("A1:" & _
Sheets("ADR").Range("A1").SpecialCells(xlLastCell).Address)
On Error Resume Next ' errors if no querytable entry
anyRange.QueryTable.Delete
On Error GoTo 0
anyRange.ClearContents
qryConnect = "URL;http://www.editgrid.com/export/book/556471?type=html"
Set anySheet = Sheets("ADR")
With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _
:=anySheet.Range("$A$1"))
.Name = "hsio070104"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub
Under the spreadsheet Summary, Cell A1 is equal to cell K14 in ADR
spreadsheet. When I run the following code, the formula in cell A1 [=ADR!K14]
for Summary spreadsheet change to the formula [=ADR!BJ14], I get no idea why
the referring location is changed.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
Sub ADR()
Dim qtEntry As QueryTable
Dim qryConnect As String
Dim anyRange As Range
Dim anySheet As Worksheet
Set anyRange = Sheets("ADR").Range("A1:" & _
Sheets("ADR").Range("A1").SpecialCells(xlLastCell).Address)
On Error Resume Next ' errors if no querytable entry
anyRange.QueryTable.Delete
On Error GoTo 0
anyRange.ClearContents
qryConnect = "URL;http://www.editgrid.com/export/book/556471?type=html"
Set anySheet = Sheets("ADR")
With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _
:=anySheet.Range("$A$1"))
.Name = "hsio070104"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub