Exporting Data to Excel

[manguilla]

Hello All. I am having a problem figuring out how to export data to excel. I have a form with a drop down box that shows a person's name from an Address table. When they select a name, it will open a form and show their information (name, address, zip, phone) from the same Address Table. Is their a way to export that information from the form to an excel spreadsheet and not the whole table. Thank you very much in advance.

Manguilla

 

[wemeier]

You could build a query that extracts the selected information from the Address table, then use the TransferSpreadsheet or OutputTo method of DoCmd to transfer the results of the query to an Excel spreadsheet. The key is to have the query reference the combobox control on your form for the criteria. For example:

SELECT PersonsName, StreetAddress, City, State, Zip FROM Address WHERE PersonsName = Forms!frmDisplayPerson!cboPersonsName;

Then, create an Event Procedure in the OnClick event of a command button on your form and use the following code to create the Excel spreadsheet:

Private Sub cmdExport_Click()
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcle97, "qryExportPerson", "Person.xls", True
End Sub

or,

Private Sub cmdExport_Click()
    DoCmd.OutputTo acOutputQuery, "qryExportPerson", acFormatXLS, "Person.xls", True
End Sub

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[manguilla]

Thanks for the response. I have tried what you said but I am only getting the column headings to appear in the excel spreadsheet and not the data.

Here is what I did:
I created a new query in the database window in Acceess and called it NameDate. Here is the code

SELECT Name, Address1, Address2, City, State, Phone1, Date
FROM Address
WHERE Name=Forms!NameByDate!cmbName AND StartDate=Forms!NameByDate!cmbDate;



I then put this code under the command button name GO on the form.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "NameDate", "C:\Test\Name.xls", True


It seems to be pretty close. It will only insert the headings on line 1 (Name, Address1, Address2, City, State, Phone1). Do I need to do something else since I am using Office 2003? Thanks again for your time.

Manguilla

 

[JediBMC]

I would attack this as a two part problem. The first thing you should do is ensure that you have the code correctly set up to create the query. Once you think you have it, let it generate the query. Next, open the query to ensure that it has all the data you want to see.

Once you know that this is working, you can tackle the export to Excel.

In the database where I use this, I have the code to generate the query under the onclick event of a button. I then have a seperate function for the export. I then call the function from the onclick event, passing the name of the query.

I am not in a place to have access to my code, but if you need, I will post it when I get back to my desk.

-Brian-
I'm not an actor, but I play one on TV.

 

[manguilla]

Thanks alot for the response. I made sure the query was correct and it is. I then checked the code and the call to the query look correct in the Transferspreadsheet statement. I should do what you said and have the query in the VB code and make a call to the function. If you have time to post the code please do so I know we are on the same page. Thanks again you very much for your response.

Manguilla

 

[JediBMC]

This is the code for the function:

Function FileExport(exportqry As Variant)
Dim dlgPath As FileDialog
Dim filesavepath As Variant
Dim completed As Variant
Dim fullfile As Variant
Dim filenamedate As String
Dim fs As Object

On Error GoTo Error_FileExport
    Set dlgPath = Application.FileDialog(msoFileDialogFolderPicker)
    
    With dlgPath
        .Show
        For Each filesavepath In .SelectedItems
        Next
    End With
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    filenamedate = DatePart("y", Now()) & DatePart("h", Now()) & DatePart("n", Now())
    fullfile = filesavepath & "BBP" & filenamedate & ".xls"
    With fs
        If .FileExists(fullfile) = True Then
            .deletefile fullfile
        Else
        End If
    End With
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, exportqry, fullfile
    completed = "The export has completed.  The file is " & fullfile & "."
    MsgBox completed, vbDefaultButton1, "Export Complete"

    Forms!Menu.SetFocus
    
Exit_FileExport:
    
    DoCmd.Close acForm, "Export"
    Exit Function

Error_FileExport:

    If Err.Number <> 2450 Then
        MsgBox "Error number " & Err.Number & ": " & Err.Description
    Else
    End If
    DoCmd.Close acForm, "Export"
    Exit Function

End Function

-Brian-
I'm not an actor, but I play one on TV.

 

[wemeier]

When you just run the query, does it return a row? Open the form and select the name and date. Then, open the query in Datasheet view. If the query doesn't return anything then make sure that the name and date you selected on the form actually appear on a row of the Address table.

You may also have to convert the contents of the date combobox to a date using the CDate function:

SELECT Name, Address1, Address2, City, State, Phone1, Date
FROM Address
WHERE Name=Forms!NameByDate!cmbName AND StartDate=[b][COLOR=red]CDate([/color][/b]Forms!NameByDate!cmbDate[b][COLOR=red])[/color][/b];

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[manguilla]

When I run the query, it does not return a row with the form open and I doubled checked the information in the table. I also tried the CDate function and that didn't work. I will still use that because I feel it's needed. I must be doing something wrong in the VB Code then. Do I need the cmbName and cmbDate comboboxes with an AfterUpdate() function?

Here is the code I have for my form.

Private Sub Go_Name_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "NameDate", "C:\Test\Name.xls", True

DoCmd.OpenForm "NameDate"
DoCmd.Close acForm, "NameByDate"
End Sub




Thanks again for all the help.

Manguilla

 

[wemeier]

Please post the SQL that resides in your query (I presume the query name is "NameDate" by the code you posted). If you can open the form, then open the query from the Database Window and the query doesn't return any rows, then there's a problem with your query.

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[assets]

As other have said you can use a query, or reports as I do. But for a quick and dirty use office links.

 

[manguilla]

Here is my query and it does return rows. Do I need to include this select statement in my vb code and try referencing it somehow? Thanks again.

SELECT Name, Address1, Address2, City, State, Phone1, Date
FROM Address
WHERE Name=Forms!NameByDate!cmbName AND StartDate=Forms!NameByDate!cmbDate;

 

[wemeier]

If the SQL code that you posted is saved in your query called "NameDate" (the one you're referencing in your TransferSpreadsheet method) and your two combo boxes are named cmbName and cmbDate, then I can't see why your query isn't returning any rows.

Do you get any error messages?

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[manguilla]

I don't get any error messages so there must be something that I am forgetting. I have a pretty good idea what needs to be done so I can try and figure the rest out. Thank you very much for all your help and I will let you know what the answer is when I do some more testing. Thanks.

Manguilla.

 

[JediBMC]

Are you trying to dynamically generate the query or is the query simply a saved query which references values on the form?

The way I use this is that I dynamically create the query, so that the select query actually is writen with values instead of references to fields on forms.

-Brian-
I'm not an actor, but I play one on TV.

 

[manguilla]

The query is saved query under Queries in Access 2003 which references values on the form from 2 combo boxes.

SELECT Name, Address1, Address2, City, State, Phone1, Date
FROM Address
WHERE Name=Forms!NameByDate!cmbName AND StartDate=Forms!NameByDate!cmbDate;


Manguilla