ChristianFast
New Member
- Joined
- Feb 4, 2014
- Messages
- 8
Hi there, I'm trying to use a macro to get data from an XML document where I have to use a login.
The macro and workbook is intended for general users to search the document.
The macro will prompt you for a value, you type it in, you will then be prompted to login with username and password. The macro then uses your value as a parameter in the request and returns a table of info.
The last piece of the puzzle is to include the username and password in the VBA code, so that I don't get prompted for it everytime i run the macro. I just can't seem to find how to do that, I went to google university but, no luck.
I should make it clear that I dont know how to write code myself, I dont know what half the code actually does, I copy pieces from various sources to try and make my wishes happen.
I would be eternally grateful to anyone who tries to solve this.
The code:
The macro and workbook is intended for general users to search the document.
The macro will prompt you for a value, you type it in, you will then be prompted to login with username and password. The macro then uses your value as a parameter in the request and returns a table of info.
The last piece of the puzzle is to include the username and password in the VBA code, so that I don't get prompted for it everytime i run the macro. I just can't seem to find how to do that, I went to google university but, no luck.
I should make it clear that I dont know how to write code myself, I dont know what half the code actually does, I copy pieces from various sources to try and make my wishes happen.
I would be eternally grateful to anyone who tries to solve this.
The code:
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://TopSecretDocument.xml?maxSize=100&Value=[""QUOTE"",""Enter Value""]", _
Destination:=Range("a1"))
.TablesOnlyFromHTML = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Connections("Connection").Delete