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
How to prevent re-mapping each time I upload? (Read 2698 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
How to prevent re-mapping each time I upload?
14.07.06 at 17:02:59
 
How to prevent re-mapping each time I upload?
 
Mark from the USA asked the following question.
 
Question:
I am using the multiple rows insert feature of SQL*XL to insert data from Excel into my database.
I cannot save a mapped definition, I have to re-map each time I uploaded.  Again, maybe I missed something along the
way....
 
Answer:
You can simply record a macro when you do this the first time. Use the macro again the next time you need to run the process so you don't need to go through the dialogs again.
 
The code that will be recorded is something like this:

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

 
If you change the code to use the Selection (all selected cells) you make it independent on the number of rows you want to insert. Just select all rows you want to insert and start the macro:
 

SQLXL.InsertRecordset Table:="Authors", Columns:="Au_ID,Author,[Year Born]", DataRange:=Selection, PromptOnError:=False, SortToStatus:=True, CommitFrequency:=50, Orientation:=1, Silent:=True, Feedback:=True

 
References:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1141037839
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133159596
Back to top
 
 

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