dcsimg
Results 1 to 7 of 7
  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2016
    Posts
    15

    Question Export acces to excel

    Hi,

    i was trying to create an export from an .mdb file to .xlsx
    my code is working but my result if i do this with vb.net i get an unsorted list.
    when i try to export this with access i get the option to keep the layout.

    is this also possible with vb.net?
    i'm using "Provider=Microsoft.ACE.OLEDB.12.0" method.

    the main reason is i want to get a sorted list is because i would like to update the prices of my data base.
    the data base can't be changed.

    the way i work now to do this is by exporting the access file to excelA.
    i have an excelB that reads the lines of excelA en looks to another excelC (that i exported from discoverer with the prices).
    this gives me an filled in excellB.
    i've created a new .accdb witch is linked to this excellB.
    this way i can copy past the Column from the .mdb from my .accdb.

    if someone has an other idea of making this easier your welcome.
    like filling in the .mdb with a lookup in the excellB would also work and maybe easier.
    but because i had the excel already i tried it first this way.

    Thx

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,850

    Re: Export acces to excel

    When retrieving data from a database, it is usually easy to add sorting (most often by simply adding an Order By clause to an SQL statement, specifying the fields you want to sort by).

    If you show us how your program is getting the data (and let us know what you want to sort by) we can show you how to add sorting to it.

    Quote Originally Posted by wesleyvh View Post
    i'm using "Provider=Microsoft.ACE.OLEDB.12.0" method.
    That gives us far less information that you seem to think... it tells us what technology outside of .Net is being used, but doesn't tell us anything about what is actually in your project (and there are lots of possible technologies there).

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2016
    Posts
    15

    Re: Export acces to excel

    Quote Originally Posted by si_the_geek View Post
    When retrieving data from a database, it is usually easy to add sorting (most often by simply adding an Order By clause to an SQL statement, specifying the fields you want to sort by).

    If you show us how your program is getting the data (and let us know what you want to sort by) we can show you how to add sorting to it.

    That gives us far less information that you seem to think... it tells us what technology outside of .Net is being used, but doesn't tell us anything about what is actually in your project (and there are lots of possible technologies there).
    below you can find the code.
    well I overlooked my excel file and saw a manner to sort that is working.
    i need to sort 3 rows wit each time 1 column has a higher priority (bit obvious )

    could you tell me how to manage a sort to an excel file.
    i think i can reuse the last part of opening and closing the excel. but the sorting will be a little problem and lots of searching.

    still learning vb.net

    Code:
    Imports System
    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Data.OleDb.OleDbConnection
    Imports excel = Microsoft.office.interop.excel
    Imports Microsoft.office
    Public Class Form1
        Dim database As String
        Dim paswoord As String
        Dim exportexcel As String = "K:\Projecten\Engineering\Standaarddocumenten\export tblCost.xlsx"
    
    
    
        Private Sub BTNBROWSE_Click(sender As Object, e As EventArgs) Handles BTNBROWSE.Click
    
            'declareren van save file dialoog
            Dim opendialog1 As New OpenFileDialog()
    
    
            'instellen van het dialoog venster
            With opendialog1
                .Filter = "access database (*.mdb)|*.mdb"
    
            End With
    
    
            'opslaan van het bestand
            If opendialog1.ShowDialog = DialogResult.OK Then
                Database = opendialog1.FileName
                TXTDATABASE.Text = Database
            Else
    
            End If
        End Sub
    
        Private Sub BTNUPDATE_Click(sender As Object, e As EventArgs) Handles BTNUPDATE.Click
    
    
    
            'Dim connectionstring As String
    
            paswoord = TXTPASWORD.Text
    
            'verwijderen van de oudere export
            If My.Computer.FileSystem.FileExists(exportexcel) = True Then
                My.Computer.FileSystem.DeleteFile(exportexcel)
            End If
    
    
            'het openen van de data base
            Dim AccessConn As OleDbConnection
            AccessConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & TXTDATABASE.Text & ";jet OLEDB:Database Password=" & paswoord & ";")
            AccessConn.Open()
    
            'exporteren van TBLitemcost naasr excel bestand
            Dim AccessCommand As New OleDbCommand("SELECT * INTO [Excel 12.0;DATABASE=" & exportexcel & ";HDR=Yes;].[tblCost] FROM [tblCost]", AccessConn)
    
            AccessCommand.ExecuteNonQuery()
    
            'database weer sluiten
            AccessConn.Close()
    
            'weergeven dat alles goed verlopen is
            MessageBox.Show("het exporteren van de artikelen is goed verlopen", "succesvol", MessageBoxButtons.OK)
    
            'excel openen en update saven
            Dim fileTest As String = "K:\Projecten\Engineering\Standaarddocumenten\tblCost.xlsx"
            Dim oExcel As Object
            oExcel = CreateObject("Excel.Application")
            oExcel.Workbooks.Open(fileTest)
            Dim oBook As excel.Workbook
            oBook = oExcel.ActiveWorkbook
    
            ' save
            oExcel.DisplayAlerts = False
            oBook.SaveAs(fileTest, 51) ' 51 == xlsx
            oBook.Close()
            oBook = Nothing
    
    
            'weergeven dat alles goed verlopen is
            MessageBox.Show("het updaten van de prijzen in excel if goed verlopen", "succesvol", MessageBoxButtons.OK)
    
    
    
        End Sub
    
    End Class

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,850

    Re: Export acces to excel

    I see you are using an SQL Select statement:
    Code:
            Dim AccessCommand As New OleDbCommand("SELECT * INTO [Excel 12.0;DATABASE=" & exportexcel & ";HDR=Yes;].[tblCost] FROM [tblCost]", AccessConn)
    ...which you can add an Order By clause to.

    I don't know the names of your fields (or what you want to sort by), but here is an example:
    Code:
    ... FROM [tblCost] ORDER BY field1, field2, field3 Desc", AccessConn)
    This will sort the data by field1 (lowest value first), and if the values are equal then sort by field2, and if those values are also equal then sort by field3 (highest value first).


    I'm not certain this will work with a Select Into, but it is likely to work.

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2016
    Posts
    15

    Re: Export acces to excel

    a Question about the sorting if i try sorting in exel i have to specify he the other 7 columns values also when sorting.
    do i just add the fields in this i think their isn't a value where he could sort afther the first 3 but. is their a way to let him sort by the first 3 and takes the whole row in the sorting?

    update it works now with the sorting didn't need to add the other fields
    Last edited by wesleyvh; Jun 13th, 2018 at 05:44 AM.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,850

    Re: Export acces to excel

    It is up to you whether you specify all of the columns or not, but if you don't specify them all (and the data can't always be fully sorted by the columns you have specified) then the order will be unpredictable for rows that have identical values for the columns you specified (and the order could change if you run it twice).

    The output will always be consistently ordered if you specify all the columns, but it is up to you... if you think just some of the columns are enough then that is all you need to specify.

  7. #7

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


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.