Exporting Data Into Excel

[AshParr]

Hi,

I am currently trying to export data from one of my forms to excel. I
know you can export to excel using macros and vb but i have noticed
that access gives the option to exprot to excel using a template.

How would i create a excel template?


Please reply and any advice is much appreciated

Many Thanks,

Ashley Parr

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ExportToExcel.mdb" which might give you some help.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[AshParr]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ExportToExcel.mdb" which might give you some help.

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L











- Show quoted text -

i have tried this and it works in the sample database you set up, but
as soon as i try to use it in mine i get alot of errors, it says
something along the lines of i havnt defined exportspreadsheet.

any help would be good thanks

 

[Roger Carlson]

Well, the purpose of the sample (as with all of my samples) is to give you a
working model that you can dissect. Take the time to figure out exactly
what is happening with my sample before you try to implement it in your
database. If you have specific questions about why it's doing certain
things, feel free to ask.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[AshParr]

Well, the purpose of the sample (as with all of my samples) is to give you a
working model that you can dissect. Take the time to figure out exactly
what is happening with my sample before you try to implement it in your
database. If you have specific questions about why it's doing certain
things, feel free to ask.

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L








- Show quoted text -

Thanks for the reply, but i did download your sample and open it up.

I looked at what it did and how it works and modified it to show data
in a different template and that worked great, but now when im trying
to implent it into my database it doesnt like some of the lines.

One of the errors i get is: "Compile Error: Userdefined type not
defined"
with "Set objXLApp = New Excel.Application" highlighted

I also had the same problem with defining objXLApp, so i changed the
code to:

Dim objXLBook As Object
Set objXLBook As Excel.WorkBook

then this part was fine, was this right to do or not?

 

[AshParr]

OK then this is what iv got so far, like i posted above i have
allready played around and it worked in the database i downloaded but
when i create this in my own it doesnt work. If it seems silly or noob
like then i am sorry.

Sub exportspreadsheet()
On Error GoTo HandleError

{~So below we define the variables,right?~}
Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Object
Set objXLBook = Excel.Workbook

Dim db As DAO.Database
Set db = CurrentDb

conPath = GetPath(db.Name)

'delete the spreadsheet
Kill conPath & "MonthlyData.xls"

{~Its Fine Running up to this point~}

{~Then it highlights Set objXLApp = New Excel.Workbook~}
{~I changed this so it reads below but i now get a "424 Runtime Error:
Object Required}

' create a workbook from the template
Set objXLApp = NewWorkbook
Set objXLBook = objXLApp.Workbooks.Open(conPath & "Template.xlt")
'objXLApp.Visible = True

objXLBook.SaveAs (conPath & "MonthlyData.xls")
objXLBook.Close

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Form:
Home Page ", conPath & "MySpreadsheet.xls", True


Once again sorry if these seems like a minor problem to you guys

 

[AshParr]

OK then this is what iv got so far, like i posted above i have
allready played around and it worked in the database i downloaded but
when i create this in my own it doesnt work. If it seems silly or
noob
like then i am sorry.

Sub exportspreadsheet()
On Error GoTo HandleError


{~So below we define the variables,right?~}
Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Object
Set objXLBook = Excel.Workbook


Dim db As DAO.Database
Set db = CurrentDb


conPath = GetPath(db.Name)


'delete the spreadsheet
Kill conPath & "MonthlyData.xls"


{~Its Fine Running up to this point~}


{~Then it highlights Set objXLApp = New Excel.Application~}
{~I changed this so it reads below but i now get a "424 Runtime
Error:
Object Required}


' create a workbook from the template
Set objXLApp = NewWorkbook
Set objXLBook = objXLApp.Workbooks.Open(conPath & "Template.xlt")
'objXLApp.Visible = True


objXLBook.SaveAs (conPath & "MonthlyData.xls")
objXLBook.Close


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Form:
Home Page ", conPath & "MySpreadsheet.xls", True


Once again sorry if these seems like a minor problem to you guys

 

[Roger Carlson]

Make sure you have a reference set to the version of Excel you are using.
Do this in any code module. Go to Tools>References. Scroll down the list
until you find Microsoft Excel.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L