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]

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

    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