SQL export to workbook 3

[cardiac]

How do I export data from a sheet in an open workbook to another sheet in a closed wokbook using SQL?

 

[SkipVought]

You can use ADODB to Update a sheet.

But why not just PULL the data when that workbook is open using MS Query? faq68-5829

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cardiac]

I probably did not explain very well....

I have code which requests the user to choose several files to import data from (SQL connection).
The imported data is then manipulated.
(The code for this works fine).

I then want to be able to select an Excel workbook to export the manipulated data to, again using an SQL statement if possible?
(This is where I am stuck).

 

[SkipVought]

Nothing you stated, has changed my opinion.

Perhaps you ought to explain what is already in the SHEET in the WORKBOOK that you want to export to.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cardiac]

There is nothing in the unopened workbook that I want to export to - I just need to export the modified data to a new workbook

 

[SkipVought]

How about a simple copy Sheet Tab to New Workbook. Use your macro recorder. Post back with your recorded code if you need help customizing.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cardiac]

I actually WANT to use SQL - I know how to copy a sheet to a workbook.
I dont actually want to open a new workbook hence the SQL.
Ther are actually other reasons which I dont need to go into here.

 

[cardiac]

This is my code - which does not work!
Set rsExcel = New ADODB.Recordset

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\test.xls ";Extended Properties=Excel 8.0;"

sSql = "SELECT * INTO [Test] IN C:\test.xls FROM [Sheet1$]"

rsExcel.Open sSql, sConnect, adOpenDynamic, adLockOptimistic

 

[SkipVought]

Don't get it. You want a NEW workbook, not an EXISTING workbook, which is what I assume that test.xls is supposed to be.

What am I missing?

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[SkipVought]

Try this...

sSql = "SELECT * INTO [Test$] IN [C:\test.xls]  FROM [Sheet1$]"

assuming that Test is a sheet in c:\test.xls.

Must use $ for sheets.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[cardiac]

Thanks - but still no joy.
Yes, I do want to create a new workbook with the sheet 'Test' in it.

The Microsoft site says you must not use a '$' sign for sheets reference by the INTO clause and that the sheet to write to must NOT already exist in the workbook.

so I tried: -
sSql = "SELECT * INTO [Test] IN [C:\test.xls] FROM [Sheet1$]"

but this gives me the error invalid argument in the 'rsExcel.Open sSql, sConnect,adOpenDynamic, adLockOptimistic' statement

I'll keep chipping away at it!

 

[ClulessChris]

cardiac,

Have you gone as far as creating the new workbook?
If so have you managed to rename a sheet to Test?

note the $ in Skips post, this may be causing you some issues if itis missing from your code.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[cardiac]

It should create both the new workbook automatically - but it does neither.

The $ is definately not required - it does not work with or without at the moment.

I have created the empty workbook manually and just atempted to export to it - but this does not work either.

 

[ClulessChris]

cardiac,

I bow to your superior knowledge.

Good Luck

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[PHV]

Why not simply something like this ?

Worksheets("Sheet1").Copy
ActiveSheet.Name = "Test"
ActiveWorkbook.SaveAs "C:\test.xls"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

The connection should be to your SOURCE WORBOOK, not the workbook you are saving into...

Sub test()
    Dim rsExcel As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    Set rsExcel = New ADODB.Recordset
    
    sConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
    sConn = sConn & "Data Source=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "Extended Properties=Excel 8.0;"
    
    Set cnn = New ADODB.Connection
        
    cnn.Open sConn
    
    sSQL = "SELECT * INTO [Test] IN [C:\test.xls] FROM [Sheet1$]"
    
    rsExcel.Open sSQL, cnn, adOpenDynamic, adLockOptimistic

    rsExcel.Close
    cnn.Close
    Set rsExcel = Nothing
    Set cnn = Nothing
End Sub

This syntax works for SELECT queries, however, I cannot get it to work for the INTO clause.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[PHV]

I cannot get it to work for the INTO clause
MS no longer supports the updating of excel files via SQL.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cardiac]

Thanks to everyone for trying.

 

[ClulessChris]

MS no longer supports the updating of excel files via SQL.

Many thanks I did not know that. a star for you.

Never knock on Death's door: ring the bell and run away! Death really hates that!