vba - Exporting Access Query to Excel while creating new sheets and formating -


i have database in access in short long list of companies, products sell, , there fiscal sales each on of products. write vba will:

1.) allow me export query excel. 2.) create new sheet each time query run 3.) format data presented.

i can't seem figure out how paste data query new sheet. if kind soul me, appreciated.

i know of possible, i've done bit of research on it. i've run out of time , need figure out i've gone wrong. essential have seen far: (access vba how add new sheets excel?), (formatting outputted excel files access using vba?), (https://www.youtube.com/watch?v=9ydmhzv7nns).

sub mysub() dim objexcel excel.application dim wbexcel excel.workbook dim wbexists boolean dim qdfquery2014sales querydef dim rsquery2014sales recordset  set qdfquery2014sales = currentdb.querydefs("query2014sales") set rsquery2014sales = qdfquery2014sales.openrecordset()  set objexcel = createobject("excel.application")   on error goto openwb wbexists = false set wbexcel = objexcel.workbooks.open("c:\users\mortbanker\documents\test.xls") wbexists = true  openwb: on error goto 0 if not wbexists     set wbexcel = objexcel.workbooks.add() end if  copytoworkbook wbexcel end sub  private sub copytoworkbook(objworkbook excel.workbook) dim newworksheet excel.worksheet set newworksheet = objworkbook.worksheets.add()   newworksheet .range("a1") = rsquery2014sales .columns("a:a").horizontalalignment = xlright .rows("1:1").font.bold = true end 'copy stuff worksheet here' end sub

you need pass recordset object companion sub , use excel.application object's range.copyfromrecordset method perform actual operation.

sub mysub()     dim objexcel excel.application     dim wbexcel excel.workbook     dim wbexists boolean     dim qdfquery2014sales querydef     dim rsquery2014sales recordset      set qdfquery2014sales = currentdb.querydefs("query2014sales")     set rsquery2014sales = qdfquery2014sales.openrecordset()      set objexcel = createobject("excel.application")     objexcel.visible = true       on error goto openwb     wbexists = false     set wbexcel = objexcel.workbooks.open("c:\users\mortbanker\documents\test.xls")     wbexists = true  openwb:     on error goto 0     if not wbexists         set wbexcel = objexcel.workbooks.add()     end if      copytoworkbook wbexcel, rsquery2014sales      'need save workbook, make visible or something. end sub  private sub copytoworkbook(objworkbook excel.workbook, rsqry recordset)     dim newworksheet excel.worksheet     set newworksheet = objworkbook.worksheets.add()       newworksheet         .range("a1").copyfromrecordset rsqry   '<-magic happens here!         .columns("a:a").horizontalalignment = xlright         .rows("1:1").font.bold = true     end     'copy stuff worksheet here' end sub

you won't getting field names; have put in operation. if know field names might want store them in variant array , stuff row 1 en masse. i've made objexcel object visible have not saved or closed it.


Comments

Post a Comment

Popular posts from this blog

javascript - Using jquery append to add option values into a select element not working -

i'm looking dynamically populate select element names of items stored array. for reason it's not working , i'm not sure why. html: <div class="col-md-4 col-md-offset-4"> <select class="select1"> </select> , <select class="select2"> </select> </div> note i'm using bootstrap layout. here's jquery i'm using try , populate ".select1" jquery: select: function() { $.each(state.greens, function(index, value) { $(".select1").append("<option value =' " + index + " '>" + state.greens[index].name + "</option>"); }); } note function 'select' stored within object called 'display'. state.greens name of array i'm trying access. so @ end of html page call display.select(); call function. no error messages showing in console. also, saw question: appending
Read more

Android soft keyboard reverts to default keyboard on orientation change -

i'm building android soft keyboard , can't seem fix bug - have arabic , qwerty keyboard , when rotate device on qwerty keyboard (or arabic shift), it's if program has "restarted" , becomes arabic keyboard without shift. the onsaveinstancestate(bundle savedinstancestate) not work because application not extend activity inputmethodservice . i put following in android manifest android:configchanges="keyboard|keyboardhidden|orientation" android:windowsoftinputmode="stateunchanged|adjustresize"> i tried using @override public void onconfigurationchanged(configuration newconfig) { super.onconfigurationchanged(newconfig); log.i(mydebug, "config changed " + currentkeyboard.equals(qwerty)); } however, currentkeyboard.equals(qwerty)) results false , made sure true before orientation change. any appreciated. i think applications fault. if application restarting on orientation change
Read more

Rendering JButton to get the JCheckBox behavior in a JTable by using images does not update my table -

i rendering jbutton image on simulate jcheckbox behavior. why don't let jtable render jcheckbox? because need bigger checkboxes , there's no way can resize them (as far know). so, have own table cell renderer , editor. both renders jbutton , checkbox images on them. trying change image checked image unchecked image (or vice versa) , update cell value (boolean true or false). however, reason, getcelleditorvalue() not update table. so, release mouse, value goes original one. any appreciated! lot! public class tableexample extends jframe { jscrollpane pane; jpanel panel; jtable table; object[][] data; mytablemodel tm; renderer buttoncolumn; public tableexample(object[][] data) throws ioexception { string[] columns = {"column#1", "column#2", "column#3", "column#4", "column#5", "column#6"}; this.data = data; this.setpreferredsize(new dimension(700, 500));
Read more