Goal: I want to use Excel to automate the export of data from our SharePoint site back into Excel. I'm unable to create a workflow to push this from SharePoint, so I want to pull it from Excel.
Where I'm stuck: When the export begins, a pop-up comes up asking "Open", "Save", or "Cancel". After "Open is selected", an Excel Import Data wizard pops up, which I also need to manipulate. I'm unsure how to control these pop-ups programatically, and recording a macro shed no light (for the wizard).
My code to start the export is pretty lean:
Suggestions on what direction to pursue? I feel like I'll need one approach for the IE pop-up, and another to manipulate the Excel wizard.
Where I'm stuck: When the export begins, a pop-up comes up asking "Open", "Save", or "Cancel". After "Open is selected", an Excel Import Data wizard pops up, which I also need to manipulate. I'm unsure how to control these pop-ups programatically, and recording a macro shed no light (for the wizard).
My code to start the export is pretty lean:
Code:
Private Sub CommandButton1_Click()
Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "[URL="http://gnie.geico.net/sites/Commercial/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=%7B8B0CC00E%2D1DE6%2D490E%2D941A%2D5ABC64A1A9CC%7D&View=%7B6B35109F%2DA398%2D4567%2D86B4%2DF2914928B3B4%7D&CacheControl=1"]http://[/URL]URL_for_export"
ie.Visible = TrueSuggestions on what direction to pursue? I feel like I'll need one approach for the IE pop-up, and another to manipulate the Excel wizard.
