CODEWITHSUNDEEP
vbams-access-2010ms-access-2007
ThisIsMe
ThisIsMe

Reputation: 11

exporting selected data from access to excel

i have a big access DB with many columns and over 2000 rows. sometimes I need to export data to excel to send letters and etc. but each time its a differet data, like:

most of times i dont need all the columns, like when i send letters i need just name and address and not all the info.

not always I need all data, like just clients from one city, or above buying sum and etc.

is there a way to make a simple form or something else, that will contain the option to select which columns and which data to export?

i tried (using ai engine) and it gave me this code to use on the export button:

Private Sub ExportButton_Click()

    Dim xlApp As Object

    Dim xlBook As Object

    Dim xlSheet As Object

    Dim strSQL As String

    Dim rs As Object

    ' Create a new Excel application

    Set xlApp = CreateObject("Excel.Application")

    xlApp.Visible = True

    ' Create a new workbook and worksheet in Excel

    Set xlBook = xlApp.Workbooks.Add

    Set xlSheet = xlBook.Worksheets(1)

    ' Get the selected column name

    Dim selectedColumn As String

    selectedColumn = Forms("export").Controls("SelectedColumnListBox").Column(0, Forms("export").Controls("SelectedColumnListBox").ListIndex)
    'MsgBox Forms("export").Controls("SelectedColumnListBox").Value

    ' Get the selected specific data

    Dim selectedData As String

    selectedData = Me.SelectedDataListBox.Value

    ' Build the SQL query to retrieve the data

    If selectedData = "" Then

        ' If selectedData is null, return all options

        strSQL = "SELECT " & selectedColumn & " FROM full"

    Else

        ' If selectedData is not null, filter by the selected value

        strSQL = "SELECT " & selectedColumn & " FROM full WHERE " & selectedColumn & " = '" & selectedData & "' OR " & selectedColumn & " IS NULL"

    End If

    ' Execute the query

    Set rs = CurrentDb.OpenRecordset(strSQL)

    ' Check if the recordset is empty

    If rs.EOF Then

        MsgBox "No records found."

    Else

        ' Export the data to Excel

        xlSheet.Range("A1").CopyFromRecordset rs

    End If

    ' Clean up

    rs.Close

    Set rs = Nothing

    Set xlSheet = Nothing

    Set xlBook = Nothing

    Set xlApp = Nothing

End Sub

but it keeps showing me error 94 that the values are null

Upvotes: 0

Views: 90

Answers (1)

Gustav
Gustav

Reputation: 55981

It tells so for a reason. So try using Nz:

selectedData = Nz(Me!SelectedDataListBox.Value)

Upvotes: 0

Related Questions