VBA - open excel, find and replace, delete row, save as csv -


i trying write program in vba can remotely manipulate excel file sas (a statistical programming software). want program accomplish following:

  1. open specified excel file
  2. find , replace blanks in header row nothing (e.g., "test name" become "testname")
  3. delete second row if first cell in row (i.e., a2) blank
  4. save file csv

i not know vba, have dabbled little, know other programming languages, , have tried piece together. stole code make 1 , 4 work. cannot 2 , 3 work. have:

i put following in sas call vba program -

x 'cd c:\location';/*change location of vbs file*/ x "vbsprogram.vbs inputfile.xls outputfile.csv";

the vba program -

'1 - open specified excel file if wscript.arguments.count < 2     wscript.echo "error! please specify source path , destination. usage: xlstocsv sourcepath.xls destination.csv"     wscript.quit end if dim oexcel set oexcel = createobject("excel.application") dim obook set obook = oexcel.workbooks.open(wscript.arguments.item(0))  '2 - find , replace obook.worksheets(1).range("a1:g1").select     selection.replace what:="* *", replacement:="", lookat:=xlpart, _         searchorder:=xlbyrows, matchcase:=false  '3 - delete second row if blank obook.cell("a2").specialcells(xlcelltypeblanks).entirerow.delete  '4 - save csv obook.saveas wscript.arguments.item(1), 6 obook.close false oexcel.quit wscript.echo "done"

any assistance pointing me in right direction appreciated.

also, there way select data in row 1 range (in part 2) , not have specify set range of cells?

try:

'2:   - find , replace obook.worksheets(1).cells(2,1).entirerow.replace " ", ""  '3 - delete second row if blank if oexcel.counta(obook.worksheets(1).cells(2,1).entirerow) = 0   obook.worksheets(1).cells(2,1).entirerow.delete end if

Comments

Post a Comment

Popular posts from this blog

linux - xterm copying to CLIPBOARD using copy-selection causes automatic updating of CLIPBOARD upon mouse selection -

i've tried various ways coerce xterm (versions 285 , 292) copy selection clipboard clipboard whenever press ctrl-shift-c. promising way far has been, in ~/.xresources, put this: xterm*vt100.translations: #override \ ctrl shift <keypress> c: copy-selection(clipboard) \n\ ctrl shift <keypress> v: insert-selection(clipboard) ctrl-shift-v works perfectly, copying has nuance... if restart xterm, highlighting text puts things in primary clipboard; expected, proper, default behavior. if hit ctrl-shift-c, copies current selection clipboard clipboard. the bug, however, if highlight text after first time pressed ctrl-shift-c, you'll see highlighting copies both primary and clipboard. cannot figure out how stop xterm updating clipboard upon selection... , doesn't make sense me in first place. i said, @ specific point in time, copy selection clipboard... yet starts updating automatically upon selection after doing once... anyone have workaround...
Read more

c++ - qgraphicsview horizontal scrolling always has a vertical delta -

Image
i've subclassed qgraphicsview custom canvas used in cad application. i've reimplemented qgraphicsview::wheelevent check keyboard modifiers control key and, if control key pressed, zoom. i'm trying implement horizontal scroll when user holds shift , uses wheel. the problem i'm having horizontal scrolling always scrolls 0.279. not huge problem, hugely annoying , points else being wrong. so, here questions: is right way implement horizontal scrolling? if not, is? how eliminate delta of 0.279? thanks in advance. code , sample output below void myview::zoom(int delta) { double factor = pow(1.2, delta/abs(delta)); this->scale(factor, factor); } void myview::scrollhorizontal(int level) { qpointf center = maptoscene(viewport()->rect().center()); qdebug() << "center: " << center.x() << ", " << center.y(); centeron(qpointf(center.x() - level, center.y())); } void myview::wheelevent(qwheeleve...
Read more