VBA to log in to website for web query

[dwrgt3]

Hello,

I am attempting to build a workbook that i am going to use to get order status updates from each vendor's website. I have about 30 vendors and each one will have it's own sheet within this workbook. The problem I'm running into is getting the VBA to log in, and search a date range then import the results to my workbook.

The code i have is working to some extent. I changed the URL to the homepage of the website i'm trying to access. But the code will import data, it is just saying i'm not logged in if i try to import from for # 20, which is the one i need. Also, i imagine when i solve this the very next issue i'm going to have is getting the macro to enter date ranges and click search, at that point i will need it to import those results. I'm looking for general direction, i don't expect anyone to write this up fro me. I am trying to learn how to fish, don't feed me.... I hope I've explained this clearly. Ayy input would be helpful.



Sub STATUS_ALPHABRODER()


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.alphabroder.com/", Destination:=Range("$A$1"))
.Name = "my orders"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "20"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


End Sub


To summarize.

1. I need this code to log in using my credentials (cannot provide this information to you)
2. navigate to https://www.alphabroder.com/cgi-bin/online/webshr/my-orders.w
3. Enter yesterdays date through today's date for date range
3. click search
4. import results from query to excel.

 

[John_w]

If the date range search results in a direct URL for that specific search then a VBA web query might work if you first set up IE as follows. Open the site in IE, tick the 'Remember me' box and log in, and set this 'logged in' page as your IE home page. Then in the VBA code, use the specific search URL as the "URL;http//www.xxxx/yyyy" connection string for the web query.

If any of the above doesn't work then you would have to use IE automation with HTMLDocument and related classes (or possibly directly accessing the HTML using XMLhttp/WinHttp) to run the search and extract the results data.