ADO SELECT INTO - create new worksheet 2

[bartekR]

HI

I have a macro that suppose to select data from a named range in excel and transfer it to the newly created sheet in another spreadsheet using ADo recordset.

It seems like there is a bug in this piece of code :

sSql = "SELECT * INTO ['" & sExportToFile & "'].['" & sNewSheetName & "'] FROM MyTable" 'sDataBase

rsExcel.Open sSql, sConnect, adOpenDynamic, adLockOptimistic

I get "Invalid argument" error here.

When i tried to replace MyTable with the name of an active worksheet : 'sDataBase = "[" & ActiveSheet.Name & "$]"

I got an error saying that Microsoft jet could not find the sheet object.

I am sure about all the spellings, all files exist, paths are correct.

Can anyone spot the bug in my code?
It might be something obvious but i just can't see it right now.

Thank you


here's my code :

Sub adoExcelEXPORT() 'USING RECORDSET

Dim rsExcel As ADODB.Recordset
'Dim sDataBase As String
Dim sExportToFile As String
Dim sNewSheetName As String
Dim sConnect As String
Dim sSql As String
Dim iLastRow As Integer

'CREATE A TABLE TO TRANSFER
iLastRow = Cells(Rows.Count, "a").End(xlUp).Row
Range("a5:c" & iLastRow).Name = "MyTable"

'sDataBase = "[" & "MyTable" & "]" 'if transferring one table
'sDataBase = "[" & ActiveSheet.Name & "$]" 'if transferring the whole sheet

'CREATE PATH FOR A DESTINATION FILE AND NAME FOR A NEW SHEET
sExportToFile = Range("ExportPath")
sNewSheetName = Format(Date, "ddmmyy") & "Download"


'ADO ROUTINES :
Set rsExcel = New ADODB.Recordset

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDataBase & _
";Extended Properties=Excel 8.0;"

'SQL COMMAND - Transfer data from 'MyTable' to newly created Sheet(sNewSheetName) in sExportToFile
sSql = "SELECT * INTO ['" & sExportToFile & "'].['" & sNewSheetName & "'] FROM MyTable" ''''''& sDataBase

'EXECUTION
rsExcel.Open sSql, sConnect, adOpenDynamic, adLockOptimistic


'Clean Up and free memory
ActiveWorkbook.Names("MyTable").Delete
Set rsExcel = Nothing


End Sub

 

[combo]

The syntax for external database is SELECT ... INTO ... IN ....
Alternatively, it is possible to create worksheet and use excel's CopyFromRecordset method.

combo

 

[bartekR]

thanks combo

I have amended SELECT statement as per below :

sSql = "SELECT * INTO " & sNewSheetName & " IN ''[Excel 8.0;Database=" & sExportToFile & "]" & " FROM MyTable"

It's working now however another problem appeared - I am losing some data e.g. I have a column with Id codes majority of them are numeric, though cells are formatted as text (they get transfered) but some of them are text strings and they get lost during the transfer.

Do you know how to work around this?

 

[xlbo]

ADO uses the "1st 10" principle to determine data types for the recordset

If the 1st 10 rows of data are numeric, that is the data type the recordset will use. Any values not corresponding to the set data type will be dropped

Solution is to have consistent data types in your data set

to ensure that all are numeric, before running the query you need to run something like:

Range("Z1") = 1
Range("Z1").copy
Range(YourIDRange).PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply

this will convert all IDs to numeric (as long as they are all able to be coerced)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question