sql server - Automated export of a table/query from SQL SVR 2012 into Excel 2007 and subsequent VBA formatting macro? -

-

i’ve been given task of updating code isn’t working after our sql svr 200 sql svr 2012 (don't laugh) conversion. have automated task creates 500ish xls files in line-by-line manner using old sp_oacreate command. in 2000, job takes hours , rickety @ best. needless say, doesn’t work @ in 2012. that's never upgrading?

i rewrote job constructing table, adding info table, & doing bulk export xls (using openrowset). new task ran in less 15 minutes , ecstatic. ops complained new files weren’t formatted…

i looped , tried formatting automation. that’s fell apart.

  1. use openrowset , export “xls template file” has autorun vba code formatting when file opened first time. didn’t work.

  2. use openrowset , export “xlsm template file” has autorun vba code formatting when file opened first time. didn’t work.

  3. use bcp , export “xls template file” has autorun vba code formatting when file opened first time. didn’t work.

  4. use bcp , export “xlsm template file” has autorun vba code formatting when file opened first time. didn’t work.

  5. use bcp/openrowset , export xls/xlsx , try execute vba code workbook…didn’t work.

  6. use bcp/openrowset, export “dummy” xls/xlsx, use bat file recopy file, resave, etc…didn’t work.

  7. use bcp/openrowset, export “dummy” txt/csv, use bat file convert file excel, resave, etc…didn’t work.

  8. etc. didn't work.

i tried every combination, every export method, every filetype, etc. methods allowed me export no formatting. other methods wouldn’t export anything. no method permitted export/format, though.

then discovered “real” problem ==> when export using bcp/openrowset , try open file, “file trying open in different format specified file extension” error (fyi using excel 2007). had sort of ignored error, after days of banging head against wall can see error leading me real problem along. export file (regardless of xls/xlsm/xlsx) not actual excel file; it’s bunch of html tags. why formatting vba code won’t work; it’s not actual excel file! no matter do, it’s still not real excel file. formatting never work because it's not real excel file.

so, need automated method export table sort of excel format (xls/xlsm/xlsx) , execute vba code format (bold, column width, number/date formatting, etc.) newly-exported file. seems such routine task…but see routine <> easy. i’ve seen references npoi , closedxml in forums, can’t believe need additional 3rd party software accomplish task.

you write vba code in excel spreadsheet go data database , formatting vba after. below code should starting point.

please note have add reference microsoft activex data objects library code function. also, may need change provider in connection string deepening on systems configuration. below code utilizes sql server native client 11.

dim cn new adodb.connection dim sqlrs new adodb.recordset dim sqlcommand string  sqlcommand = "select...from..."  cn.connectionstring = "provider=sqlncli11;server=xxxxxxx;database=xxxxxx;trusted_connection=yes;" cn.open cn.execute "set nocount on" ''required complex t-sql queries  sqlrs.cursorlocation = aduseclient call sqlrs.open(sqlcommand, cn, adopenstatic, adlockbatchoptimistic)  activeworkbook.sheets("sheet1").range("a1").copyfromrecordset sqlrs  sqlrs.close set sqlrs = nothing cn.close set cn = nothing

Comments

Post a Comment

Popular posts from this blog

Fail to load namespace Spring Security http://www.springframework.org/security/tags -

-
i have problem namespace of spring security on xhtml page. don't understand why can't load uri, , have following issue: whitelabel error page application has no explicit mapping /error, seeing fallback. tue jul 21 16:58:12 utc 2015 there unexpected error (type=not found, status=404). please need help! possible uri has been changed? best regards you using incorrect namespace url. correct url http://www.springframework.org/schema/security the error page seeing spring source website since not recognize url. try visiting http://www.springsource.org/security/tags in browser update : spring security tags used in jsp page correct tag lib declaration is: <%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags" %>
Read more

sql - MySQL query optimization using coalesce -

-
can explain me why second query faster first? (first 1 takes around 40 seconds, second 1 less 0.1) select releases.feature_code, releases.platform_id, version, release_date, general_available, uri, filesize, display_name, feature_name.full_name, if ((select exists(select 1 release_exceptions releases.feature_code = release_exceptions.feature_code , releases.platform_id = release_exceptions.platform_id , releases.version = release_exceptions.version) = 1), 1, 0) release_exceptions releases left outer join platform using(platform_id) left outer join feature_name using(feature_code) order version desc; ~~~ query 2 ------------------ ~~~ select feature_code, version, release_date, general_available, platform_id, uri, filesize, display_name, feature_name.full_name, coalesce(release_exceptions, 0) release_exceptions mus.releases left outer join ( select distinct feature_code, version, platform_id, 1 release_exceptions mus.release_exceptions ) release_exceptions using ...
Read more

unity3d - Unity local avoidance in user created world -

-
i'm using unity3d networked multiplayer online game have large complex 3d terrain scene forest, trees, cliff, hills, mountains, bounders, etc. players can build structures sort of minecraft, , put them anywhere in scene, or move them around anytime. aside human controlled players, there automated ai players , objects animals roaming around scene following path. the problem how make these automated ai players , animals, able navigate around static , dynamic player created structures, because path follow can blocked player created structures, or other players , other ai objects, cliffs etc. have find away around them or on track if tumble down off high cliff example. so made navmesh , used navagents, takes care of static, non moving objects, how make ai players navigate around each other , dynamic structures created players can number in hundreds? i thought of adding navmeshobstacle everything, result in being attached hundreds of objects since user created structures b...
Read more