EXCEL - VBA . Getting the cell values as Key Value Pairs -

-

i trying address values excel cells of column 'i' , pass query string url using vba. have embedded 'microsoft object browser' inside excel load page.

is possible? because worried amount of data passed query string high (1000 rows approximate).

the code not working though, there way same passing query string array?

also need vba syntax parse dictionary values.

i new vba. please help.

    dim arr() variant ' declare unallocated array. arr = range("i:i") ' arr allocated array set dict = createobject("scripting.dictionary") dim irow integer irow = 1  dim parms variant    dim rg range     each rg in sheet1.range("i:i")         ' print address of cells negative         'msgbox (rg.value)          'result = result & rg.value           dict.add rg.value           irow = (irow + 1)             next msgbox (dict.item(1)) set dict = nothing 'webbrowser1.navigate2 "http://localhost/excelmaps/maps.php?adr=" & parms end sub

it seems maximum url length ie 2083 characters:

https://support.microsoft.com/en-us/kb/208427

to build query use string builder ("system.text.stringbuilder"). need url encode arguments.

here example building url names/values range [a1:b10] :

sub buildurl   ' read names/values sheet   dim names_values()   names_values = [a1:b10].value2    ' create string builder   dim sb object   set sb = createobject("system.text.stringbuilder")   sb.append_3 "http://localhost/excelmaps/maps.php"    ' build query   dim i&, name$, value$   = 1 ubound(names_values)     name = names_values(i, 1)     value = names_values(i, 2)      if = 1 sb.append_3 ("?") else sb.append_3 ("&")     sb.append_3 urlencode(name) ' adds name     sb.append_3 "="     sb.append_3 urlencode(value) ' adds value   next    ' print result   debug.print sb.tostring() end sub   public function urlencode(url string, optional space_to_plus boolean) string   static tohex(15), isliteral%(127), buffer() byte, buffercapacity&   dim urlbytes() byte, bufferlength&, i&, u&, b&, space&    if space_to_plus space = 32 else space = -1   if buffercapacity = 0 gosub initializeonce   urlbytes = url    = 0 ubound(urlbytes) step 2     if bufferlength >= buffercapacity gosub increasebuffer      u = urlbytes(i) + urlbytes(i + 1) * 256&     if u , -128    ' u+0080 u+1fffff '       if u , -2048 ' u+0800 u+1fffff '         if (u , 64512) - 55296 ' u+0800 u+ffff '           b = 224 + (u \ 4096):       gosub writebyte           b = 128 + (u \ 64 , 63&): gosub writebyte           b = 128 + (u , 63&):      gosub writebyte         else  ' surrogate  u+10000 u+1fffff '           = + 2           u = ((urlbytes(i) + urlbytes(i + 1) * 256&) , 1023&) _             + &h10000 + (u , 1023&) * 1024&           b = 240 + (u \ 262144):       gosub writebyte           b = 128 + (u \ 4096 , 63&): gosub writebyte           b = 128 + (u \ 64 , 63&):   gosub writebyte           b = 128 + (u , 63&):        gosub writebyte         end if       else ' u+0080 u+07ff '         b = 192 + (u \ 64):    gosub writebyte         b = 128 + (u , 63&): gosub writebyte       end if     elseif isliteral(u)  ' unreserved ascii character '       buffer(bufferlength) = u       bufferlength = bufferlength + 2     elseif u - space  ' reserved ascii character '       b = u: gosub writebyte     else  ' space character '       buffer(bufferlength) = 43   ' convert space +  '       bufferlength = bufferlength + 2     end if   next    urlencode = leftb$(buffer, bufferlength)   exit function  writebyte:   buffer(bufferlength) = 37  '%   buffer(bufferlength + 2) = tohex(b \ 16)   buffer(bufferlength + 4) = tohex(b , 15&)   bufferlength = bufferlength + 6   return increasebuffer:   buffercapacity = ubound(buffer) * 2   redim preserve buffer(buffercapacity + 25)   return initializeonce:   buffercapacity = 2048   redim buffer(buffercapacity + 25)   = 0 9:    tohex(i) = cbyte(48 + i): next  '[0-9]'   = 10 15:  tohex(i) = cbyte(55 + i): next '[a-f]'   = 48 57:  isliteral(i) = true:  next '[0-9]'   = 65 90:  isliteral(i) = true:  next '[a-z]'   = 97 122: isliteral(i) = true:  next '[a-z]'   isliteral(45) = true  ' - '   isliteral(46) = true  ' . '   isliteral(95) = true  ' _ '   isliteral(126) = true ' ~ '   return end function

Comments

Post a Comment

Popular posts from this blog

java - Run spring boot application error: Cannot instantiate interface org.springframework.context.ApplicationListener -

-
i have spring project , try make use spring boot , run @ embadded tomcat following : https://spring.io/guides/gs/rest-service/ this application //@configuration //@enableaspectjautoproxy @springbootapplication @componentscan(basepackages = "gux.prome") public class application { public static void main(string[] args) { springapplication.run(application.class, args); } } if use maven command: mvn spring-boot:run , project starts fine, need debug , run main method @ intelij, exception occurs: exception in thread "main" java.lang.illegalargumentexception: cannot instantiate interface org.springframework.context.applicationlistener : org.springframework.boot.logging.classpathloggingapplicationlistener @ org.springframework.boot.springapplication.createspringfactoriesinstances(springapplication.java:414) @ org.springframework.boot.springapplication.getspringfactoriesinstances(springapplication.java:394) @ org.springframework.bo...
Read more

python - pip wont install .WHL files -

-
when try install .whl gohike typing cmd c:\users\owner\downloads>pip install ta_lib-0.4.9-cp27-none-win_amd64.whl i print out: `processing c:\users\owner\downloads\ta_lib-0.4.9-cp27-none-win_amd64.whl complete output command python setup.py egg_info: traceback <most recent call last>: file "<string>", line 1, in <module> ioerror: [errno 2] no such file or directory:'c:\\users\\owner\\appdata\\lo cal\\temp\\pip-9gwk2c-build\\setup.py' ----------------------------------------- command "python setup.py egg_info" failed error code 1 in c:\users\owner\ap pdata\local\temp\pip-9gwk2c-build\` the part between pip- , -build in pip-9gwk2c-build different on same file. i asked python 2.7.11 pip 8.0.3 can't install gohike whl of ta-lib 0.4.9 few days ago , decided ask differently. can me! you have outdated pip not understand wheels. wheels not supposed have setup.py inside. try upgrading pip pip in...
Read more

Excel VBA "Microsoft Windows Common Controls 6.0 (SP6)" Location Changes -

-
i have excel workbook used generate quotations customers. there main page user inputs data, , there buttons generate various pdfs , send them customer. however, 1 user consistently getting error preventing generating , sending pdfs. i beleive "microsoft windows common controls 6.0 (sp6)". it's installed on users' computers. on mine, location "c:\windows\syswow64\mscomctl.ocx" on others "c:\windows\system32\mscomctl.ocx" the user experiencing issues on 32-bit machine, "mscomctl.ocx" file should in system32. have manually put in there, , pointed common controls reference in vba it. issue seems keep reverting looking in syswow64. since folder doesn't exist, can't find it, , can't produce pdfs. i thought perhaps issue user running older version of office (2007) there users on 2013 , 2016. thought perhaps mixture of 64-bit , 32-bit users. user gets sent workbook other users (as quotation process goes further, gets passed ...
Read more