VBA and web data

YounesB3 · #1 02-03-2014, 09:33 PM

Quote:

Originally Posted by BobBridges

QUOTE

Again, thank you so much! I was able to end the macro myself

:

Code:

Sub Temp()

Sheets("Temp").Select

URL = "URL;" & Range("A3").Value

    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=Range("K7"))
        .RowNumbers = False
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = False
        .SaveData = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .Refresh BackgroundQuery:=False
    End With

    Range("K7").Select
    Cells.Find(What:="page:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Cells.Find(What:="…", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

Dim i As Integer
i = Mid(ActiveCell.Value, 2) - 1

For Y = 1 To i

URL = "URL;" & Range("A3").Value & "&page=" & Y
Set vd = Range("K" & Rows.Count).End(xlUp).Offset(1, 0)

    With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=vd)
        .RowNumbers = False
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = False
        .SaveData = False
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .Refresh BackgroundQuery:=False
    End With

Next Y

Range("$A$6:$L$6").Select
Selection.AutoFilter
Selection.AutoFilter
LastRow = Range("K" & Rows.Count).End(xlUp).Row
Range("L7").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(OFFSET(RC[-1],-1,0),5)=""game "",1,IF(LEFT(RC[-1],5)=""game "",1,IF(LEFT(RC[-1],13)=""Release Date:"",1,IF(ISNUMBER(RC[-1]),IF(RC[-1]>60,1,0),0))))"
Selection.AutoFill Destination:=Range("L7:L" & LastRow)
Dim myRange As Range
Dim myCell As Range
Dim A As Range
Set myRange = Range("K7:K" & LastRow)

ActiveSheet.Range("$A$6:$L$100000").AutoFilter Field:=12, Criteria1:="0"
Range("K7:L" & LastRow).Select
Selection.ClearContents
ActiveSheet.Range("$A$6:$L$100000").AutoFilter Field:=12
Range("K7:L" & LastRow).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp

Do
    Range("K6").Select
    Cells.Find(What:="Game*", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Set myCell = ActiveCell
    Set A = ActiveCell.Offset(3, 0)
    Range(myCell, A).Select
    Selection.Copy
    Range("A6").Select
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Range(myCell, A).ClearContents

Loop Until Range("B1").Value = 1

Columns("K:L").Select

For X = 1 To i
    Selection.QueryTable.Delete
Next X

Selection.ClearContents
Range("K6").Select
ActiveCell.FormulaR1C1 = "Metacritic field"
Range("L6").Select
ActiveCell.FormulaR1C1 = "Formula"

End Sub

Some other questionmark on my head :

- The ".SaveData = False" statement seems to be saving the connections with the website even if it's set to false. I ran the macro twice just to make sure it wasn't a bother and it wasn't. I made the last part of the code delete all webqueries associated in the workbook.
- I'm not sure why your "vd" statement and my lastrow didn't work :P
- I might have made too much lines in my code hahaha!

But these are non fondamental...! The automation is finally a success!!

BobBridges · #2 02-06-2014, 09:37 PM

My understanding of the SaveData setting, from reading the documentation, is that it determines whether to retain the downloaded data when you save the workbook. The QueryTable itself is always saved, and I would expect that to include the connection.

"Didn't work" is no help; I can't guess at a cause without symptoms. If you want me to try to explain what's wrong with my vd and your lastrow, you gotta say what they did instead working. Did the program keep going infinitely? Did the report come out right but colored yellow? Did your PC display "VD doesn't work!!" and then explode? What?

YounesB3 · #3 02-06-2014, 10:38 PM

Quote:

Originally Posted by BobBridges

My understanding of the SaveData setting, from reading the documentation, is that it determines whether to retain the downloaded data when you save the workbook. The QueryTable itself is always saved, and I would expect that to include the connection.

"Didn't work" is no help; I can't guess at a cause without symptoms. If you want me to try to explain what's wrong with my vd and your lastrow, you gotta say what they did instead working. Did the program keep going infinitely? Did the report come out right but colored yellow? Did your PC display "VD doesn't work!!" and then explode? What?

Haha no, your vd statement does work, it's my lastrow which doesn't! I'm not sure why :P

Your vd statement copies the data at the correct location (cell #) while the lastrow always overwrites at the same cell even though I (tried to) state otherwise..

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Match two sets of data and display specific data	lolly150	Excel	1	05-14-2012 10:33 PM
Edit Data Source- Linking template charts to new data	lbf	PowerPoint	0	10-28-2011 12:19 PM
Powerpoint: adding data to trend lines w/o data labels	HaiLe	PowerPoint	0	04-11-2011 09:21 AM