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
Template Based Sheet w/ Dynamic Data Range Insert (Read 3614 times)
gruebusch
YaBB Newbies
*


I Love SQL*XL

Posts: 1
Template Based Sheet w/ Dynamic Data Range Insert
05.04.07 at 03:23:13
 
This is the code that will work if you have defined your Spreadsheet to be a Template for your users. I use the first 10 rows as a Template for which the data is to be inserted. The actual data starts in B11. I have added a button on the spreadsheet so the once the user clicks on the button is will do the insert. One other thing is that the template has a predefined set of columns so I know the start column and end column.
 
Here is the code for your macro. (insert all data between column B and BD)
 
    range("B11").Select
    range(Selection, "BD11").Select
    range(Selection, Selection.End(xlDown)).Select
     
    SQLXL.InsertRecordset Table:="TABLE_NAME", Columns:="COLUMN1, COLUMN2...", datarange:=Selection, PromptOnError:=True,      
               SortToStatus:=False, CommitFrequency:=50, Orientation:=1, Silent:=True, Feedback:=True
    SQLXL.Database.Commit
    range("B11").Select
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Template Based Sheet w/ Dynamic Data Range Ins
Reply #1 - 06.04.07 at 08:38:45
 
Yes, this technique can be used to select a range of cells dynamically. If you don't know how many rows the table contains you can use the above code. If you also have a unknown number of columns you can use the Selection.End(xlRight) function to determine the last column. You do need to start with a populated cell though.
Back to top
 
 

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