Exporting Table to Excel Sheets

ayazhoda · April 5th, 2007, 03:18 AM

Hi

I am new comer in MS Access VBA, I am trying to export Access table to Excel any idea please

Thanks

robzyc · April 5th, 2007, 03:33 AM

Hi ayazhoda,

Welcome to the world of Access VBA!

Funny you ask this, I am building code at the moment to do all this for me.

There are two ways of doing this. DoCmd and ADO.

DoCmd is the easiest and basically consists of:

Code:

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"My Table","C:\Test.xls",HasFieldNames:=True

This will transfer the data in table "My Table" to an Excel file called "Test" in the C: drive.
This is both quick and simple, but you have little control over it.

The second option is ADO (which is what I use, and would recommend getting in to once you are more comfortable).
Since you are new to Access VBA, I won't go in to it yet, but have a google and if your interested then I'll be happy to help more.

Regards,
Rob

Vince_421 · April 5th, 2007, 03:37 AM

You have to use the docmd.transferspreadsheet method. something like this:

Private Sub cmdOK_Click()

Dim Excel As AcSpreadSheetType

DoCmd.TransferSpreadsheet acExport, Excel, "Name of the table or query to export", "Location and name of the file"

End Sub

The name of the file you are giving can be a new one (if not it replaces the old one without asking for confirmation if I remember correctly). The name of the location should already been created... Access will NOT make a new folder if the one you specify does not exist.

Hope this is enough information for you...

ayazhoda · April 5th, 2007, 03:40 AM

Thanks Rob for quick reply :)
Just realizing now, Excel sheet has to be in solution not in c:\abc.xls like link table

any further suggestion

Regards

Ayaz

robzyc · April 5th, 2007, 03:48 AM

Ayaz,

Your welcome =)

When you say "in solution" do you mean in the same folder as the application? Do you mean in a new table to be accessed by the application? or both?! =)

Sorry, just want to make sure I understand what you are aiming for, don't want to give you wrong advice.

Regards,
Rob

ayazhoda · April 5th, 2007, 03:54 AM

Hmmm......

This should be accessible by application like
from application form if i click on button Open excelsheet XXX
it opens Excel Sheet

Cheers

ayazhoda · April 5th, 2007, 04:06 AM

Ooooooooooops

How can i select table from Application
As I want to give user option they can select table by themselve not distrub me every 5 mins

regards

Ayaz

robzyc · April 5th, 2007, 04:15 AM

Ok, if you want to open an Excel file created with the Above DoCmd action, you can do so via:

Code:

Shell "Excel.exe ""C:\test.xls""

Obviously, replacing the file name with the filename of your choice.

So on your form, you will have code like.

Code:

Private Sub Command1_Click()
Dim shellCmd as string
shellCmd = "Excel.exe ""C:\Test.xls"""
Shell shellCmd, WindowStyle:=vbNormalFocus
End Sub

Changing the WindowStyle will change how Excel opens, I normally use vbNormalFocus to bring it up to the foreground.

As always, anything else, please ask!

Rob

robzyc · April 5th, 2007, 04:25 AM

Quote:

quote:Originally posted by ayazhoda
Ooooooooooops

How can i select table from Application
As I want to give user option they can select table by themselve not distrub me every 5 mins

regards

Ayaz

Table selection is a totally different matter.

Are the users opening the table for data entry/editing, or just viewing?

It may be worth posting that question in a new thread to make it easier for others to find also.

Rob

ayazhoda · April 5th, 2007, 05:34 AM

Finally .....

Export Access Table to Excel Sheets

Option Compare Database

Private Sub cmdExport_Click()

    If Not IsNull(Me.txtExcelSheetName) And IsNull(txtFolderLocation) Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, cmbTableName.ItemData(cmbTableName.ListIndex), Me.txtFolderLocation, HasFieldNames:=True
        MsgBox "Successfully Exported to Excel Sheet", vbInformation, ExportFinished
        ClearTextField
    Else
        MsgBox "Please enter required information", vbInformation
        ClearTextField
    End If

End Sub

Private Sub cmdFindTable_Click()
On Error GoTo Errorhandler
    Me.txtTableName = OpenFile(Me.txtTableName)
    Exit Sub
Errorhandler:
    MsgBox "Error: " & Err.Description & " (" & Err.number & ")"
End Sub

Private Sub cmdSelectFolder_Click()
On Error GoTo Errorhandler
    Me.txtFolderLocation = SelectDir("", , , "Select Folder to Export")

    If Not IsNull(Me.txtExcelSheetName) Then
        Me.txtFolderLocation = Me.txtFolderLocation & "\" & GetNamePart(Me.txtExcelSheetName)
    End If
    Exit Sub
Errorhandler:
    MsgBox "Error: " & Err.Description & " (" & Err.number & ")"
End Sub

Function GetNamePart(strIn As String) As String
Dim i As Integer
Dim strTmp As String
    For i = Len(strIn) To 1 Step -1
        If MID$(strIn, i, 1) <> "\" Then
            strTmp = MID$(strIn, i, 1) & strTmp
        Else
            Exit For
        End If
    Next i
    GetNamePart = strTmp

End Function

Private Sub Form_Load()

Dim c As New ADODB.Connection
Dim r As New ADODB.Recordset

ClearTextField

Set c = CurrentProject.Connection
Set r = c.OpenSchema(adSchemaTables)
    Do Until r.EOF

        'Cant show all table to user where its not necessary In future if further table need to export just add table name here
        If r!TABLE_NAME = "TableName1" Or r!TABLE_NAME = "TableName1" Or r!TABLE_NAME = "TableName1" Or r!TABLE_NAME = "TableName1" Then
        cmbTableName.AddItem r("TABLE_NAME")
        End If
        r.movenext
    Loop

r.Close
Set r = Nothing
Set c = Nothing

End Sub

Sub ClearTextField()

Me.txtExcelSheetName = ""
Me.txtFolderLocation = ""

End Sub