Export Access table to excel

[eyobs1]

Hi everyone,

I apperciate if you can help me please. I have an access table called "retrieval" and have many fields such as "date_notified" [date], "donor_hospital" [text] etc. I'd like to export all these data to excel skipping the field names [without field names]. I know how to export using macros but i would like to do it using Visual Basic. I'd like to give the user flexibility the fields to export to excel.

Thank you for your help.

 

[JamesMcS]

look up transferspreadsheet in the help file - it's pretty easy and quite powerful. In terms of allowing the user to select what goes into the spreadsheet, I'd recommend a form with check boxes that builds a SELECT statement. Then, just use transferspreadsheet and job's a goodun.

 

[eyobs1]

Hi james,
I have used the transferspreadsheet module as follows:

Private Sub Command6_Click()
On Error GoTo Command6_Click_Err
DoCmd.TransferSpreadsheet acExport, 8, "retrieval", False, ""

Command6_Click_Exit:
Exit Sub
Command6_Click_Err:
MsgBox Error$
Resume Command6_Click_Exit
End Sub


The two things I'd like to achieve are 1) the above code still prints the field name on the exported excel file which i don't want
2) I want a combo box with list of months from the drop down list from a cross tabed query called "qryRetrieval" and would like to see the report based on this query.

Do you think it's possible?

Thank you for your help

 

[JamesMcS]

OK - I don't think you can avoid having the field names. You could probably do some more in VBA to select the field names, then delete and shift cells up... but you'll have to do some reading on that

You definitely can use a combo box. You'd need to put forms![formname].form![comboname].column(x) in the criteria of the query, and ensure the form is open when the query is run. X would be the column number of the month in the combo box, starting from 0.

 

[highandwild]

The fifth parameter of the DoCmd.TransferSpreadsheet command allows you to state whether you want the column headings on the spreadsheet.

True = show headings
False = do not show headings

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TableName, FileName, True

 

[JamesMcS]

Import only my friend... when exporting you can put what you like in that option but it'll still export them... according to the help file anyway

 

[eyobs1]

Hi James and highandwild, thank you for your help.

"You'd need to put forms![formname].form![comboname].column(x) in the criteria of the query, and ensure the form is open when the query is run. X would be the column number of the month in the combo box, starting from 0."

I don't know where to put the above line. is it in the criteria of the query or where please. have a look at the access database i attached for you to view. Thank you for your help.
Eyob

 

[JamesMcS]

Hi Eyob, as per my post the forms!.... needs to be in the criteria of the query.

 

[eyobs1]

hi james,

I have a tiltle like:
Expr2: Format([DonorDate],"mmm")
in the crosstabe query as you can see from the attached DB and i put
forms![frmRetrieval].form![Combo0].column("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec")
as a criteria on this query and it is giving me an error message.

Thank you for your help

 

[JamesMcS]

Not quite right with the column property.

The column property allows you to refer to a particular column in your combo or list box, numebred starting from 0. So, if your relevant data is held in the first column, the criteria would read

[COLOR=#0000ff][COLOR=black]forms![frmRetrieval].form![Combo0].column(0)

[/COLOR][/COLOR]

 

[eyobs1]

Thank you James, I'll have a look another way.

 

[JamesMcS]

Good luck!