Copy from web hyperlink spreadsheet using VBA

[gilmoreisu]

Preface: I have no training in VBA but can read through simple code and understand it.

Using the recorder I attempted to have the macro click a link which leads to a spreadsheet on the intranet. Copy the data. Paste the data into certain cells.

The program isn't errering out, it just stops after it clicks the link to open the page. I've tried making the macro wait before copying but it seems like the code just dies after opening the spreadsheet.

I've attempted to remake the code without using copy/paste before I realized it dies before the copy is attempted.

    Sheets("Data2").Select   [COLOR=red]'sheet where the data will be pasted[/COLOR]
    Range("C1:I1").Select   [COLOR=red]'This is the cell where the hyperlink is
[/COLOR]    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Workbooks.Open Filename:= _
        "[URL="http://sv2epwsp1001.ent.wfb.bank.corp:8080/dfm/report/view/volumes-capacity?lines=0&output-format=xls"]<link>[/URL]"
    ActiveWindow.Visible = False
    Windows("volumes-capacity.xls").Visible = True
    Sheets("volumes-capacity").Activate
    Range("A1:G5000").Select
    Selection.Copy
    Windows("Filer Capacity Report.xls").Activate
    Range("C3").Select
    ActiveSheet.Paste

    Windows("volumes-capacity.xls").Activate
    Application.CutCopyMode = False
    ActiveWindow.Close

Thanks for any help you all can provide. This seems like it should be simple, but nothing is working for me.

 

[DonkeyOte]

Does the intranet file open embedded within Web Browser (IE, Firefox etc...) ?

Also, see this link http://www.themssforum.com/ExcelProgramming/hyperlinksfollow-result/

 

[gilmoreisu]

It opens in Excel, not in a browser. Sorry I left that off.

Thanks.

 

[DonkeyOte]

Did you manage to have you had a look at the link ?

EDIT - 2 Q's:

In which cell does the hyperlink appear (should only be in one cell though the text may spread out over a few cells)

Also, what is the file called you're opening? Presumably "volumes-capacity.xls" ?

 

[gilmoreisu]

I was working on that link after I replied. I got it to insert the data (it shifts the data to the right) into the Data1 worksheet, but I need it to overwrite the data since this will be run daily. Do you know which of these options would get it to do that?

.FieldNames = True 
.RowNumbers = False 
.FillAdjacentFormulas = False 
.PreserveFormatting = True 
.RefreshOnFileOpen = False 
.BackgroundQuery = True 
.RefreshStyle = xlInsertDeleteCells 
.SavePassword = False 
.SaveData = True 
.AdjustColumnWidth = True 
.RefreshPeriod = 0 
.WebSelectionType = xlAllTables 
.WebFormatting = xlWebFormattingNone 
.WebPreFormattedTextToColumns = True 
.WebConsecutiveDelimitersAsOne = True 
.WebSingleBlockTextImport = False 
.WebDisableDateRecognition = False 
.Refresh BackgroundQuery:=False

The hyperlink is in Range("C1:I1").Select - merged cells - I put it in just C1, it made no difference.

The file that is being opened is called volumes-capacity.xls.

Thanks for the help!

 

[gilmoreisu]

Thanks for the help. I ended up with the following code, and it works just fine. I found xlOverwriteCells would prevent the insertion of the data.

THANKS!

    hypadr = "[URL="http://tm2sdfmbu68:8080/dfm/report/view/volumes-capacity?lines=0&group=223&output-format=xls"]<link>[/URL]"
    With Worksheets("Data1").QueryTables.Add(Connection:="URL;" & hypadr _
    , Destination:=Worksheets("Data1").Range("C3"))
    .Name = ""
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .Refresh BackgroundQuery:=False
    End With