Results 1 to 17 of 17

Thread: Looking for Microsoft's ExcelADO Sample Files

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Question Looking for Microsoft's ExcelADO Sample Files

    I ran across the article below from Mocrosoft that indicates there is a sample project. I must be getting old, becasue I cannot see any link to download the sample project. Do any of you see the link, or know where I can download this/these mysterious "sample" files/project?

    https://support.microsoft.com/da-dk/...4-4aadf1ff1afa

  2. #2
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,657

    Re: Looking for Microsoft's ExcelADO Sample Files

    This should be the file you are looking for. Added code tags to prevent it from being a hyperlink so that it can't be accidentally executed.

    Code:
    http://download.microsoft.com/download/excel2000/sample/12_27_2000/w98nt42kme/en-us/excelado.exe

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: Looking for Microsoft's ExcelADO Sample Files

    This was just what I was looking for. I have some questions about adding data to a specific sheet in a multi-sheet (tab) workbook. I see that the examples place the data to a default single sheet on the target workbook. Should I start a new thread?

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

    Re: Looking for Microsoft's ExcelADO Sample Files

    searching in this and office development forums should produce many examples

    workbooks are the database and worksheets are the tables
    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
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    8,018

    Re: Looking for Microsoft's ExcelADO Sample Files

    this should give you the idea....

    Code:
    Dim ows1 As excel.Worksheet, ows2 As excel.Worksheet, ows3 As excel.Worksheet
    'Get the Excel application object.
    Set oExcel = New excel.Application
    
    Dim oRng1 As excel.Range
    Dim oRng2 As excel.Range
    Set oWB = oExcel.Workbooks.Add
    
    Set ows1 = oWB.Worksheets("Sheet1")
    ows1.Name = "Sheet1Name"
    
    Set ows2 = oWB.Worksheets.Add
    Set ows2 = oWB.Worksheets("Sheet2")
    ows2.Name = "Sheet2Name"
    
    Set ows3 = oWB.Worksheets.Add
    Set ows3 = oWB.Worksheets("Sheet3")
    ows3.Name = "Sheet3Name"
    
    .....
    
    With ows1
      'do whatever you want to add stuff to Sheet1Name
    end with
    
    With ows2
      'do whatever you want to add stuff to Sheet2Name
    end with
    
    With ows3
      'do whatever you want to add stuff to Sheet3Name
    end with
    Last edited by SamOscarBrown; Feb 4th, 2021 at 04:07 PM.
    Sam I am (as well as Confused at times).

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,825

    Re: Looking for Microsoft's ExcelADO Sample Files

    Actually named and unnamed ranges can also be treated as "tables."

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: Looking for Microsoft's ExcelADO Sample Files

    I will keep the above on mind and want to rewind back to that example project OptionBase1 turned me on to. I am stumbling with some of the things there. [if I should post this as a new thread, let me know and I will do so, as the initial post was about where to find the example project.]

    Here is what OptionBase1 turned me on to:

    Code:
    http://download.microsoft.com/download/excel2000/sample/12_27_2000/w98nt42kme/en-us/excelado.exe
    I downloaded this project, and it has great basic examples of getting things out of the northwinds database, and into an Excel workbook . I am struggling perhaps with the naming conventions in this example project, when dealing with the workbook sheet names. The struggle comes when I try to work with my demo database that has one table called USERS, and my xls that has one Sheet called "USERS". I get an error that says " The Mocrosoft Jet Engine could not find the object 'USERS_Table'".

    In their example, they have this:

    Code:
    Private Sub cmdSample1_Click()
        
        'Make a copy of the workbook template
        FileCopy sOrdersTemplate, App.Path & "\Results\Orders1.xls"
        
        'Open the ADO connection to the Excel workbook
        Set oConn = New ADODB.Connection
        oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & App.Path & "\Results\Orders1.xls;" & _
                   "Extended Properties=""Excel 8.0;HDR=NO;"""
    
        'Open a connection to the Northwind database and retrieve the information
        'in OrderDetails table
        Dim oNWindConn As New ADODB.Connection, oOrdersRS As New ADODB.Recordset
        oNWindConn.Open "provider=microsoft.jet.oledb.4.0; data source=" & sNwind
                        
        oOrdersRS.Open "SELECT [Order Details].OrderID, Products.ProductName, " & _
                       "[Order Details].UnitPrice , [Order Details].Quantity, " & _
                       "[Order Details].Discount FROM Products INNER JOIN " & _
                       "[Order Details] ON Products.ProductID = " & _
                       "[Order Details].ProductID ORDER BY [Order Details].OrderID", _
                       oNWindConn, adOpenStatic
                       
        '**Note: The first "row" in the Orders_Table is hidden -- it contains dummy data that
        '        the OLE DB Provider uses to determine the data types for the table.
        
        'Add the data from the Order Details table in Northwind to the workbook
        Dim oRS As New ADODB.Recordset
        oRS.Open "Select * from Orders_Table, oConn, adOpenKeyset, adLockOptimistic
        
        Do While Not (oOrdersRS.EOF)
            oRS.AddNew
            For i = 0 To 4
                oRS.Fields(i).Value = oOrdersRS.Fields(i).Value
            Next
            oRS.Update
            oOrdersRS.MoveNext
        Loop
        
        'Close the recordset and connection to Northwind
        oOrdersRS.Close
        Set oOrdersRS = Nothing
        oNWindConn.Close
        Set oNWindConn = Nothing
        
        'Close the connection to the workbook
        oConn.Close
        Set oConn = Nothing
        
        'Open the workbook to examine the results
        DoEvents
        ShellExecute Me.hwnd, "Open", App.Path & "\Results\Orders1.xls", "", "C:\", SW_SHOWNORMAL

    That highlighted item "Orders_Table" is what I am stumbling over. I look at their example xls in Excel and I see no sheet that is named "Orders_Table". I see that the one and only sheet in the file is named "Orders". Is that "_Table" a convention that means the sheet named "Orders"? They are referring to something in the xls right? Confused.

    I am able to connect to my database and select my Users and used the same structure for connecting to the Excel file.

    Code:
    'Open the ADO connection to the Excel workbook
        Set oConn = New ADODB.Connection
        oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & App.Path & "\ExportedUserData.xls;" & _
                   "Extended Properties=""Excel 8.0;HDR=Yes;"""
    
        'Open a connection to the Northwind database and retrieve the information
        'in OrderDetails table
        
        
       Call OpenXgelConnection
        
       Dim rs_Users As ADODB.Recordset
       Set rs_Users = New ADODB.Recordset
        
    
       
       Dim strQuery As String
       strQuery = "SELECT * " & _
                        "FROM " & _
                            "USERS"
                        
          
         rs_Users.Open strQuery, conn_xgel, adOpenStatic, adLockOptimistic
       
      
                       
        '**Note: The first "row" in the Orders_Table is hidden -- it contains dummy data that
        '        the OLE DB Provider uses to determine the data types for the table.
        
        'Add the data from the Order Details table in Northwind to the workbook
        Dim oRS As New ADODB.Recordset
        
        
        Debug.Print oConn
        oRS.Open "Select * from USERS_Table", oConn, adOpenKeyset, adLockOptimistic
    
        Do While Not (oOrdersRS.EOF)
            oRS.AddNew
            For i = 0 To 4
                oRS.Fields(i).Value = oOrdersRS.Fields(i).Value
            Next
            oRS.Update
            oOrdersRS.MoveNext
        Loop
        
        'Close the recordset and connection to Northwind
        oOrdersRS.Close
        Set oOrdersRS = Nothing
        oNWindConn.Close
        Set oNWindConn = Nothing
        
        'Close the connection to the workbook
        oConn.Close
        Set oConn = Nothing
    and it breaks at the above highlighted line

    Code:
        oRS.Open "Select * from USERS_Table", oConn, adOpenKeyset, adLockOptimistic
    with the message "The Mocrosoft Jet Engine could not find the object 'USERS_Table'. Make sure the object exists ...."

    I changed the HDR to NO and that seemed to make no difference. I have also changed USERS_Table to USERS and still no joy.

    Still confused. I thought I just "did what they did" LOL, but obviously not. Doing something wrong fo sure.

    .. (It will break below too, as I have not yet modified any of that subsequent code ..)
    Last edited by clickman; Feb 4th, 2021 at 05:21 PM.

  8. #8
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    3,281

    Re: Looking for Microsoft's ExcelADO Sample Files

    You might want to search for a native BIFF12 writer (the format Excel uses) if more worksheets, more styles (bold headers), format borders or images are needed to be exported (not only tabular data).

    cheers,
    </wqw>

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: Looking for Microsoft's ExcelADO Sample Files

    Quote Originally Posted by wqweto View Post
    You might want to search for a native BIFF12 writer (the format Excel uses) if more worksheets, more styles (bold headers), format borders or images are needed to be exported (not only tabular data).

    cheers,
    </wqw>
    Thanks for that info.. Right now I'm just needing the data itself, BUT, once I can get that worked out, then of course Ill want more!

  10. #10
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    8,018

    Re: Looking for Microsoft's ExcelADO Sample Files

    I guess I'll have to download the same to assist.
    But, right off, I noticed that you have oConn where 'their' connection variable is called "conn_xgel". Stay consistent.
    EDIT: (or vice-versa...hard to follow what is YOUR code, and was original._
    Last edited by SamOscarBrown; Feb 4th, 2021 at 06:03 PM.
    Sam I am (as well as Confused at times).

  11. #11
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    8,018

    Re: Looking for Microsoft's ExcelADO Sample Files

    1- you did not SET ORS after dimming it.
    2-How about zipping and uploading YOUR project as it now stands with YOUR database. And explain what you want on separate tabs (workSHEETs) in your excel file(s)
    Sam I am (as well as Confused at times).

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: Looking for Microsoft's ExcelADO Sample Files

    Quote Originally Posted by SamOscarBrown View Post
    I guess I'll have to download the same to assist.
    But, right off, I noticed that you have oConn where 'their' connection variable is called "conn_xgel". Stay consistent.

    Please do download. it is a pretty nice example project and can assist us here.

    On the consistency - oConn IS different that conn_xgel, but still, maybe I've got thing mixed up trying to understand their code. My conn_xgel is the connection to my source database, and has that different name because usually when I am working off an example, I leave the example names intact and comment them (and the code blocks that contains them) out, and replace with my names in cloned blocks, so that I can compare their structure to my structure as a check, and not get confused as to what is mine and what is their original when things break

    Sometimes I do this, and then have to revert back to their original blocks, especially in cases like this where there is some error I can't seem to overcome. That was the case with this oConn. The oConn that I used was the same as in their example code where I thought the code was making the connection to the target Excel file.

    Could it be that I am supposed to use the conn_xgel at that place where the example code says oConn? I thought that statement where that oConn occurred, was supposed to refer to the target excel file connection. Yikes, maybe not??

    Anyway, after you kick the tires in the example, please advise and we can go from there.

    Thanks for taking a look at this.

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: Looking for Microsoft's ExcelADO Sample Files

    Quote Originally Posted by SamOscarBrown View Post
    1- you did not SET ORS after dimming it.
    2-How about zipping and uploading YOUR project as it now stands with YOUR database. And explain what you want on separate tabs (workSHEETs) in your excel file(s)
    is there something special I have to do to upload a zip. I go to the manage attachments and select the zip and select upload and nothing happens. ??? zip size i 232 kb Ahhh OK Use MS Edge and not Google.
    Attached Files Attached Files
    Last edited by clickman; Feb 4th, 2021 at 07:30 PM.

  14. #14
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    8,018

    Re: Looking for Microsoft's ExcelADO Sample Files

    WHOA! I am sorry, I can NOT help you. I thought you simply wanted to take data from an Access Table and create an Excel Workbook with more than one WeekSheet. THAT, I can do. But I never use Excel files as a 'db' (although it is common practice by many...I just never went that route).

    I'm going to step out of this Thread as my head ached after attempting to follow the code in that (and your) project. Folks that deal with stuff can definitely help you...I just never do this stuff.

    Sorry....

    Sam
    Sam I am (as well as Confused at times).

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: Looking for Microsoft's ExcelADO Sample Files

    I'm going to step out of this Thread as my head ached after attempting to follow the code in that (and your) project. Folks that deal with stuff can definitely help you...I just never do this stuff.

    Sorry....
    I feel you my friend .. actually you made me feel better .. I am nowhere as skilled as the sages like you on the board and I was feeling really sheepish about it all.

    I simply tried to "copy" the process and logic of the example, into my new command button, so as to preserve the logic ( and the code itself so I could see it) of the initial project itself. I first tried modeling my Sub after theirs, with the oConn etc, but it blew a gasket at that same place.


    I looked at my excel file, and at theirs, and could not see any difference and was stumped. I then tried the "Insert Into" route ,as one of the larger projects I have uses this very way to take data out of a access database, and write the access database data to corresponding tables in an excel file that had been created out of an excel template. This DB and the Excel file having a correspondence betweeen the DB table name and the Sheet Name; and a correspondence between the db field names ( of the db table) and the column names of the corresponding Excel tab/sheet.

    That code was entangled with a larger scope, and instead of unentangling it, I thought I would try that example project you sent the link to that I saw mentioned in this Microsoft article.

    https://support.microsoft.com/en-us/...rs=en-us&ad=us


    But Wait , in that article I see the following, and the plot thickens:

    Click Sample 1. This sample creates a copy of OrdersTemplate.xls. It then uses ADO to connect to the workbook and opens a Recordset on a table that is a defined range in the workbook. The name of the range is Orders_Table. It uses ADO AddNew/Update methods to add records (or rows) to the defined range in the workbook. When the row additions are complete, the ADO Connection is closed and the workbook is displayed in Microsoft Excel. Follow these steps to do this: ....


    Perhaps Optionbase1 can shed light on things?
    Last edited by clickman; Feb 4th, 2021 at 10:57 PM.

  16. #16
    Frenzied Member
    Join Date
    Dec 2008
    Location
    Melbourne Australia
    Posts
    1,468

    Re: Looking for Microsoft's ExcelADO Sample Files

    I would not use Excel as a database even if you offered me large sums of money.
    Rob
    PS
    I was a Chartered Accountant (and Auditor), and then 20 years as a Senior Analyst in IT in our biggest Banks, and Telecommunications companies.
    There are lots of people/companies using Excel for all sorts of things, that they should not be doing.

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,447

    Re: Looking for Microsoft's ExcelADO Sample Files

    if you are using any ADO on workbook that is later than .xls, you should check your connection string is up to date (connectionstrings.com), without checking, i would assume the sample file is a bit old now and does not take into account later versions of excel and windows

    the table name for a worksheet is the worksheet name followed by a $
    "select * from sheet1"
    if the worksheet name contains spaces, the table name should be enclosed in []
    "select * from [some sheet$] where f1=bill" ' bill in column A

    if the first row of data contains headers these will be treated as field names, the connection string should contain headers=yes as part of the extended properties
    if there are no headers the field names are f1, f2, f ......... to the number of columns and headers=no

    using excel as a data base sometimes works really well using sql statements, but can have serious limitations for columns with mixed data types, there can be ways to work around this, but not so easy
    i have, over time, posted several examples in office development forum

    if you have any specific questions, it may be better to start a new thread
    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

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