VBA code with data connections

[goldendroid]

Trying to write macro for a data connection that changes from week to week. For example, web address would be ***week1 for first week and ***week2, etc. all the way to 52. In order to compensate for this, I was trying to put the week number in different cell and have the macro look at this cell and update the data connection accordingly. How is the macro written to do this? Portion of code is below. site replaced with *** to protect the innocent.

.Connection = _"URL;http://***week=1"

I've tried to replace 1 with "A1" and similar combinations but that doesn't work. Please help. I could copy and paste this 52 times for each week but I'd rather take the easy way. It's just I can't figure it out.

 

[mole999]

.Connection = _"URL;http://***week=" & A1

 

[goldendroid]

.Connection = _"URL;http://***week=" & A1

THat isn't working for me. I've double checked the connection string after doing this and the string stops at week= and doesn't enter the value from A1. I've changed the quote to the end after A1 and string changes to week= & A1 as the text string and not capturing the cell value.

 

[mole999]

sheets("A").range("A1")

or Dim update_value as string

update_value = sheets("A").range("A1")

 

[goldendroid]

Dim update_value as string

update_value = sheets("A").range("A1")

I'm having success with this. Thanks mole.