VBA web queries

[Crazzled]

Hi all,

So I used the record macro to import a table, by web query. I then build a dynamic link, placed it in and altered the code around it. Then provided a basic loop to repeat it for each value in the dynamic range. The problem I'm having is that it creates the page, and connection but it doesn't import the table onto the sheet. Is there some issue that VBA has when following importing web queries? Or can someone find how it's wrongly coded? Appologies if my code, or references are wrong or unjustly named - I'm quite new to this part of Excel.

Any input would be appriciated;

Sub populatedata()

'get dates
Dim linkdatefrom As Date
linkdatefrom = range("Control!$C$3").Value

'split string for use as a date within a dynamic link
Dim ddlinkdatefrom As String
ddlinkdatefrom = Left(linkdatefrom, 2)

'yyyylinkdatefrom is the year
Dim yyyylinkdatefrom As String

yyyylinkdatefrom = Right(linkdatefrom, 4)

'mmlinkdatefrom

Dim mmlinkdatefrom As String
mmlinkdatefrom = Mid(linkdatefrom, 4, 2)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'split dates to into three pieces
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim linkdateto As Date

linkdateto = range("Control!$C$3").Value

Dim ddlinkdateto As String
ddlinkdateto = Left(linkdateto, 2)

Dim mmlinkdateto As String
mmlinkdateto = Mid(linkdateto, 4, 2)

Dim yyyylinkdateto As String
yyyylinkdateto = Right(linkdateto, 4)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'split timefrom into pieces
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim linktimefrom As Date

linktimefrom = range("control!$b$5").Value

Dim hhtimefrom As String
Dim mmtimefrom As String
Dim sstimefrom As String

hhtimefrom = Left(linktimefrom, 2)
mmtimefrom = Mid(linktimefrom, 4, 2)
sstimefrom = Right(linktimefrom, 2)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'split timeto into pieces
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim linktimeto As Date

linktimeto = range("control!$c$5").Value

Dim hhtimeto As String
Dim mmtimeto As String
Dim sstimeto As String

hhtimeto = Left(linktimeto, 2)
mmtimeto = Mid(linktimeto, 4, 2)
sstimeto = Right(linktimeto, 2)

'create the import code

'Create dynamicURL1
'part for dynamic login
Dim counter As Integer
counter = 8
Dim dynamicloginpartofURL As String

Dim part1 As String
Dim part2 As String
Dim part3 As String
Dim part4 As String
Dim part5 As String
Dim part6 As String

Dim secondrylinkpart As String
secondrylinkpart = "pick-history.cgi?attribute=PickerId&value="

part1 = "https://bhx1-portal.amazon.com/fc-cgi/picking/pick-history.cgi?attribute=PickerId&value="
'Variable Login
part2 = "&from="
'frommmparthere
part3 = "%2F"
'fromddparthere
'part3
'fromyearparthere
part4 = "+"
'timefromhhhere
part5 = "%3A"
'timefrommmhere
'part5
'timefromsshere
part6 = "&to="
'tommdatehere
'part3
'todddatehere
'part3
'toyyyyparthere
'part4
'timetohhparthere
'part5
'timetommparthere
'part5
'timetossparthere


Do Until ThisWorkbook.Sheets("control").Cells(counter, 1).Value = ""

dynamicloginpartofURL = range("A" & counter).Value

Dim dynamicURL1 As String
dynamicURL1 = part1 & dynamicloginpartofURL & part2 & mmlinkdatefrom & part3 & ddlinkdatefrom & part3 & yyyylinkdatefrom _
& part4 & hhtimefrom & part5 & mmtimefrom & part5 & sstimefrom & part6 _
& mmlinkdateto & part3 & ddlinkdateto & part3 & yyyylinkdateto & part4 _
& hhtimeto & part5 & mmtimeto & part5 & sstimeto
MsgBox (dynamicURL1)

Dim dynamicpart2 As String
dynamicpart2 = secondarylinkpart & dynamicloginpartofURL & part2 & mmlinkdatefrom & part3 & ddlinkdatefrom & part3 & yyyylinkdatefrom _
& part4 & hhtimefrom & part5 & mmtimefrom & part5 & sstimefrom & part6 _
& mmlinkdateto & part3 & ddlinkdateto & part3 & yyyylinkdateto & part4 _
& hhtimeto & part5 & mmtimeto & part5 & sstimet


ActiveWorkbook.Worksheets.Add.Name = dynamicloginpartofURL
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;dynamicURL1" _
, Destination:=range("$A$1"))
.Name = dynamicpart2
.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

End With

counter = counter + 1

Loop

End Sub

 

[BrianMH]

Try refreshing it in your code.