Importing from Web

[Doug]

I have two questions.
When I do a web import, I am importing a sizable amount of data. It covers
from O3:BL800. I am assuming this is a lot. It is very slow and does not
always work, so I have to click refresh a couple times in order to get it to
work. I have a fast internet connection. Sometimes I have to go into the edit
query to recheck the data that I want imported. It seems to forget. So my
first question is how can I get this to work proficiently?
Second question is. I have been importing the data and then copying and
pasting it to a table with like headers. I have been using a macro to do this
in a much shorter time. I would like to know if there is a way that this can
automatically ascribe the data to the given header column into my table at
the time it imports the data? One reason for this is because the data
imported is not in the columns I would prefer. Example. I would like to place
the data in with the same column header. Can I do this? I looked at the
properties and options for the query and it seems like I should be able to do
this. I tried finding a tutorial and help data but it was not very helpful to
me. I am using Excel 2007. Can you please tell me how to make this work
proficiently?

Thank you so much!

 

[Matthew Herbert]

Doug,

It's a bit hard to tell how to provide the best help without some code or
data examples. Putting the examples aside, I quickly threw together an
example of XMLHTTP below, which queries Yahoo's site for stock pricing data.
(Be sure to test the code, which returns daily pricing into a string
variable). The code uses late binding (i.e. the CreateObject line) as
opposed to adding the XML reference (Tools | Reference). This is one example
of how to get internet data without using the built-in web query. Simply run
TestXMLHTTP.

As for the "column header" portion, I'm not sure that I completely follow
your explanation. Without seeing the data, my best guess would be for you to
look into the VLOOKUP, HLOOKUP, MATCH, and/or INDEX functions to perform the
table match.

Best,

Matthew Herbert

Sub TestXMLHTTP()

Dim dteStart As Date
Dim dteEnd As Date
Dim strRes As String

dteStart = DateSerial(2007, 1, 1)
dteEnd = Date
strRes = GetYahooXMLHTTP("APOL", dteStart, dteEnd)
If strRes = "" Then
MsgBox "Data not downloaded."
Else
MsgBox "Data downloaded."
End If

End Sub

Function GetYahooXMLHTTP(strTicker As String, _
dateStart As Date, _
dateEnd As Date) As String

Dim objXMLHTTP As Object
Dim strURL As String
Dim strMonthStart As String
Dim strMonthEnd As String
Dim strDayStart As String
Dim strDayEnd As String
Dim strYearStart As String
Dim strYearEnd As String
Dim strText As String

'set up the start dates
strMonthStart = CStr(Format(DateSerial(Year(dateStart), Month(dateStart) -
1, Day(dateStart)), "mm"))
If strMonthStart = "12" Then strMonthStart = "00"
strDayStart = CStr(Format(DateSerial(Year(dateStart), Month(dateStart),
Day(dateStart)), "dd"))
strYearStart = CStr(Format(DateSerial(Year(dateStart), Month(dateStart),
Day(dateStart)), "yyyy"))

'set up the end dates
strMonthEnd = CStr(Format(DateSerial(Year(dateEnd), Month(dateEnd) - 1,
Day(dateEnd)), "mm"))
If strMonthEnd = "12" Then strMonthEnd = "00"
strDayEnd = CStr(Format(DateSerial(Year(dateEnd), Month(dateEnd),
Day(dateEnd)), "dd"))
strYearEnd = CStr(Format(DateSerial(Year(dateEnd), Month(dateEnd),
Day(dateEnd)), "yyyy"))

'Yahoo! URL
strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
"&a=" & strMonthStart & "&b=" & strDayStart & "&c=" & strYearStart
& _
"&d=" & strMonthEnd & "&e=" & strDayEnd & "&f=" & strYearEnd &
"&g=d" '&ignore=.csv

'create the XMLHTTP object
Set objXMLHTTP = CreateObject("Microsoft.XMLHTTP")

'query the server
With objXMLHTTP
.Open "GET", strURL, False
.send
strText = .responseText
End With

If objXMLHTTP.statusText <> "OK" Then
GetYahooXMLHTTP = ""
Else
GetYahooXMLHTTP = strText
End If

End Function

 

[Doug]

The web import comes in with columns full of data that I would like to be in
a different order. Instead of it being column A,B,C for example, I would like
it to know and place it in order to match the column headers. Like if I have
it B,C,A in the table then I would like for the import to automatically place
the data in those columns. I couldn't get the macro to work although I like
the idea of skipping the web query if there is a better way...
I can send it over to your e-mail if you'd like to see what I'm trying to do?

 

[Matthew Herbert]

Doug,

Sorry for the late reply. The "Notify me of replies" must not be working
properly because I was reviewing some of my previous posts and stumbled
across your reply. If you don't want to use the web query then there is some
involved coding that must take place. Feel free to send me what you are
working on and I can take a look at it; however, there are no promises of a
turn-around time.

Go ahead and send it to meh2030 at hotmail dot com, making the appropriate
substitutions. Include "Doug - Excel VBA Coding Help" in the subject line so
that I don't push the email into the trash can because I don't know who the
email is from. Also, include the thread to this post so that I can remember
what it is you are looking to do, and include any other comments you feel
necessary in the email.

Best,

Matt