Export Data from Access to Excel

[RufussMcGee]

Private Sub cmdExport_Click()
Dim excelobj As New Excel.Application
excelobj.Visible = True

With excelobj
'Add a workbook.
.Workbooks.Add
ChDir "C:\LCD\Programming"
.Workbooks.Open FileName:="C:\LCD\Programming\PROJECT.XLS"
.Cells(1, 2).Value = Me!cboName
.Cells(2, 2).Value = Me!TxtAddress
.Cells(3, 2).Value = Me!TxtCity
.Cells(6, 2).Value = Me!TxtPhone
.Cells(7, 2).Value = Me!TxtFax
.Cells(2, 7).Value = Me!txtPM

MsgBox "Switch to Excel in the taskbar, check and save results."
'.ActiveWorkbook.Close SaveChanges:=False
.Quit

End With
Set excelobj = Nothing
End Sub


I had this code running once in another access file when I copy it this line

>>>Dim excelobj As New Excel.Application<<<

returns the following error.

>>>Compile error: User-defined type not defined<<<

What am I missing? Or should it be written different?

Thanks

 

[PWise]

check your References if you have one for excell

 

[RufussMcGee]

cannot find anything that looks out of place in VB code no this is the only place where it is written and Under Tools I have no reference

 

[Christineeve]

You need to check your references to see if you have the Excel object added into your project.

From your code module select Tools, References and then scroll through and look for Microsoft Excel xx.x Object Library where xx.x is the version you're using. For instance, I'm using Office 2003 and so mine is Micrsoft Excel 11.1 Object Library.

I hope that helps.

 

[avayaman]

Here is the VB code behind a routine in my database to export information to an excel worksheet called template.xls from a query in access, called excelcards. Note I have specified the location of the excel worksheet on the PC. There is one routine & it calls another subroutine to pen the excel sheet after export. This is actually a membership database for a club. What it does it export member info from the database into excel where that information is used to print membership cards. One the data is in excel, macros in that worksheet are used to populate a template.


Private Sub Command66_Click()
On Error GoTo Err_Command66_Click

Msgbox "Please ensure the Excel workbook 'Template.xls' is in the directory (c:\natcoa\template.xls), and that it is not open."

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "excelcards", "c:\natcoa\Template.xls"

Msgbox " The list of cards to be printed has been exported to c:\natcoa\Template.xls."

If Msgbox("Do you want to try to open C:\natcoa\Template.xls? ", vbYesNo, "Open Excel") = vbYes Then

Call cmdOpenExcel_Click



End If

GoTo line1
Err_Command66_Click:
Msgbox "Error. Chances are, you have c:\natcoa\Template.xls open"
line1:


End Sub
Private Sub cmdOpenExcel_Click()
On Error GoTo Err_cmdOpenExcel_Click
Dim retVal As Variant

retVal = Shell("Excel " & Chr(34) & "C:\natcoa\Template.xls" & Chr(34), vbMaximizedFocus)

Exit_cmdOpenExcel_Click:
Exit Sub

Err_cmdOpenExcel_Click:
Msgbox Err.Description
Resume Exit_cmdOpenExcel_Click

End Sub

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[avayaman]

Wel maybe this will help since I do this in my own database:

Here is the VB code behind a routine in my database to export information to an excel worksheet called template.xls from a query in access, called excelcards. Note I have specified the location of the excel worksheet on the PC. There is one routine & it calls another subroutine to pen the excel sheet after export. This is actually a membership database for a club. What it does it export member info from the database into excel where that information is used to print membership cards. One the data is in excel, macros in that worksheet are used to populate a template.


Private Sub Command66_Click()
On Error GoTo Err_Command66_Click

Msgbox "Please ensure the Excel workbook 'Template.xls' is in the directory (c:\natcoa\template.xls), and that it is not open."

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "excelcards", "c:\natcoa\Template.xls"

Msgbox " The list of cards to be printed has been exported to c:\natcoa\Template.xls."

If Msgbox("Do you want to try to open C:\natcoa\Template.xls? ", vbYesNo, "Open Excel") = vbYes Then

Call cmdOpenExcel_Click



End If

GoTo line1
Err_Command66_Click:
Msgbox "Error. Chances are, you have c:\natcoa\Template.xls open"
line1:


End Sub


Here is the second routine, called from the first:


Private Sub cmdOpenExcel_Click()
On Error GoTo Err_cmdOpenExcel_Click
Dim retVal As Variant

retVal = Shell("Excel " & Chr(34) & "C:\natcoa\Template.xls" & Chr(34), vbMaximizedFocus)

Exit_cmdOpenExcel_Click:
Exit Sub

Err_cmdOpenExcel_Click:
Msgbox Err.Description
Resume Exit_cmdOpenExcel_Click

End Sub

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com