Question Open access table/Report in an excel sheet

[rajesh876]

Hi,

I am newbie in access. i have a requirement to show so many fields in report.
Access reports are not good to display that.

I don't want the end user to open the table and look for the information.
So,I want to open up an excel sheet when they click on button and display the table/query information.

Thanks,
Rajesh.

 

[John Big Booty]

Check out the DoCmd.TransferSpreadsheet method.

 

[rajesh876]

Thanks for the reply. Can you post some sample VBA code having Docmd.transferspreadsheet to export into excel? It would be very helpful.

 

[MSAccessRookie]

Thanks for the reply. Can you post some sample VBA code having Docmd.transferspreadsheet to export into excel? It would be very helpful.

The Link that was provided gives all of the required VB Code for a basic case of Access to Excel Data Transfer. If you need to learn more about Visual Basic, then perhaps you can visit the following site first. Good luck in your new adventure.

http://www.functionx.com/vb/index.htm

 

[John Big Booty]

From the link

DoCmd.TransferSpreadsheet(acExport, , TableName, FileName, HasFieldNames, Range, UseOA)

 

[rajesh876]

I had written this in click event.

Private Sub Command4_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dbo_rpt-Metformin_Sulfonylurea", "W:\Prospective Patient Database\ReportsExcelSheets\MetforminSulfonylurea.xls", False
End Sub

Do you think i am doing something wrong?
When i click on the button,it is not doing anything.

Thanks,

 

[John Big Booty]

.....you will of course need to adjust all the variants from TableName on to fit your particular needs.

 

[John Big Booty]

Have you check in W:\Prospective Patient Database\ReportsExcelSheets\ to see if the file MetforminSulfonylurea. xls has been created?

 

[rajesh876]

Ohh...Its my bad....I thought it is doing nothing.

It is exporting the data to excel sheet. It is just not opening the sheet.

Do you know how can i open that excel sheet? Please advise. Also,Can u suggest how can i make the headers bold while exporting to excel?

Thanks,
You are a great help John...

 

[MSAccessRookie]

I had written this in click event.

Private Sub Command4_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dbo_rpt-Metformin_Sulfonylurea", "W:\Prospective Patient Database\ReportsExcelSheets\MetforminSulfonylurea.xls", False
End Sub

Do you think i am doing something wrong?
When i click on the button,it is not doing anything.

Thanks,

Since TransferSpreadsheet is a Function, I believe that the open and close parentheses might be required. Everything else looks OK to me.

DoCmd.TransferSpreadsheet [B][COLOR=red]([/COLOR][/B] acExport, acSpreadsheetTypeExcel9, "dbo_rpt-Metformin_Sulfonylurea", "W:\Prospective Patient Database\ReportsExcelSheets\MetforminSulfonylurea.xls", False[COLOR=red][B] )[/B][/COLOR]

 

[rajesh876]

Can you/someone please help me how to open up excel sheet in VBA code ?

Thanks,

 

[John Big Booty]

Try inserting the following in your code;

Application.Workbooks.Open ("W:\Prospective Patient Database\ReportsExcelSheets\MetforminSulfonylurea.xls")

 

[rajesh876]

I inserted that code in VBA editor.It is giving me an error listed below.
Compile error: Method or Data member not found.

Do we need to include any libraries or parent directories?

Thanks,

 

[rajesh876]

Thank you for the help John.

I am able to open the excel sheet after replacing the following code.

Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String

' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")

' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0

' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = "W:\Prospective Patient Database\ReportsExcelSheets\MetforminSulfonylurea.xls"

' Open it
With oXL
.Visible = True
.Workbooks.Open (sFullPath)
End With

ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Run it from a form button

Private Sub cmdOpenExcelFIle_Click()
' Access form button
Call OpenSpecific_xlFile

End Sub

Private Sub Command4_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dbo_rpt-Metformin_Sulfonylurea", "W:\Prospective Patient Database\ReportsExcelSheets\MetforminSulfonylurea.xls", False
Call OpenSpecific_xlFile
End Sub

But it is opening different sheet instead of the one that was exported.

Thanks,