Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing from Web
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! -- Thank you! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing from Web
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" wrote: 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! -- Thank you! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing from Web
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? -- Thank you! "Matthew Herbert" wrote: 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" wrote: 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! -- Thank you! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing from Web
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 "Doug" wrote: 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? -- Thank you! "Matthew Herbert" wrote: 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" wrote: 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! -- Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Joel - Importing multiple text files to 1 spreadsheet, now importing from excel files | Excel Programming | |||
Importing from web | Excel Discussion (Misc queries) | |||
Importing Alan Beban's code on Arrays; Importing a module or a project | Excel Worksheet Functions | |||
importing SDF | Excel Worksheet Functions | |||
Importing | Excel Worksheet Functions |