none
Open a Url, download File and Save it using excel vba code RRS feed

  • Question

  • Hi,

    I m using SharePoint 2010 list, which I need to export to Excel and save it on my local drive. I could do that easily using point-click interface from SharePoint itself.But I need to do it using vba. I m using Url protocol shown below to export my list to a Microsoft Excel worksheet.

    http://Server_Name/[sites/][Site_Name/]_vti_bin/owssvr.dll?CS=
       109&Using=_layouts/query.iqy&List=GUID&View=GUID&CacheControl=1

    On pasting this Url to the browser with my Server name, site name and appropriate GUIDs, I get

    "Do you want to open or save owssvr.iqy from Server_Name"?

    I want to Open owssvr.iqy file. So I hit Open button manually. Then it shows a small window saying Microsoft Office has identified a potential security concern. File path:C:\..Temporary Internet Files\Content.IE5\RUHUI25\owssvr.iqy

    Data Connections have been blocked. If you choose to enable data connections, your computer no longer be secure.Do not enable this content unless you trust the source of the file". I click on Enable button. Then under a new workbook, in worksheet names owssvr, it dumps all my SharePoint list data. I change the name of worksheet from owssvr to some meaningful name such as EmployeeList and  click on Save As and save it as EmployeeList.xlsx to the drive where I want to save .

     I need to write excel vba code to automate this process. How do I do it? Any thoughts? Since I m a newbie in vba, any help/guidance is much appreciated.Thanks.

    Friday, April 20, 2012 1:37 PM

All replies

  • Hi

    You can use the WebQuery to get the data from Excel -->Data Tab-->From Web. This will show you the Dialog where you can specify the webpage etc.

    You can try using QueryTables for doing the same from VBA

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com

    Sunday, April 22, 2012 4:49 PM
  • In VBA code we can use 

    CreateObject("InternetExplorer.application")

    And we can use navigate method of create object.

    then we have to specify the website link

    OPENINTERNET ("http://www.accessguru.net/MS%20Access%20VBA%20Articles.php")

    For download a file from website we have to specify the path. Automatically the file is download.


    I have a code with similar kind of functionality implemented in VBA on MS Access Form.

    Here is link: 

    http://www.accessguru.net/Articles_MSAccess/0047-Create%20a%20log%20file%20in%20MS%20Access%20using%20VBA.php
    • Edited by AccessGuru Thursday, July 24, 2014 10:06 AM
    Thursday, July 24, 2014 9:11 AM