Hi Mike, From web is the feature in Excel for Windows. Excel for Mac does not have the button for this feature. So I encourage and suggest you go to UserVoice to vote for. According to your information, I would like to confirm whether the issue occurs when you follow the steps as below: 1.Create a Word file, paste the web URL.
Power Query is included with Excel 2016 (Office 365). It has been renamed and is now on the Data tab of the Ribbon in the Get & Transform section. This means there is nothing to install. If you are using Excel 2016, go to the Data tab on the ribbon and press the New Query button to create a query and open the Power Query editor.
2.Save the Word as.iqy with.txt format. 3.Choose MS-DOS as coding.
4.Create an Excel file and click 'Data Get External Data Run Web Query (Or Run Saved Query) ' of the Bar. Besides, here is for you to use. The workaround may help. Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.
Hope this to help you. Best Regards, Eli. H Eli, In response to your question I'm using: Microsoft Office 2016 for Mac 1000 Microsoft Word Updater Package1302 Word 16.14.1 Excel 16,14.1 Here is the procedure I use: 1.Open Excel for Mac 2016 to a sheet where you want your data to reside 2. Open new Word document 2. Paste the URL for website I am interested in into the new Word doc 4.
Place cursor to the right of the URL and click 5 times 5. Click Save As '.txt' format 6. Click MS/DOS coding 7.
Save document to desktop folder I have named 'Queries iqy' 8. Open the desktop 'Queries iqy' folder and rename the.txt file as a '.iqy' document 9. Close Queries iqy folder 10.
Go back to the Excel sheet where I want the data to reside and click the on cell in upper left corner of the worksheet (or wherever you want to place the data) 11 Then click on top level 'Data' tab 12. Then click on 'Get External Data' 13. Then click on 'Run Saved Query' 14. Go to desktop 'Queries iqy' folder and click on the '.iqy file of interest (I have stored many.Iqy docs there) 15.
I am prompted to select where you want the data to go, which I have already done in step 10, but I select it again 16. I am prompted to fill out my desired 'data range properties' so I select 'save range properties', 'enable background refresh', 'refresh data when opening', 'adjust column width', 'import html tables only', insert cells for new data, delete unused cells' and then click 'Okay' 17. There is also another button that I use to establish the format for the data that will be downloaded, so I select all the 'delimited' data properties that I want.
I then click on the 'get data' or whatever the command is to begin the download. The upper left cell in excel spreadsheet shows 'getting data' and after a brief, wait all the data I want appears. So the download process actually works!! Unfortunately however, it is not in the format which I selected and is completely unusable for additional analysis using Excel.
So, I go back to Data, select 'Text to Columns' and again select all the formatting properties needed. This works, and now the data are converted into a format suitable for further analysis in Excel. HOWEVER, it turns out that after setting up a simple excel program to in order to actually use the downloaded info in meaningful way, I run into other issues: a) The data do not automatically refresh in the background b) I don't know how to manually refresh the data without repeating steps 1-22, which is a total waste of time and for fluid, dynamic, analysis completely unusable! This is where I am. I can do web queries on Excel for Mac but I can't use the data unless 23 (a) and (b) are resolved.
I would appreciate any help you can give me. Thanks, Mike. Hi Mike, Thanks for your detailed information. According to following your steps, it seems that the issue that data won't be automatically refreshed in the background and you don't know how to manually refresh the data without repeating steps 1-22 occurs in current situation. Given this situation, for question 23(a), the properties show that 'refresh data when opening file' is your choice in the step 16. So how about closing this file and opening it again? Does the data get refreshed?
For question 23(b), I would suggest you use the feature button 'Refresh All' in Data tab in Excel. Hope this helps you. Best Regards, Eli.
Hi Here's my web page explaining how to make Web Queries for the Mac. Sounds like you have this part down OK: Your.iqy web query files go into this folder: The User Library folder is Hidden by Apple. Unhide it (these directions work with all current versions of Mac OS). The 3 sample MSN queries don't work. You can trash them.
To run a web query use the Data menu The 'Background query' thing has to do with if you have a really slow internet connection. It lets you do other things in Excel if your connection is slow (like mine is at the moment).
Notice the status bar says it is running the background query. To refresh a query manually, go to the Data tab of the Ribbon and click the Refresh button. Another way to refresh is to right-click in the query table result set and choose Refresh You can also use a Macro to refresh your query using just about any criteria you can think of. I am an unpaid volunteer and do not work for Microsoft. 'Independent Advisors' work for contractors hired by Microsoft. 'Microsoft Agents' work for Microsoft Support. Hi Jim and Eli Haven't had a chance to try this yet, and I will, But.
Are you saying that the only way to get the 'Refresh' button under 'Data' to light up is if the queries are coming from the default 'Queries-Office' file in the Library folder? Is this why my desktop '.iqy folder' containing my queries won't trigger the 'Refresh Data' tab to light up? Also, what about the autoformating issue? How can I get the data to autoformat rather than having to repeat the 'Text to Columns'adjustment every time I download the data? Do you mean that all I have to do is to click anywhere on the 'already downloaded and Text to Columns' adjusted data, and it will format properly? Finally, I don't know how to set up a Macro. Do you mean that I can download Web Query data directly into a Macro and refresh it from there anytime I want?
Thanks for your patience, I really appreciate the help! I'll reply in-line Are you saying that the only way to get the 'Refresh' button under 'Data' to light up is if the queries are coming from the default 'Queries-Office' file in the Library folder? Is this why my desktop '.iqy folder' containing my queries won't trigger the 'Refresh Data' tab to light up? The Refresh button on the Ribbon has two options.
You can click the big part, which defaults to Refresh All queries in the workbook at once. Or, you can select a cell within a result set and choose the other option to refresh only the query having a cell selected in the result set. The right-click option also refreshes only the query for the data range that has the selected cell. Also, what about the autoformating issue? How can I get the data to autoformat rather than having to repeat the 'Text to Columns'adjustment every time I download the data?
Do you mean that all I have to do is to click anywhere on the 'already downloaded and Text to Columns' adjusted data, and it will format properly? Running Text-to-Columns is an action that has to taken by you, or you can use a macro to perform both the update and perform the subsequent Text-to-Columns actions. Finally, I don't know how to set up a Macro.
Do you mean that I can download Web Query data directly into a Macro and refresh it from there anytime I want? I mean you can make a macro that runs your web query and performs the text-to-columns action anytime you want. So the answer to this is, yes. I'll see what I can do to help you get started on working with macros. You need to be able to get to the Visual Basic Editor easily. You do that by using the Developer tab of the Ribbon, which Microsoft hides because of reasons that make no sense to me.
Here's how to turn the Developer tab on. Once you have the Developer tab turned on you will find a Record button. Click it once to start recording, then click it a second time to stop. I would start with that and record the actions of refreshing your query followed by text-to-columns. Then use the Macros button to view your recorded macro and look through the code. VBA code should be somewhat human readable. Can you make sense of the code?
I am an unpaid volunteer and do not work for Microsoft. 'Independent Advisors' work for contractors hired by Microsoft. 'Microsoft Agents' work for Microsoft Support.