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
InsertRecordset (Read 4131 times)
grahamf
YaBB Newbies
*


I Love SQL*XL

Posts: 13
InsertRecordset
19.03.07 at 16:09:27
 
- I've been playing with InsertRecordset - trying to get the column names from a range.. and I can't get it to work... e.g.
 
   SQLXL.InsertRecordset table:="SCRATCH_1", Columns:=range("_test"), DataRange:=Range("$B$3:$B$8"), PromptOnError:=False, SortToStatus:=True, CommitFrequency:=50, Orientation:=2, Silent:=True, Feedback:=True
 
doesn't work (invalid type - as its a range - not a string) - and using the "address" function gives me "invalid character" errors as the $A$3:$A$8 are really not liked in the SQL generated.
 
Suggestions please?
Graham
 
 
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: InsertRecordset
Reply #1 - 20.03.07 at 07:28:27
 
The parameter types you used are not correct. You should use a comma separated list of column names. Please refer to the SQL*XL help in the VBA section. Read: http://www.oraxcel.com/projects/sqlxl/help/vba/SQLXL/index.html  
 
InsertRecordset
( Table As String
, Columns As String
, DataRange As Range
, PromptOnError As Boolean
, CommitFrequency As Long
, Orientation As Integer
, Feedback As Boolean
)
 
Description:
Inserts a block of data into the database. This is the function being executed when the ok button is pressed on the multi row insert dialog.
 
Note:
Note that the columns parameter is a comma separated list with the column names in the table.
 
Example:
InsertRecordset( "emp", "empno, ename", Range(A2:B5), True, 10, litHorizontal, True)
Back to top
 
« Last Edit: 20.03.07 at 07:31:51 by Gerrit-Jan Linker »  

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


I Love SQL*XL

Posts: 13
Re: InsertRecordset
Reply #2 - 20.03.07 at 09:47:47
 
Quote from Gerrit-Jan Linker on 20.03.07 at 07:28:27:
The parameter types you used are not correct. You should use a comma separated list of column names.

 
Understood - perhaps this could be re-filed under "Feature Request" then - as it would be great to specify the field names directly from a range (after all that's how you actually specify it in the dialog - then you read these and extract the text names yourself.). It just makes it orthoganol with the data range specification then.
Regards
Graham
Back to top
 
 
  IP Logged
Pages: 1