Results 1 to 16 of 16

Thread: How to export query result to xls file with ADODB ?

  1. #1

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    How to export query result to xls file with ADODB ?

    I have this code:

    Code:
    Dim WithEvents dbConn As ADODB.Connection
    Dim sql as string
    sql = "SELECT * INTO [Worksheet1] IN ''[Excel 8.0;Database=" & CurrentProject.Path & "\MyExcel.xls] FROM MyTable"
    
    Set dbConn = New ADODB.Connection
    dbConn.Open "Provider=SQLOLEDB;Data Source=myComputerName\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI;"
    dbConn.Execute sql
    I get error when I run this code:
    Code:
    ADODB conn error: -2147217908, Command text was not set for the command object., Microsoft OLE DB Provider for SQL Server, 1000440, C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\1033\VbLR6.chm
    I am guessing the ADODB cannot run a query like that.

    How can I export query result to xls file by using ADODB connection?
    Is is possible? Or which is a proper way to do it?

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: How to export query result to xls file with ADODB ?

    Command text was not set
    Set the command text before execute ?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    Re: How to export query result to xls file with ADODB ?

    Yeah, in my code sql string was Nothing.
    Now I set it but I get syntax error:

    Code:
    ADODB conn error: -2147217900, Incorrect syntax near the keyword 'IN'., Microsoft OLE DB Provider for SQL Server, 1000440, C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\1033\VbLR6.chm

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to export query result to xls file with ADODB ?

    debug.print sgl
    will show the exact string you are passing to dbconn.execute, make sure it looks correct
    do you still have the ' ' in your sql?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    Re: How to export query result to xls file with ADODB ?

    I though those two quotes are used to redirect the query result to xls file. Nop, it doesn't work without the quotes. It still prints the same error, even without quotes.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to export query result to xls file with ADODB ?

    after many attempts i got it to work without error, but i am not at all sure the data is correct

    but i can not tell you why yours does not work
    on testing, i found that
    you can insert into a new or existing workbook, but the worksheet (table) must not exist

    this is the sql i used
    sql = "SELECT * INTO [sheet7] in '' [Excel 8.0;Database=c:\temp\neww5w.xls] FROM [Sheet1$]"
    as you can see i was just working from one excel database to another, i tested with both jet and ace, the results appeared correct with ace, whereas the results from jet i think i may have screwed up

    you can test if the problem is in the source or target, by opening a recordset select * from mytable
    if that works the problem is in the target
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    Re: How to export query result to xls file with ADODB ?

    Did you use DAO.Database or ADODB.Connection?

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to export query result to xls file with ADODB ?

    ado
    note: i do not have access installed, i only use access databases from vb(a)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    Re: How to export query result to xls file with ADODB ?

    Hmm, not sure. I want a data from my table in my MS SQL Express server to be exported as Excel file with ADODB. Or any other query result run on my SQL Express server.

    I want to use ADODB since it has events, I can monitor what happens and when.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to export query result to xls file with ADODB ?

    i doubt there is much difference with the database type of the query to be saved in excel, the best i could try is an access database query as i do no have sql express, but i do not believe that using another excel workbook as the database would make any difference
    in addition to my previous post, if the target workbook is already open you will get an error
    as i indicated above it took several attempts experimenting before i got it to work without error (work at all)

    i also found why i was getting some strange data exported to the excel workbook, strictly my fault, the data was not coming from the source i thought, but some other workbook, hard to check which workbook the data is coming from with my poor coding

    ado = adodb = activex data object
    Last edited by westconn1; Jun 17th, 2018 at 04:30 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to export query result to xls file with ADODB ?

    also, could your table contain too many fields or records?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: How to export query result to xls file with ADODB ?

    Excel 8.0=Excel 97 --> 256 Columns, 65K Rows max!
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    Re: How to export query result to xls file with ADODB ?

    I made a simple table with only two fields, the first is ID, primary key, the second is ordinary varchar(200).
    The table has like 20 rows. Nothing heavy or special. Just wanted to learn how to export data from a query directly into excel worksheet, so that I can create many of those and automate the process.

    By the way the production database is not MS SQL. It is IBM DB2 and it is still 32 bit server, meaning it doesn't have 64 bit driver for newer systems.

    I will repeat again. DAO.Database does not have a mechanism to monitor when a query executed and VBA wrote the query result into xls file. That is why I want to use ADODB.

    So far all works with DAO.Database, and I have like 50 queries running automatically and by choosing which to execute. I made a form with check-boxes and i choose which queries to run, but the problem is, when I run all 50 queries it takes like 20 minutes to finish which displays white screen or "Not responding" message, which of course looks confusing to the ordinary user.

    Even I will get confused after a while of not using my Access 2000 application.



    I have other duties, so maybe next period I will try ADODB harder. In meanwhile thanks for the info.

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to export query result to xls file with ADODB ?

    I made a form with check-boxes and i choose which queries to run, but the problem is, when I run all 50 queries it takes like 20 minutes to finish
    i assume that you must be using some sort of a loop to run the queries, so you could update some status msg on each iteration, so at least you have something happening for the user
    Code:
    msg = "saving query " & queryname & " number " & num " " out of " & queriescount
    i can make a test later, if you like, using an access database, i would not expect it to be different, but i can not try at all with db2
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    202

    Re: How to export query result to xls file with ADODB ?

    Yes, I would like it to be so simple, but the Access 2000 GUI interface does not get refreshed aka redrawn so displaying a message is useless while executing 50 queries in sequence.

    Hmm. If VBA has some asynchronous capabilities, to start processing at given time, not related to DAO or ADODB?
    Last edited by kutlesh; Jun 20th, 2018 at 02:26 AM.

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to export query result to xls file with ADODB ?

    probably could refresh the application screen at each loop using a call to doevents

    If VBA has some asynchronous capabilities,
    i am not sure if a timer process would do this while the code is running
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width