VBA code to make web-address as variable dependent.

[mchilapur]

Dear all,
I have a macro to extact a file from a web address. The format is as shown below,

"http://www.xxxxxx.com/xxxxx/xxxxx/xxxx/YYYYMMDD/filename.xlsx"

Here, YYYYMMDD is the date (in specified format), which keeps on updating for everyday and is time dependent.
For example,
If am trying to get this link before 3PM of the day, this date will previous day date.If its more than 3PM, then it will be todays date. In simple words, this date will be previous day or present day date depending upon time (3PM)
I want to automate this link based on this Date and time critaria.

Secondly,
I want to take this 'filename.xlsx' name from a cell value,means, i will ask user to enter file names into excel cells (as there are many files) and keyin this file name into this web address from the excel cell. As of now consider 2 cells with 2 different file names.

I hope i am clear.

Please provide me the VBA code to achieve both of the above.

Thanks for all your great help..

Regards,
Madvesh.

[6StringJazzer]

Please Login or Register  to view this content.

[Leith Ross]

Hello mchilapur,

Here is UDF that will take the file name from a cell a return the URL based on the time.

Please Login or Register  to view this content.

[mchilapur]

Hey Thanks for your fast response..
I dont why, above both VBA codes didnt link to web address.

Later i removed BaseURLvariable and modified code as below
Workbooks.Open Filename:= "http://www.xxxxxx.com/xxxxx/xxxxx/xxxx/" & DateStamp & "/" & Range("A1")

This work good...
Thanks again..Have a gr8 day...

[6StringJazzer]

I dont why, above both VBA codes didnt link to web address.

You didn't say you wanted to link to it, just that you wanted the URL. You said you already had the code to extract the file. My code provides a URL string that can be used any way you want in the code, and Leith Ross used equivalent logic to build a UDF that can be used in a worksheet or from VBA to provide a URL to the file.

Getting the right answer depends on asking the right question I'm glad you worked out the last bit and got what you needed.

[mchilapur]

Hmmm..Thats true..Sorry and thanks.