[RESOLVED] How to use excel spread sheet in data reporting-VBForums
Results 1 to 19 of 19

Thread: [RESOLVED] How to use excel spread sheet in data reporting

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    12

    Resolved [RESOLVED] How to use excel spread sheet in data reporting

    Hi,
    I am new to VB coding. How can i use excel spread sheet as a data table to get output via data report. One more question, VB6 doesn't support MS Access 2007 file, is it correct ?
    An early reply to the question will be appreciated. Thanks in Advance.

  2. #2
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,370

    Re: How to use excel spread sheet in data reporting

    False. VB6 work with ANY database.
    Just have the correct provider/driver depending on the database you use.


    Using ADO with Excel files
    http://www.xtremevbtalk.com/tutors-c...cel-files.html

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    12

    Re: How to use excel spread sheet in data reporting

    Thank you very much gibra for a quick response. The content in the link will take time to read out. Will you please correct me.

    "VB6 doesn't support MS Access 2007 file." Thank you again.

  4. #4
    Frenzied Member
    Join Date
    Dec 2008
    Posts
    1,028

    Re: How to use excel spread sheet in data reporting

    Quote Originally Posted by Urgentbody View Post
    Hi,
    How can i use excel spread sheet as a data table to get output via data report.
    If you are nor clear in your request, you may not get a quick response, or you may get 'incorrect' responses.
    You should clarify exactly what you are doing, and what output you are looking for.
    EG you might be -
    - using ADO to get data from an Access DB
    - then you may be looking to automate Excel
    - and writing/sending that data into the Excel sheet
    - and then telling Excel to send it to the printer
    - and you may wish all that to happen in a hidden way so that the user does not even know you are using excel to create his printout.

    And none of the above uses the Data Report in any way.

    My assumption as to what you want may be off the track, but at least it could encourage you to provide more detail about what you actually are seeking.

    Rob

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    22,805

    Re: How to use excel spread sheet in data reporting

    "VB6 doesn't support MS Access 2007 file." Thank you again.
    why repeat the statement, when you were already told it is incorrect, just requires different connection string and if you are still on XP you may need to download the appropriate access database engine (free) from microsoft, but any later version of windows will possibly already have it

    you do not need to have access installed to use an access data base in vb6
    there is no reason you can not use an earlier or later version data base in vb6, regardless of what access version is installed
    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

  6. #6
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,370

    Re: How to use excel spread sheet in data reporting

    Quote Originally Posted by Urgentbody View Post
    Thank you very much gibra for a quick response. The content in the link will take time to read out. Will you please correct me.

    "VB6 doesn't support MS Access 2007 file." Thank you again.
    ConnectionStrings.com
    https://www.connectionstrings.com/

    Access
    https://www.connectionstrings.com/access/

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,322

    Re: How to use excel spread sheet in data reporting

    MS Access 2007 can produce several different file formats.

    It can produce the standard Jet 4.0 "MDB" format that is supported by Windows with no additional software. It can also support the ACE 12.0 "ACCDB" format using its private, extended version of Jet called ACE. ACE 12.0 is not part of Windows so you'd have to either install MS Access 2007 or later or else install a bundle of components that comprise ACE.

    Microsoft Access Database Engine 2010 Redistributable is still available.

    Read the notes there though. ACE formats are not really intended for general use. The Jet format is preferred.


    Similar issues apply to MS Excel file formats.

    Are you trying to use an Excel Workbook as a data source?


    In any case these questions have been covered many times. I assume you are looking for copy/paste code since you haven't put any effort into self-education. If so then try a few searches, sample code has been posted many times for both XLS and XLSX format Workbooks.

    Here's one: [RESOLVED] Calling Microsoft Ace Database Engine V12 or V14 to open an Excel XLSX or XLS file

  8. #8

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    12

    Re: How to use excel spread sheet in data reporting

    Hi,
    I am having data in excel with simple arithmetic calculations i.e. Cell (x,y) = Cell (x1,y1) * Cell x2,y2) etc etc.
    In form1 I am giving input to some cells and accordingly I am getting calculated values in excel file. That file then saved.

    Private Sub ok_Click()

    Dim excelApp As Excel.Application
    Dim excelWB As Excel.Workbook
    Dim excelWS As Excel.Worksheet
    Set excelApp = CreateObject("Excel.Application")
    excelApp.DisplayAlerts = False

    Set excelWB = excelApp.Workbooks.Open("F:\VB\Analysis Of Rates\Analysis.xlsx", Password:="1")
    Set excelWS = excelWB.Worksheets(1)
    With excelWS
    .Cells(3, 7).Value = Val(Sand.Text)
    End With

    excelWB.Save
    excelApp.DisplayAlerts = True
    excelWB.Close
    Excel.Application.Quit
    Set excelApp = Nothing

    End Sub

    Now I want the excel data to be printed. To get printed in a specific manner. For that i want data report for it. I cant connect the excel file to data environment. As i need data environment for data report.

  9. #9
    Fanatic Member Spooman's Avatar
    Join Date
    Mar 2017
    Posts
    620

    Re: How to use excel spread sheet in data reporting

    Urgent

    Well, you closed your Excel app there
    Maybe if you re-open it in a similar fashion to the way you originally opened it.

    Spoo

  10. #10
    Hyperactive Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    495

    Re: How to use excel spread sheet in data reporting

    Hi,

    this is not true..
    Now I want the excel data to be printed. To get printed in a specific manner. For that i want data report for it. I cant connect the excel file to data environment. As i need data environment for data report.
    here a sample to open an Excelsheet and select the Data you want with Sql.
    Just put the Sql-Statement together and pass it to the Datareport.
    Code:
    '1x Datagrid
    '1x Commandbutton
    'you need to set to ADO 2.x Library
    Option Explicit
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Private Sub Command1_Click()
      On Error GoTo ErrHandler
        Set rs = New ADODB.Recordset
        '--- open recordset
        'Sheet1 is a Tablenname in Excel
        'this will select only a Artikelname with the letter C
        rs.Open "SELECT Artikelname, Einzelpreis FROM [Sheet1$] Where Artikelname Like 'c%' ", cn, adOpenDynamic, adLockOptimistic
        Set DataGrid1.DataSource = rs
    ErrHandler:
        Err.Clear
    End Sub
    
    Private Sub Command2_Click()
        Set rs = New ADODB.Recordset
        '--- this time select a range of cells from the XLS file
        'rs.Open "SELECT * FROM [Sheet1$A1:B2] ", cn, adOpenDynamic, adLockOptimistic
            rs.Open "SELECT * FROM [Sheet1$] ", cn, adOpenDynamic, adLockOptimistic
        Set DataGrid1.DataSource = rs
    End Sub
    
    Private Sub Form_Load()
    On Error GoTo ErrHandler
        Set cn = New ADODB.Connection
        ' -- connection provider
        cn.Provider = "Microsoft.Jet.OLEDB.4.0"
        cn.ConnectionString = _
            "Data Source=C:\TestExcel.xls;" & _
            "Extended Properties=Excel 8.0;"
        cn.CursorLocation = adUseClient
        cn.Open
    Exit Sub
    ErrHandler:
        MsgBox "Connection-Fehler"
    End Sub
    
    
    Private Sub Command3_Click()
          Set rs = Nothing
        cn.Close
        Set cn = Nothing
        End
    End Sub
    regards
    Chris
    Last edited by ChrisE; Sep 14th, 2017 at 06:23 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,322

    Re: How to use excel spread sheet in data reporting

    Not having any problem here:

    Name:  sshot.png
Views: 54
Size:  2.9 KB

    No need for a lot of caveman procedural code, data binding does it all.
    Attached Files Attached Files

  12. #12
    Hyperactive Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    495

    Re: How to use excel spread sheet in data reporting

    Quote Originally Posted by dilettante View Post
    Not having any problem here:

    Name:  sshot.png
Views: 54
Size:  2.9 KB

    No need for a lot of caveman procedural code, data binding does it all.
    Hi Urgent,

    what more could you want, dilttante gave you all you need

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    12

    Re: How to use excel spread sheet in data reporting

    Thanks to The forum. And Thanks to dilettante for the valuable information. I am looking ahead for answers for my tons of silly questions. If not protested and advised to go and refer books. Haha.....

    only One thing left, that can i use the Excel file having Password protected in the above.

  14. #14
    Frenzied Member
    Join Date
    Jun 2015
    Posts
    1,454

    Re: How to use excel spread sheet in data reporting


  15. #15

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    12

    Re: How to use excel spread sheet in data reporting

    Quote Originally Posted by Urgentbody View Post
    Thanks to The forum. And Thanks to dilettante for the valuable information. I am looking ahead for answers for my tons of silly questions. If not protested and advised to go and refer books. Haha.....

    only One thing left, that can i use the Excel file having Password protected in the above.

    I want to know how to use a password protected spread sheet for "Data Report". Tried a little error message during linking file to data environment. So is it possible to link a PW protected file to Data Report.

  16. #16
    Frenzied Member
    Join Date
    Jun 2015
    Posts
    1,454

    Re: How to use excel spread sheet in data reporting

    "Could not decrypt file." ?

  17. #17

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    12

    Re: How to use excel spread sheet in data reporting

    Quote Originally Posted by DEXWERX View Post
    "Could not decrypt file." ?
    Hi,
    I could not get it clear by

    "Could not get decrypt file"?

    Are you saying that files can't be pass word protected.

  18. #18
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,322

    Re: How to use excel spread sheet in data reporting

    Both Jet 4.0 and the "private Jet" called ACE process Excel Workbook data via Installable Indexed Squential Access Methods (IISAMs). These are extra plug-in DLLs the database engine loads to handle things like Excel, Text, HTML, etc.

    The Excel IISAMs do not support encrypted Workbooks (i.e. there is no way to use a password). The "password" required is not the connection-level password.

    If the Workbook is open in Excel and then you open a connection it will succeed without any password supplied, because Excel has decrypted the data already.

  19. #19

    Thread Starter
    New Member
    Join Date
    Sep 2017
    Posts
    12

    Re: How to use excel spread sheet in data reporting

    Thank you.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.