Hi,
I have a macro that uses ADO to insert data to my SQL Server.
It works well inserting one row at a time, but I really want to insert a whole range at once.
I believe this is possible but it doesn't like the way I am writing the range which I got from the Microsoft website.
What am I missing here?
The error says "Invalid object name 'Forecast$A2:AS10'.
Please Login or Register to view this content.
Last edited by treva26; 08-05-2014 at 12:44 AM.
Is it that I have set my SQL Server as an ADO connection but not Excel??
Or can I read the range into an array and put the array as the data source in the INSERT statement?
I think you need a dollar sign after the sheet name.
There seems to be a lot on the net about it.3 Export data from Excel to existing SQL Server table
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
I do have a dollar sign after the sheet name:
I think the issue is that form of code is for running on the SQL server and referencing an Excel sheet.Please Login or Register to view this content.
I am running vba code in Excel. So I probably need something quite different.
An array object or something?
Last edited by treva26; 08-04-2014 at 11:15 PM.
Well I am just going to do it by inserting rows of data in a loop, not an entire range.
Although I have made a loop to insert 10 rows with each INSERT INTO statement.
This enters my 3,000 rows of data in 2 seconds rather than 5 seconds doing each row separately.Please Login or Register to view this content.
Interestingly inserting 100 rows at a time takes 10 seconds!
For whatever reason 10 at a time seems to work fastest and most consistently.
My somewhat inelegant final code:
Please Login or Register to view this content.
To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:
strQuery = "SELECT * FROM [Sheet1$]"
You can also delimit the worksheet name with the slanted single quote character (`) found on the keyboard under the tilde (~). For example:
strQuery = "SELECT * FROM `Sheet1$`"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks