Hello,
this is my headache for last few days, I have been trying to get a table from a link from a cell and i cant get it. link works fine and also same code works for other links in the same workbook. if anyone can help plz. this will help me a lot
Sub PPA1()
Dim www1 As String
Dim sht As Worksheet
Dim lastrow As Integer
Dim URL$(0 To 1)
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Set sht = ThisWorkbook.Sheets("PPA1")
lastrow = Sheets("PPA1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("PPA1").Range("A:F").Clear
www1 = Sheets("Instructions").Range("f14")
With sht.QueryTables.Add(Connection:= _
"URL;" & www1 _
, Destination:=sht.Cells(1, 1))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Application.CutCopyMode = False
With ThisWorkbook
For lngConn = .Connections.Count To 1 Step -1
.Connections(lngConn).Delete
Next lngConn
End With
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
this is my headache for last few days, I have been trying to get a table from a link from a cell and i cant get it. link works fine and also same code works for other links in the same workbook. if anyone can help plz. this will help me a lot
VBA Code:
Dim www1 As String
Dim sht As Worksheet
Dim lastrow As Integer
Dim URL$(0 To 1)
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Set sht = ThisWorkbook.Sheets("PPA1")
lastrow = Sheets("PPA1").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("PPA1").Range("A:F").Clear
www1 = Sheets("Instructions").Range("f14")
With sht.QueryTables.Add(Connection:= _
"URL;" & www1 _
, Destination:=sht.Cells(1, 1))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Application.CutCopyMode = False
With ThisWorkbook
For lngConn = .Connections.Count To 1 Step -1
.Connections(lngConn).Delete
Next lngConn
End With
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub