Excel 2010 and 2007 for Windows have the option to import data from the web. Excel for Mac users don’t. An integral part of working with Excel is using keyboard shortcuts. They make your life so much easier (in the Windows versions at least, in the Mac version I think they tend to shorten your life span). In my last post I dealt with getting a, here I’m going to use the web page and import to a spreadsheet. Get a Help Topic Web Page Address As you will see, it helps to have the web address or URL on the clipboard before importing data from the web. 'This Web Query returned no data. To modify the query, click OK, click the name of the external data range in the name box on the formula bar, and then click Edit Query on the External Data toolbar.' What is wrong here? How come it can see the data in the dialog box, but when I come to actually import to the sheet it fails? In this example I’ll use the following steps to get the URL for Keyboard Shortcuts for Excel 2010: • Press the F1 key • Type Excel keyboard shortcuts in the search box • Click the link for Keyboard Shortcuts for Excel 2010 • Right click on the topic heading then select Properties • Triple click the Address (URL) link then copy ( Ctrl+C) to the clipboard • Click Cancel and close the Help window Now we have the URL on the clipboard. Get Data From a Web Page Choose Data > Get External Data > From Web to bring up the New Web Query dialog box. This dialog box functions as a Web browser and can be re-sized. Clear the Address bar and paste the URL from the clipboard, then press Enter or click Go. The web page above will appear in the New Web Query window. Scroll down and you’ll see a right-arrow in a yellow box at the top of each table. Click an arrow to queue any table for import into Excel. We want the entire page so I’ll click the right-arrow in a yellow box at the top-left corner of the web page. This will give us the entire page. Once you click the right arrow it turns to a green check in a box. Now click the Options button then select Full HTML. Since we’re importing the entire page this option will give the best formatting. Now click the Import button and Excel will ask where you want to put the data. I’m leaving the default location cell A1. The data on the web page is imported into the worksheet. This is now an active external query. To Edit the Query choose Data > Get External Data > Refresh All > Connection Properties then select the Definition tab and click Edit Query. You’re now back to the Edit Web Query dialog box where you can make modifications. To modify the data range properties, right-click any cell in the imported data range and select Data Range Properties from the pop-up box. The great thing about a web query is that if the web page data is updated all you have to do is Refresh the query to update the worksheet. 4 thoughts on “ Import Data From the Web in Excel” • CP Lai I found this feature quite useful. However, when the source webpage is not in UTF-8 nor Latin1 encoding, the import brings in gibblish. I couldn’t figure out how to tell Excel to handle the character encoding correctly. The import’s preview screen displays the webpage contents correctly, so at the software level, it should honor the character encoding from the http header and use it to convert the text to Excel internal data accordingly. But it does not. Experience superior data security with external and internal drives and burners from LG. Please refer to the instructions that come with your security cameras or contact their manufacturer for advise.^IFV. Lg external dvd burner driver for mac. Regrettably, without any information about your wired security cameras, we cannot determine if you can connect this unit directly to them or if you have to go through your PC/Laptop. Hi bbzipo, This forum is used to discuss about Excel developing issue, your issue is more related with web query which is a product function. I will move this thread to the more related forum. Reference: Thanks for your understanding. Best Regards, Edward We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. Click to participate the survey. Thank you, but the information you referenced does not apply to Excel 2016. The methods described there do not work for me in Excel 2016 for Mac. My experiments so far have revealed that • The iqy file format that used to work (and still works) in Excel 2011 for Mac (and in all versions of Excel for Windows) does not work in Excel 2016 for Mac. • Excel documents containing web queries created in other version of Excel can be opened in Excel 2016 for Mac, but the data cannot be refreshed. • New web queries created in Excel 2016 for Mac can only reference my Office 365 locations (OneDrive and SharePoint), but not arbitrary web locations. All I'm asking is for someone to please either confirm those findings, or tell me I'm doing something wrong and provide verified working instructions how to make it work. I also posted this question here http://answers.microsoft.com/en-us/mac/forum/macoffice2016-macexcel/web-query-iqy-support-in-excel-2016-on-mac-os-x/b87416e6-23a4-498c-bda2-4400b94ce12c.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |