mysql - SQL Query is updated when user updates Excel -

-

i have excel document want link sql query. in excel document have list of item numbers. whenever item number gets changed want sql query query list of item numbers , return output. want excel sheet use item number parameter database item numbers ? excel item numbers updated daily.

keep in mind mock example trying do. no knowledge of database or spreadsheet, can't guarantee of work. @ least, require make adjustments before can use it.

with in mind, have commented on various parts of code let know going on there. sections have *** areas may want change. sections ### areas have change work you.

this code assumes have list of item numbers in column of sheet 1, each item number return 1 record, , there no blank cells in list of item numbers.

sub grabiteminfo() dim objado new adodb.connection dim objrecset new adodb.recordset dim objcmd new adodb.command dim strconn string dim strsql string dim rownum long dim errnum long  'open connection database '### change properties connection suit needs strconn = "dsn=dsnname; dbq=database; uid=username; pwd=password" objado.open strconn objcmd.activeconnection = objado objcmd.commandtype = adcmdtext  'errnum row error log start on '***change errnum change row starts on errnum = 1 '***changerownum here change row start on rownum = 1 'start loop until thisworkbook.sheets(1).cells(rownum, 1) = ""     on error resume next     '### change sql whatever need     '*** change cells section if you're not using first column     strsql = "select [field] [table] itemnum = " & thisworkbook.sheets(1).cells(rownum, 1).value     objcmd.commandtext = strsql     set objrecset = objcmd.execute     'pastes results query cell next item number     '***change cells section if want use different column     thisworkbook.sheets(1).cells(rownum, 2).copyfromrecordset objrecset     'clear out recordset before loops starts again     set objrecset = nothing     'put item number, error number, , error description on second sheet of work book     '***change sheet number put on sheet if you're using second     if err > 0         thisworkbook.sheets(2).cells(errnum, 1).value = thisworkbook.sheets(1).cells(rownum, 1).value         thisworkbook.sheets(2).cells(errnum, 2).value = err.number         thisworkbook.sheets(2).cells(errnum, 3).value = err.description         on error goto 0     end if     'raise value row next iteration     rownum = rownum + 1 loop  'clear out connection set objado = nothing set objrecset = nothing set objcmd = nothing  end sub

for more information on connection strings, recommend http://www.connectionstrings.com it's great resource use figuring out kind of connection string need. connections strings can be...tricky...sometimes, , helps.

if need resources sql, recommend http://www.w3schools.com/sql have introduction there. past that, reference book, find mentor, join forums(or q&a sites one), etc. if sql tag on site, there more information, along recommended resources well.

good luck.


Comments

Post a Comment

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

-
Image
i have similar problem described in how prevent numbers being changed exponential form in python matplotlib figure : i don't want (in special case) weird scientific formatting of axis. problem different have problem @ z -axis. 2-d plots can use ax.get_yaxis().get_major_formatter().set_useoffset(false) . , there no function ax.get_zaxis() what use format z -axis same way? edit: example: from mpl_toolkits.mplot3d import axes3d import numpy np import sys import matplotlib import matplotlib.pyplot pyplot def func(xi, ti): res = 10e3 + np.cos(ti) * np.sin(xi) return res if __name__ == '__main__': timespacing = 20 timestart = 0 timeend = 1 time = np.linspace(timestart, timeend, timespacing) widthspacing = 50 widthstart = 0 widthend = 3 width = np.linspace(widthstart, widthend, widthspacing) reslist = [] matplotlib.rcparams['legend.fontsize'] = 10 fig = pyplot.figure() ax = fig.gca(projection = '3d...
Read more

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

-
i install web server database on raspberry pi (little computer). computer has 1gb ram. i want know best combination: linux distribution , web server , dbms run local server multiple users minimal latency, use php on server. , best settings performance , not have bugs (memory usage, disable plugin, disable service, etc)? i thought light debian , lighttpd server , sqlite database. solution? i think lighttpd + sqlite great choice. linux distro, debian @ centos or tiny core linux , although i'm not sure of compatibility pi. obviously, can't go wrong raspbian if want use in production , more stable performance, few more pi's , set them in cluster .
Read more

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -

-
i developing app in xamarin android. have splash screen serialize class , pass mainactivity using intent . when try deserialize in mainactivity error message : "serializationexception unable find constructor use types" serialization : void loaddata() { currency currency = new currency(this); intent = new intent(this, typeof(mainactivity)); intent.putextra("currency", newtonsoft.json.jsonconvert.serializeobject(currency)); startactivity(intent); } deserialization : cur = newtonsoft.json.jsonconvert.deserializeobject<currency> (intent.getstringextra("currency")); // error here class constructor : public currency(context context) { thiscontext = context; } i began experiencing problem after added parameter context context constructor. has caused this? how can solve it? possible serialize/deserialize class has parameters? you need have empty constructor in currency class in order deserialize it. ...
Read more