Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Macro coding to automate batch insert (Read 14785 times)
liew
YaBB Newbies
*




Posts: 4
Macro coding to automate batch insert
28.11.05 at 07:33:16
 
How could I use macro coding to do a batch update and insertion of rows of records into a current table i.e via a button click? That is, I have done a retrieval of all records based on a table with sql where clause.
 
Users are allowed to insert new rows for new records in between retrieve records as well as updates to the existing records.
 
How could I use macro coding on SQL*XL commands to event handle the necessary insertion and updates?
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Macro coding to automate batch insert
Reply #1 - 28.11.05 at 09:55:01
 
Dear Liew,
 
Thank you for your question. Yes this is entirely possible. With macro recording you actually get to nearly 100% here. There are a couple of techniques you will need to use. See the following references for more information:

The first thing you need is to retrieve the data you want to modify. You have already done the retrieval of data and you have probably already put a button on the worksheet to run your macro.
 
Now you should remember to not loose your database connection. The last retrieved results from a select statement can be updated but only if you do not logoff the database.  
 
Any cells your users modify can be sent back to the database. You need to use the multiple rows update feature of SQL*XL. You can start the multiple rows update dialog with this command: SQLXL.mnuUpdateMulti_Click. This is the event handler that is also used when you use the SQL*XL menu item or toolbar item for the multiple rows update feature. So a simpel button to start the update dialog is sufficient to implement sending changes back to the database.
 
The last thing to achieve is the addition of rows. You need to make an area on the worksheet where your users can put the new rows. Perhaps you can let your users add rows at the end of the worksheet. Note that you need to find out yourself where the new rows start. Use the macro recorder again to record the inserting of records. Modify the part of the code where you tell SQL*XL where the new rows are. You need to make this variable. One time your users can add only one row, the next time they may add 100 rows. This is the code the macro recorder will record:
Code:
Sub InsertMultipleRows()
  SQLXL.InsertRecordset Table:="Authors", Columns:="Au_ID,Author,[Year Born]", DataRange:=Range("$A$6:$C$11"), PromptOnError:=False, SortToStatus:=True, CommitFrequency:=50, Orientation:=1, Silent:=True, Feedback:=True
End Sub
 


 
This part of the code you need to change: DataRange:=Range("$A$6:$C$11")
You need to give the range of cells where the user typed his new rows. The easiest way is to let the user select all the cells before pressing the button you are going to make to insert the rows. The code will become: DataRange:=Selection. You could perhaps build in some checks. You could check whether the number of columns in the selected range matches the number of columns you expect.
 
There are other techniques too (less error prone perhaps ; the user could make a mistake with the selection). It involves the Excel methods Range, Cells, Rows and Columns. The easiest way to start experimenting is by using macro recording again. A technique I have used is to record the selection of a block of filled cells. If you hold the Shift key and use Ctrl- arrow right, Excel will select all filled cells to the right. When you continue to hold the Shift key and use the Ctrl- down key now, Excel will widen the selection to all filled cells down. When you record this as a macro you have a way to select all filled cells from a certain start cell. The start cell is the first cell below the retrieved data. The block of selected cells are the new rows.
 
Hope this helps.
Back to top
 
« Last Edit: 28.11.05 at 10:17:45 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
liew
YaBB Newbies
*




Posts: 4
Re: Macro coding to automate batch insert
Reply #2 - 28.11.05 at 10:37:48
 
Thanks for your reply.
 
Is there any way to cater for a dirty bit checks of which record is insertion or update? What happen is all records were retrieved based on country. User needs to insert new record in between by country level. Hence, not possible to advise them to use another area at end of worksheet for example.
 
Any advice is welcome.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Macro coding to automate batch insert
Reply #3 - 28.11.05 at 10:56:01
 
If you allow your end user to insert a row in the retrieved resultset please do not use the update function. The update function assumes that there are no inserted or deleted rows or columns. Also do not change the order of the rows. SQL*XL need to find the data exactly as it put it there barring modifications to the values of course.
 
If your application required the user to add a row in between of old rows you need to somehow collect these in one area to submit them to SQL*XL. I guess I can change the code to allow you to set the DataRange parameter to a range where you have multiple ranges of cells, something like this: DataRange:="A1:D1,A10:D10,A15:D15". Still you would need to find out that row 1, 10 and 15 are inserted which would not be trivial to do. On the other hand you are free to call the insert function for each new row giving only the data range for one row at a time.  
 
You could mark the inserted rows somehow and just before you run the insert process collect the new data in another area of the spreadsheet to do the insert. You could e.g. put a "I" in front of any row that was inserted so you can loop all rows finding all rows with an "I" to find the inserted rows. There are events in Excel too that fire when you insert rows. With some effort you could write some code that remembers which rows were inserted.  
To collect all inserted rows you could also use the sort function to group all inserted rows together, do the insert and then sort the data in their orinigal order.
Back to top
 
« Last Edit: 03.04.07 at 16:47:28 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1