Export report to excel with vba

[megatronixs]

Hi all,

I managed to create an excel export from a report with VBA. This works fine, well, almost.
The problem I have is that there are Yes/No checkboxes in the form. They don't appear on the excel output file, but they do in the report correctly.
How can I avoid this? I would like to show those columns also in the excel export.

I use the below code to export to excel with VBA:

Private Sub ErrorCorrections_Click()
On Error GoTo Err_ErrorCorrections_Click
    Dim stDocName As String
    'stDocName = "ErrorsandcorrectionsReport"
    'DoCmd.OpenReport stDocName, acPreview
 
    DoCmd.OutputTo acOutputReport, "ErrorsandcorrectionsReport", acFormatXLS, "X:\Test\ReportName - " & Format(Date, "yyyymmdd") & ".xls"
Exit_ErrorCorrections_Click:
    Exit Sub
Err_ErrorCorrections_Click:
    MsgBox Err.Description
    Resume Exit_ErrorCorrections_Click
 
End Sub

Any help in solving this problem woudl be more then welcome

Greetings.

 

[Alansidman]

Try changing the DoCmd.OutputTo line to a DoCmd.TransferSpreadsheet line.

Here is the syntax for that command

http://msdn.microsoft.com/en-us/library/office/aa141565(v=office.10).aspx

 

[megatronixs]

Hi, I was trying to find some information about it, but I only get the ones for the import and not export. Can I use something like this?

DoCmd.TransferSpreadsheet acOutputReport,True, "ErrorsandcorrectionsReport", acFormatXLS, "X:\Test\ReportName - " & Format(Date, "yyyymmdd") & ".xls"

I just don't have access handy at this moment and I will not be able to test it.

Greetings.

 

[Alansidman]

You appear to have combined the TransferSpreadsheet and the OutputTo functions Here is the syntax you need to use:

DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

which I believe in your case would be

DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,"ErrorsandcorrectionsReport","X:\Test\ReportName - " & Format(Date, "yyyymmdd") & ".xls",True

 

[megatronixs]

Hi,

Woooow, works like magic. Thank you very much for your help.

Greetings.

 

[megatronixs]

Hi Alansidman,

Can you help with below:

I would like to count a field if another is empty. I have 2 fields "registered" and "Started" and "closed"
I would like to count all the fields "registered" if the field "started" is empty. I also want to do it with "started" if "closed" is empty.


Thanks and greetings in advance ;-)

 

[pr2-eugin]

What is the Query that you have at the moment?

 

[megatronixs]

Hi,
I don't have a query for this, just put directly a formula in the textbox like the one below:

=Sum(Abs([Spouse Attending]))

Or is this not possible?

Greetings.