Copying data from a MS Access form into Excel -


i have code takes fields ms access form , copies data saved excel file. first record in access in imported excel range of a2:i2. second record in access imported excel range of a3:i3, , on.... happens if close form in access , open up, , had 2 records imported same excel file, , want add third record, start on @ first row (a2:i2) , write on there. question how can i, if close , open access keep starting on over (a2:i2), , instead start @ next available row, follow example given (a4:i4)? code have

private sub command73_click() set objxlapp = createobject("excel.application") set objxlbook = objxlapp.workbooks.open("y:\123files\edmond\hotel reservation daily.xls") objxlapp.application.visible = true  objxlbook.activesheet  set r = .usedrange = r.rows.count + 1  .cells(i + 1, 1).value = me.guestfirstname & " " & guestlastname .cells(i + 1, 2).value = me.phonenumber .cells(i + 1, 3).value = me.cbocheckindate .cells(i + 1, 4).value = me.cbocheckoutdate .cells(i + 1, 5).value = me.guestno .cells(i + 1, 6).value = me.roomtype .cells(i + 1, 7).value = me.roomnumber .cells(i + 1, 8).value = date .cells(i + 1, 9).value = me.employee end  set r = nothing set objxlbook = nothing set objxlapp = nothing  end sub

you can last used row:

set r = objxlbook.activesheet.usedrange = r.rows.count + 1

some notes.

private sub command73_click() ''it idea put sensible names on command buttons. ''it may not seem of problem today, there dim objxlapp  object dim objxlbook  object dim r object dim integer  ''it best check whether excel open before  ''opening copy. set objxlapp = createobject("excel.application") set objxlbook = objxlapp.workbooks.open( _       "y:\123files\edmond\hotel reservation daily.xls") objxlapp.application.visible = true  ''it best specify sheet ''with objxlbook.activesheet  objxlbook.sheets("room reservation")      ''if used range includes empty rows      ''it may not suit     ''set r = .usedrange     ''i = r.rows.count + 1       ''from comments, appears data dense      ''but number of empty rows @ end of sheet       = .range("a1").end(xldown).row + 1      .cells(i, 1).value = me.guestfirstname & " " & guestlastname     .cells(i, 2).value = me.phonenumber     .cells(i, 3).value = me.cbocheckindate     .cells(i, 4).value = me.cbocheckoutdate     .cells(i, 5).value = me.guestno     .cells(i, 6).value = me.roomtype     .cells(i, 7).value = me.roomnumber     .cells(i, 8).value = date     .cells(i, 9).value = me.employee  end  ''tidy set objxlbook  = nothing set objxlapp = nothing  end sub

you might @ transferspreadsheet.

another possibility use recordsetclone, data form, or recordset, matter. not give quite same control, fast:

dim objxlapp object dim objxlbook object dim r object dim integer dim rs dao.recordset  set objxlapp = createobject("excel.application") objxlapp.visible = true set objxlbook = objxlapp.workbooks.open( _       "y:\123files\edmond\hotel reservation daily.xls")  set rs = me.recordsetclone objxlbook.sheets("sheet1")      set r = .usedrange     = r.rows.count + 1      .cells(i, 1).copyfromrecordset rs  end

Comments

Post a Comment

Popular posts from this blog

apache - Add omitted ? to URLs -

i working on website cms reads page name of query string, e.g. http://exmaple.com/?pagename . trying create rewrite rule rewrite ? . so, user types in /pagename , taken /?pagename try this: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^/?(.*) index.php?$1 [l]
Read more

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

i using bbpress installation without wordpress integration. the redirect of rewriteengine @ forum site works: rewriteengine on rewritecond %{http_host} ^forum.mysite.com$ rewriterule (.*)$ http://www.forum.mysite.com/$1 [r=301,l] the problem login pppress. bbpress login not recognize: http://www.forum.mysite/bb-login.php . it redirects to: http://forum.mysite.com/bb-login.php . i tried permalink settings none , name based, same issue. does uses redirect in bbpress or how can eliminate "double" content , redirect permanently www.? i redirected www.forum.mysite.com forum.mysite.com , achieved eliminate "double" content.
Read more

php - How can I stop spam on my custom forum/blog? -

so have custom built forum & blog system of late has been dealing lot of spam. if wordpress use akismet, if different common platform i'm sure i'd find plugin. there kind of static class can download this? using php. i'd still go akismet, if it. uses outside of wordpress, may have pay fee it, depending on use -- check terms , conditions -- it's option , easy implement in php using api. use api key wordpress. com account access. basically, grab whichever php client library takes fancy -- use alex potsides' php5 library -- plug in key, , it's handful of lines of code. here's bare bones of validation straight 1 of live sites: ... if ($akismet) { $akismet->setcommentauthor($name); $akismet->setcommentauthoremail($session->userinfo["email"]); $akismet->setcommentauthorurl(""); $akismet->setcommentcontent($sentence);...
Read more