Exporting Data from Access to Excel

[Shazz]

Hi Guys,

I have a database with nearly 50,000 records in including sub forms that are connected to the main form, what I want to be able to do is, have a button on my switchboard that when clicked with export any records over 6 months into an Excel Spread Sheet. Is this possible at all and if so, How do I do it.

I only have basic knowlege of access so simple explanation is needed, sorry xx

Shazz

 

[Lightwave]

Yes very easy

Firstly you need to figure out how to design a query that will select only the records you are interested.

Then you create the button you are interested in and then put a variation of the code from the following thread behind the onclick event

http://www.access-programmers.co.uk/forums/showthread.php?t=103457

Good luck

 

[Shazz]

How would I go about creating the query though, I am very new to all this, I need to be a ble to have a button that would know what 6 months + date was and auto export to a saved Excel Workbook called "DTDs Exported Excel Data Archive".

Can you help me

I assume I would use the code below as well.

[COLOR=black][FONT=Courier New]Public Sub ModifyExportedExcelFileFormats(sFile As String)[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]On Error GoTo Err_ModifyExportedExcelFileFormats[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]   Application.SetOption "Show Status Bar", True[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]   vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]   Dim xlApp As Object[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   Dim xlSheet As Object[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]   Set xlApp = CreateObject("Excel.Application")[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]   With xlApp[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Sheets("DTDs Exported Excel Data Archive").Select[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Cells.Select[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Selection.ClearFormats[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Rows("1:1").Select[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Selection.Font.Bold = True[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Cells.Select[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Selection.RowHeight = 12.75[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Selection.Columns.AutoFit[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Range("A2").Select[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.ActiveWindow.FreezePanes = True[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Range("A1").Select[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Selection.AutoFilter[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]           .Application.Activeworkbook.Save[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Application.Activeworkbook.Close[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]           .Quit[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   End With[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]   Set xlApp = Nothing[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   Set xlSheet = Nothing[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]   vStatusBar = SysCmd(acSysCmdClearStatus)[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]Exit_ModifyExportedExcelFileFormats:[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   Exit Sub[/FONT][/COLOR]
 
[COLOR=black][FONT=Courier New]Err_ModifyExportedExcelFileFormats:[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   vStatusBar = SysCmd(acSysCmdClearStatus)[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   MsgBox Err.Number & " - " & Err.Description[/FONT][/COLOR]
[COLOR=black][FONT=Courier New]   Resume Exit_ModifyExportedExcelFileFormats[/FONT][/COLOR]

 

[Lightwave]

OK your trying to run before you can walk here a bit but first your going to have to learn how to create a query

see Access 2003 instructions this link

http://office.tizag.com/accessTutorial/accessqueries.php

or this link which will be better if you are ussing Access 2007 or 2010

http://www.youtube.com/watch?v=FwaRAKBBci4

Now the code would normally sit behind a command button. However this is the run before you can walk bit. If you haven't done queries before you probably aren't familiar with coding and you really need to understand how to design forms and what controls are before you can progress to the coding section so I recommend you do a couple of tutorials on designing forms here's some I found on Google.

Before you start with the code do a tutorial on designing forms.

http://databases.about.com/od/tutorials/ss/access2007forms_4.htm

Then you need to understand how controls work

http://www.gcflearnfree.org/access2007/8.3

Now you are ready to think about placing code behind buttons

Now here's lessons on Visual Basic for Applications

http://www.functionx.com/vbaccess/Lesson01.htm

I wouldn't worry about the second code part as that deals with formatting and will be unlikely to be relevant for your purposes so just ignore that until you've tried the above

 

[Shazz]

Thanks lightwave, I have done basic queries and I can do basic Button commands ie open a form/table etc, but I am unsure how to export the data if it is 6 months or older.

 

[Lightwave]

Hi S will try and put an example together and post it. Not going to be able to do anything till Wed night though as got other committments at the moment.

The central method used in all VBA code is called TransferSpreadsheet a description of which along with required parameters is listed at the following link

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

 

[Lightwave]

In the meantime you might want to take a quick lookd at this link

http://office.microsoft.com/en-us/access-help/examples-of-query-criteria-HA010066611.aspx

and in particular scroll down to the section marked

Criteria for Date/Time fields

I will have to use a variation of one of those items to get the date period you are looking at.

 

[Lightwave]

Shazz

Open the database and then open form F001...

I pulled together a really simple database that has two forms. One an invoice form in which you can enter invoices and then a second that will show any with invoices more than 6 months old. What I do is take today date count the difference between todays month and all the records months and then simply filter out anything which is greater than 6. That does mean you won't get partial months.

On the second form is a button that then exports the filtered query to an excel file on the C drive called 6months.xls.

The code I used was much simpler than the one I linked to...

Anyway hope you see this

Mark