embedding excel colum value as a constant in a macro

cheers, I tried that but I now get a run time error

"url does not use a recognized protocol"

any ideas?

Hi,

From the error message you reported, it sounds like after you assigned the value of cell [A1] to the Public variable, url was then used in an Open statement or another method of retrieving the contents of an external page/file.

I presume you do realise that "tom's website" is not a Uniform Resource Locator [URL], more like the title of a web site.

A URL would be something like [ http://google.com ].

Please can you post the entire set of code statements you are using & confirm what is in cell [A1] (or whatever cell you are using as the source of the value of the Public variable)?

Thank you.

BFN,

fp.

Cheers

see excerpt code below

Public url As String

Sub AssignValue()

url = Range(L1).value

End Sub


'------------------------------------------------------------------------------
Private Sub GetData(os As String, where As String, attrList() As String)
    Dim objHTTP, objXML, objSet, obj, objAttr
    Dim i As Integer
    Dim row As Integer
   
    'Create HTTP call
   
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
   
  objHTTP.Open "POST", url + os, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"

the issue comes up when the POST url is called


 "  
   HTTP.Open "POST", url + os, False
"

Thank you.

Your code demonstrates what I described above.

You are trying to "post" (send data) to an invalid Uniform Resource Locator [URL].

The value in cell [A1], "tom's website", is not a valid address for reaching a web site, or a service running on a web site.

However, your code is using the value in a cell referenced by the variable (or the constant) value in "L1".

What does "L1" contain, & if this is a cell reference other than [A1], what is the different cell's value?

Hi Fanpages,thanks for assisting, appreciate your time, I changed my AI filed to L1 instance


this is the below const that works:

Option Explicit

 'Global Const URL = "http://mywebsite:7001/integration/os/"

I would though like to read this from the excem sheet itself , this is what i used but still gives the error:

Public url As String

Sub AssignValue()

url = Range("L1").value

fanpages

finding no value passing thru, also the OS appendment is a requirement to define the object structure (needed by the app) to read and write the values from excel to and from

may I post the sheet to u privately?

The workbook should be attached to this thread if the discussion is to remain at Experts-Exchange.com.

If the content of the workbook contains sensitive information, then I would suggest "blanking" ("beclouding" via obfuscation) this data, such as simply replacing text/values to "XXXX" (or similar), before uploading/attaching any file to this thread.

Are you able to do that?

Alternatively, can the worksheet cell [L1] value be read from within the GetData() routine or, if not, can the value be passed as a parameter as well as the parameter "os"?

fanpages

i rebuilt the sub

Option Explicit
Public Sub AssignValue()
Dim Url As String
Url = Range("K1").value
MsgBox Url
End Sub

can you verify this is sound coding practice (im a raw novice at this) :)

this i tested on a new sheet to see if it compiled, then I added the rest and it worked, prob some dodgy characters too preventing the compilation,hence not being able to view the url initially in the msgbox you suggested me to work with

thanks for ur help

Did you intend to accept the first comment as the "solution"?

hmm, my bad, i thought it was u who posted it fanpages,it did set me in the right eay but you gave the most support here, is there a way I can reverse it? i guess it was not the most complete answer

Your own answer was the "solution", so I have requested attention for this thread so that one of the Moderators, or Topic Advisors, can retract your initial acceptance in order for you to accept your own comment.