How do you export to Excel? 1

[devRyan]

Hi All,

I was wondering if someone could tell me how to export data to excel. I've looked in the FAQ and can't seem to find anything there on the topic.

All I need to do is a basic data dump, from a form. I've got a search form in my app that users can use to search through the records of the db. Once they find the recordset that they want, I want them to be able to click an Export button and dump the data to and excel spreadsheet.

On the search form, there are multiple fields for filtering the query, and once the query is complete, the recordset is displayed in a Listbox, that can be used to view the records before exporting. This all works well. I just don't know how to get my data to export to an excel file.

Thanks.

devRyan

 

[jamez05]

Here's the code I use to export to a CSV file. It allows the user to pick a location to save the file to. The reason I have the export to a csv versus excel file because fields over 255 characters (such as memo fields) get truncated when exporting to excel. However, if you want Excel, this code is fairly easy to change (should be self explanatory:

Private Sub btnCSV_Click()
    
    Dim dlgSaveAs As Office.FileDialog
    Dim strFile As String
    Dim strExtension As String
    Dim ErrMsg As String
    
    ErrMsg = "CSV Export Cancelled"
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
    
    With dlgSaveAs
        If .Show = True Then
           strFile = dlgSaveAs.SelectedItems(1)
           'Make sure we are saving to an .csv file
           If CharCount(strFile, ".") = 0 Then
                strFile = strFile & ".csv"
           End If
           If Right(strFile, 4) <> ".csv" Then
                ErrMsg = ErrMsg & vbNewLine & "The file must have an extension of '.csv'"
                GoTo OnError
           End If
           'Passed error checking, ready to create the file
           DoCmd.TransferText acExportDelim, , "qryDelimited", strFile, True
           GoTo OnExit
           
        Else
            'The User cancelled
            GoTo OnError
        End If
    End With

OnExit:
    MsgBox "The .CSV File has been saved to:" & vbNewLine & strFile
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub
OnError:

    MsgBox ErrMsg
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Exit Sub
    
    
End Sub

 

[jamez05]

Whoops, have a global function called charcount that this function uses as well. You can put it above this function in your code if you want:

Public Function CharCount(OrigString As String, _
  Chars As String, Optional CaseSensitive As Boolean = False) _
  As Long

'**********************************************
'PURPOSE: Returns Number of occurrences of a character or
'or a character sequencence within a string - Added by jfn bsaf 1.0

'PARAMETERS:
    'OrigString: String to Search in
    'Chars: Character(s) to search for
    'CaseSensitive (Optional): Do a case sensitive search
    'Defaults to false

'RETURNS:
    'Number of Occurrences of Chars in OrigString

'EXAMPLES:
'Debug.Print CharCount("FreeVBCode.com", "E") -- returns 3
'Debug.Print CharCount("FreeVBCode.com", "E", True) -- returns 0
'Debug.Print CharCount("FreeVBCode.com", "co") -- returns 2
''**********************************************

                'Open "C:\Documents and Settings\jniesen\Desktop\bsaf\BSAF_New.txt" For Append As #1
                'Write #1, strSQL
               ' Close #1 ' Close file.

Dim lLen As Long
Dim lCharLen As Long
Dim lAns As Long
Dim sInput As String
Dim sChar As String
Dim lCtr As Long
Dim lEndOfLoop As Long
Dim bytCompareType As Byte

sInput = OrigString
If sInput = "" Then Exit Function
lLen = Len(sInput)
lCharLen = Len(Chars)
lEndOfLoop = (lLen - lCharLen) + 1
bytCompareType = IIf(CaseSensitive, vbBinaryCompare, _
   vbTextCompare)

    For lCtr = 1 To lEndOfLoop
        sChar = Mid(sInput, lCtr, lCharLen)
        If StrComp(sChar, Chars, bytCompareType) = 0 Then _
            lAns = lAns + 1
    Next

CharCount = lAns

End Function

 

[devRyan]

Wonderful, Thanks. I'll let you know how it goes.

 

[devRyan]

Just curious. In looking at your usage of the CharCount function, why wouldn't you simply use inStr? Since you're not actually looking for a certain number of instances > 0.

 

[jamez05]

Good idea. Still kind of new to vba. Like the input though and will look into it.

 

[devRyan]

Also, what version of Access are you using? My compiler is unaware of the Office object used to create the dialog.

 

[devRyan]

Nevermind, I found this, which does the job for me.

Dim dlgSaveAs As FileDialog
    
    Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
    
    dlgSaveAs.Show

 

[devRyan]

lol, I just looked at that closer and realized is was the same thing. duh!

 

[devRyan]

Is there anyway to do this with a query string and not an actual access stored query? Or anyway to dynamically update a stored query's contents?

 

[devRyan]

Seems that DoCmd.TransferText won't accept a raw query string. Looks like I'll need to write to the file manually.

 

[jamez05]

Yes, you I think you can do it inline like:

dim strSQL AS string
strSQL = "SELECT fields FROM table"
DoCmd.TransferText acExportDelim, , strSQL, strFile, True

 

[devRyan]

Yeah, I tried that with no luck. Also, I read a few articles saying that it wasn't supported.

 

[jamez05]

One thing that's kind of neat. I have just one query named "qrydelimited" that I created (doesn't matter what the fields in it are). There are a bunch of different reports that are based on different queries that all need an option to be exported. You can dynamically change this query using in-line sql so you don't have to worry about writing other queries:

                ' If window is open, close it
                DoCmd.Close acQuery, "qryDelimited", acSaveNo
        
                'load query
                Set qry = db.QueryDefs("qryDelimited")
                qry.SQL = strSQL
                qry.Close