Need Excel macro to open Sharepoint library file and save it to my hard drive as an .xlsx file or import into my workbook

dixiesstar

New Member
Joined
Jul 5, 2012
Messages
2
Hi! I'm stuck on this one and have been trying to figure it out all day, with no luck. I'm pretty new to VBA and macros so what is easy to a lot is quite the challenge for me. I have a workbook that I need to put some code into that will go to a specific sheet then open up a SharePoint document and then either copy some data I need to my workbook or save the document in my "data" folder on my hard drive. I have some code below that works, but am stuck at how to proceed.

The code below goes to my sheet and then opens an internet explorer page and goes to the link (in cell A1 of the sheet). I have tried to use the "Get External Data" (from Web) function on the Data Tab of Excel and record the macro so i can see the steps, but it always errors out and doesn't record. The data will not import into my workbook. I checked and my java script is enabled, so I don't know why it won't import in. It may have something to do with the fact that the arrow icons (to select the table to be copied) are not where I need them in order to select the data. By the way, I have Excel 2007.

Any help would be GREATLY appreciated!!

One more thing (maybe it will be useful): the URL address for the SharePoint document ends in .aspx: http://abc.123.com/group1/group2/group3/-layouts/xlviewer.aspx


Code:
    Application.ScreenUpdating = False
    Sheets("Sheet1").Select
    Range("A1").EntireRow.Hidden = False
    Range("A1").Select 'CELL A1 CONTAINS THE FILE PATH
    FileLoc = ActiveCell.Value
    
    'THIS OPENS UP AN INTERNET EXPLORER PAGE AND GOES TO SHAREPOINT, TO THE APPROPRIATE LIBRARY,
    'AND SELECTS THE DOCUMENT (OPENS LOOKING LIKE EXCEL, BUT STILL GIVES OPTION TO "OPEN IN EXCEL"
        Dim web
        Set web = CreateObject("INTERNETEXPLORER.APPLICATION")
        web.navigate FileLoc
    
    'NEED CODE HERE TO DO EITHER OPTION:
        '1. COPY 3 COLUMNS OF DATA FROM THE WEB SCREEN (WHICH IS NOT OPENED IN EXCEL) AND
            'PASTE THEM INTO THE WORKBOOK (THAT THIS CODE IS IN)
        '2. OPEN THE FILE IN EXCEL AND THEN SAVE AS C:MY_DataFolder_NAME.XLSX
    
    'THEN CLOSE THE WEB PAGE
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top