opening an excel file from a website using vba

[Jayne22]

Is there a way to open and activate an excel spreadsheet from an online
source using vba? I am attempting to write a function in one spreadsheet that
calls upon and performs a function on another sheet; unfortunately this is a
spreadsheet from an online site that changes daily.

I know how to open and activate a sheet that is within my own folder, but
this is obviously more complex.

 

[Don Guillett]

More detail and url(s) along with examples. You can probably use an external
query to make it all automatic with one mouse click.

 

[Jayne22]

The website is internal and requires a password, so I can't post that
address. But the spreadsheet shows vacation days for our team and i would
like to call that spreadsheet up, count the number of vacation days for a
particular person, and then post that on the original spreadsheet.

Is this possible?

 

[Tom Ogilvy]

do
File=>Open
then select the file by nagivating to it like you would any other file. The
challenge will be finding the path to the file. Coordinate that with your IT
department if you don't know how to get to it. (if you can't do that, VBA
won't be able to do it either I would suspect).

Then if you need code, turn on the macro recorder while you do it manually.

 

[Tim Williams]

Workbooks.Open "http://someserver/path/goes/here.xls"

Tim

 

[Jayne22]

Perfect! Thanks for the help.

One more question: After I count up the cells in the spreadsheet I just
opened, what code do I use to put that value into the initial spreadsheet
rather than the one I opened?

Can I just reactive the initial spreadsheet?

 

[Tim Williams]

If the initial spreadsheet is the one containing the code then

'*************************
ThisWorkbook.Sheets("Sheet1").Range("A1").Value = cellsCount
'*************************

should do it.

If the initial sheet is not in the workbook with the code then

'*********************************************
Dim wbInitial as WorkBook
set wbInitial = ActiveWorkbook

'open the web file and get the count...

wbInitial.Sheets("Sheet1").Range("A1").Value = cellsCount
'*********************************************



Tim