excel - VBA date double quotation mark gives triple quotation mark -

-

i have strange problem excel vba code. have date string column in csv spreadsheet wrap around in double quotation marks. wrote vba script in excel achieve this. however, everytime run script, date string output wrapped in 3 sets of double quotation marks instead of one.

i this:

"""2015-11-11 00:00:00.40""",59845,-0.20375,3.447,2.0135,32.08286,12,32,11.6

instead of this:

"2015-11-11 00:00:00.40",59845,-0.20375,3.447,2.0135,32.08286,12,32,11.6

where going wrong? have tried using char(34) , chr(34) represent " has not helped. here script:

    sub quotations() ' ' quotations macro '  ' dim strfile string     strfile = dir("e:\copy\*.csv")      ' create new workbook , assign variable     dim wb workbook       while len(strfile) > 0     set wb = workbooks.add     windows("personal.xlsb").activate     wb.activate     activesheet.querytables.add(connection:= _         "text;e:\copy\" + strfile, destination:= _         range("$a$1"))         .name = "e:\copy\" + strfile         .fieldnames = true         .rownumbers = false         .filladjacentformulas = false         .preserveformatting = true         .refreshonfileopen = false         .refreshstyle = xlinsertdeletecells         .savepassword = false         .savedata = true         .adjustcolumnwidth = true         .refreshperiod = 0         .textfilepromptonrefresh = false         .textfileplatform = 850         .textfilestartrow = 1         .textfileparsetype = xldelimited         .textfiletextqualifier = xltextqualifierdoublequote         .textfileconsecutivedelimiter = false         .textfiletabdelimiter = false         .textfilesemicolondelimiter = false         .textfilecommadelimiter = true         .textfilespacedelimiter = false         .textfilecolumndatatypes = array(2, 1, 1, 1, 1, 1, 1, 1, 1)         .textfiletrailingminusnumbers = true         .refresh backgroundquery:=false     end     range("j1").select     activecell.formular1c1 = "=chr(34)&rc[-9]&chr(34)"     range("j2").select     columns("j:j").columnwidth = 23.57     range("j1").select     selection.autofill destination:=range("j1:j864000")     range("j1:j864000").select     selection.copy     columns("a:a").select     selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _         :=false, transpose:=false     columns("j:j").select     application.cutcopymode = false     selection.delete shift:=xltoleft     'activewindow.smallscroll down:=-6      activeworkbook.saveas filename:= _         "e:\copy\quotes\" + strfile, fileformat:= _         xlcsv, createbackup:=false     activewindow.close savechanges:=false     strfile = dir     loop  end sub

what doing wrong? in advance help.

from https://en.wikipedia.org/wiki/comma-separated_values

"fields containing line-break, double-quote, and/or commas should quoted"

when save modified file csv, excel sees quotes added , adds quotes around fields. when doubles-up quotes added escape them (so not interpreted field quotes program parsing saved file)

if re-open csv file in excel you'll see parsed correctly, , single set of quotes added displayed.


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

reactjs - React router and this.props.children - how to pass state to this.props.children -

-
i'm using react-router first time , don't know how think in yet. here's how i'm loading components in nested routes. entry point .js reactdom.render( <router history={hashhistory} > <route path="/" component={app}> <route path="models" component={content}> </route> </router>, document.getelementbyid('app') ); app.js render: function() { return ( <div> <header /> {this.props.children} </div> ); } so child of app content component sent in. i'm using flux , app.js has state , listens changes, don't know how pass state down this.props.children. before using react-router app.js defines children explicitly, passing state natural don't see how now. using couple of react helper methods can add state, props , whatever else this.props.children render: function() { var children = react.ch...
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