embedding excel colum value as a constant in a macro

folks

I would like my macro module to read the value inputted into an excel sheet column i.e.

a1   tom's website

be embeddded into my code


Option Explicit

Global Const URL =  value of column a1 i.e. tom's website

how do I achieve this?

thanks in advance!
rutgermonsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

etech0Commented:
You would have to use a Variable instead of a Constant, and you would give it a value within a sub (subroutine).

Example:
Option Explicit
Public url as string

sub AssignValue()

url = range("A1").value

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rutgermonsAuthor Commented:
cheers, I tried that but I now get a run time error

"url does not use a recognized protocol"

any ideas?
0
[ fanpages ]IT Services ConsultantCommented:
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.
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

rutgermonsAuthor Commented:
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
"
0
[ fanpages ]IT Services ConsultantCommented:
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?
0
rutgermonsAuthor Commented:
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
0
[ fanpages ]IT Services ConsultantCommented:
Hi again,

Assuming cell [L1] contains:
http://mywebsite:7001/integration/os/

(Note: no quotes; just for clarity)

Also, assuming that the Public variable url does contain the value in cell [L1] *, & that the worksheet containing this value is active at the time (i.e. no other worksheets are active)...

What are you passing as the parameters to:
Private Sub GetData(os As String, where As String, attrList() As String)
?

Specifically, the first parameter, os, as this is added to the url string within the code statement that you reported is failing:

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


* Perhaps you can add this line somewhere in your code after the variable url has been set:

MsgBox url


This will show what is actually within that variable.
0
rutgermonsAuthor Commented:
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?
0
[ fanpages ]IT Services ConsultantCommented:
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"?
0
rutgermonsAuthor Commented:
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
0
[ fanpages ]IT Services ConsultantCommented:
Did you intend to accept the first comment as the "solution"?
0
rutgermonsAuthor Commented:
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
0
[ fanpages ]IT Services ConsultantCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.